Re: [libreoffice-users] Recalculate Rand between targeting a particular Cell or Cell Range

> From: Jack Cambel <jcna2000-dauntless@yahoo.com>
> To: "users@global.libreoffice.org" <users@global.libreoffice.org>
> Sent: Fri, 19 August, 2011 4:20:20
> Subject: [libreoffice-users] Re: Recalculate Randbetween targeting a particular
> Cell or Cell Range
>
> Hello,
> I want to learn how to create a button to recalculate a cell with randbetween
> function. I know it is going to be a bit complicated, can anyone point out where
> I can I find resources online and where I can have a handy reference of the
> macros.
>
> Thanks in advance,
> Jack
>
> LibreOffice 3.3.2
> OOO330m19 (Build:301)
> tag libreoffice-3.3.2.2, Ubuntu package 1:3.3.2-1ubuntu5

I might be wrong, but I don't think it's possible to recalculate only
one cell, not even with a macro. Ctrl+Shift+F9 recalculates all cells.

If you want a random value in one cell and not recalculate the others,
then I would probably not enter a cell formula in that cell at all,
but rather create a macro that does that.

In View→ Toolbars (or something like that) there is a toolbar for
creating formula controls. One of those is a button that you can draw
on the spreadsheet (if the edit button in the same toolbar is
activated). Then right click the button and select Properties. The
last tab in the window that pops up you can assign macros to different
events.

The macro could look something like this (not tested):

Option Explicit

Sub main
        Randomize
        Dim X As Double, k As Double, b As Double
        Dim ShName As String
        k=1000 ' Size of the span between minimum and maximum values
        b=1 ' Lowest possible value
        X=Int(k*Rnd)+b

        ShName="Sheet1" ' The name of the sheet
        CName="A1" ' The "name" of the cell
        ThisComponent.getSheets().getByName(ShName).getCellByName(CName).setValue(X)
'       I'm not sure there is a getCellByName method. If not, put a ' before
the last two lines above and remove the traiing ' from the lines
below:
'       Col=0 ' 0 is column A, 1 is B and so on
'       Row=0 ' 0 is the first row, so B2 is at row 1 and column 1, for example.
'       ThisComponent.getSheets().getByName(ShName).getCellByPosition(Col,Row).setValue(X)
End Sub

To enter the macro, click Tools → Macros → LibreOffice Basic→ Look for
your file, it's one of the last ones in the tree to the left, double
click it → Standard (or create a new one) → Create or New or the name
of the button is → Give the module a name or let it be Module1 → Click
New → Start writing macros!
Something like that anyway.
You are now in the Basic IDE, and if you click Help you can get some
help regarding Basic.

J.R.

-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted