[Solved] Record macro for Fill Random Number

[thanksinadvance1]

Recently I started this thread:
RANDBETWEEN retains its value in OO, why not in LibreOffice? at
viewtopic.php?f=9&t=106786

I thought I found a great solution to this, but…

I discovered this:
In Sheet>Fill Cells>Fill Random Number it will generate random numbers that don’t change whenever you do anything else with the spreadsheet. It just puts random numbers in selected cells without any formula.

Libre Office has this feature. Open Office doesn't. So Libre Office is better. Now we're talking. I thought.

So I tried to record a macro that, at the click of a button (which I need to have on the spreadsheet to generate the random number), would put a random number into cell A1 (which was already selected), and this is what it looked like:

Code: Select all

sub random1
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:RandomNumberGeneratorDialog", "", 0, Array())

end sub

I go to run it and, for whatever reason, it would not put a random number into the cell. I tried variations of this macro, one which would cut the number and one which would copy the number and paste it back into the cell but neither one would work. In all cases no random number would go back into a cell, the cell just stayed empty.

I still have the solution I came up with in the old thread, but this one seems cleaner and easier if I could get it to work.

Anyone know why this doesnt work and/or how I could make something like this work?

[Villeroy]

Macros are a complete waste of time and energy. Just learn how to use a spreadsheet with more keyboard and less mouse input. This gives you a lot more versatility.

[thanksinadvance1]

Appreciate that suggestion, unfortunately I'm trying to make a spreadsheet that relies totally on a mouse and no keyboard at all.

[Zizi64]

The Macro Recorder has a very limited capability. And it works in the Calc and Writer application only, but not work in the other applications.
You must WRITE your macros (based on the API functions and procedures) - if you want to work with macros efficiently.

Study Andrew Pitonyak's free macro books:
https://www.pitonyak.org/oo.php

[thanksinadvance1]

Great advice all, and thanks for providing it.

But in my particular case I'm only trying to tighten up a particular spreadsheet a little, one that I already have a kind of messy but apparently working workaround for. If I put in the time and effort to do what you mentioned, I'd have no idea how to put that knowledge to good use. That, and, I just have way too much other stuff to do.

[JeJe]

See Rnd and Randomize in the help file (Edit: the Basic IDE one) for how to generate a random number in Basic.

[JeJe]

This will put a random number between 1 and 100 into the selected cells - change those two numbers in the macro to your wanted values

Code: Select all

Sub RandomNumberIntoSelectedCells
dim lowerbound,upperbound,dataarray
lowerbound = 1
upperbound = 100

dataarray = thiscomponent.currentselection.getDataArray 

for i = 0 to ubound(dataarray)
for j = 0 to  ubound(dataarray(i))
dataarray(i)(j)= Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
next
next
thiscomponent.currentselection.setDataArray dataarray

End Sub

[Villeroy]

Just nitpicking:

Code: Select all

Sub RandomNumberIntoSelectedCells()
dim lowerbound,upperbound,dataarray
lowerbound = 1
upperbound = 100
sel = thiscomponent.currentselection
if sel.supportsservice("com.sun.star.sheet.SheetCellRange") then
	rgs = sel.queryIntersection(sel.getRangeAddress())
elseif sel.supportsservice("com.sun.star.sheet.SheetCellRanges") then
	rgs = sel
else
	msgbox "No cells selected", 16
	exit sub
end if
e = rgs.createEnumeration()
while e.hasMoreElements()
		sel = e.nextElement()
		dataarray = sel.getDataArray
		for i = 0 to ubound(dataarray)
			for j = 0 to  ubound(dataarray(i))
				dataarray(i)(j)= Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
			next
		next
		sel.setDataArray dataarray
wend
End Sub

works with multiple selection of cell ranges.

[JeJe]

Thanks Villeroy.

(At first I was going to be *really* lazy and just reply with the easy one cell selection case)

Code: Select all

lowerbound = 1
upperbound = 100
thiscomponent.currentselection.value = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)