Macro not active when the document start

I'm using macro's function and it doesn't start when the document start. It would start if I change the value of the variabel at the function. The code is to change numeric value to string. If I execute the code: ubah(123) the result is "seratus dua puluh tiga". Any idea how to make the function executed when I open the document?

The code is:

Public Function ubah(x as currency) as String
     Dim triliun As Currency
     Dim milyar As Currency
     Dim juta As Currency
     Dim ribu As Currency
     Dim satu As Currency
     Dim sen As Currency
     Dim baca As String
     If x > 1000000000000 Then
        gusti = " Modul belum tersedia saat ini "
        Exit Function
     End If
     'Jika x adalah 0, maka dibaca sebagai 0
     If x = 0 Then
        baca = angka(0, 1)
     Else
        'Pisah masing-masing bagian untuk triliun, milyar, juta, ribu, rupiah, dan sen
        triliun = Int(x / 1000 ^ 4)
        milyar = Int((x - triliun * 1000 ^ 4) / 1000 ^ 3)
        juta = Int((x - triliun * 1000 ^ 4 - milyar * 1000 ^ 3) / 1000 ^ 2)
        ribu = Int((x - triliun * 1000 ^ 4 - milyar * 1000 ^ 3 - juta * 1000 ^ 2) / 1000)
        satu = Int(x - triliun * 1000 ^ 4 - milyar * 1000 ^ 3 - juta * 1000 ^ 2 - ribu * 1000)
        sen = Int((x - Int(x)) * 100)
        'Baca bagian triliun dan ditambah akhiran triliun
        If triliun > 0 Then
           baca = ratus(triliun, 5) + "triliun "
        End If
        'Baca bagian milyar dan ditambah akhiran milyar
        If milyar > 0 Then
           baca = ratus(milyar, 4) + "milyar "
        End If
        'Baca bagian juta dan ditambah akhiran juta
        If juta > 0 Then
           baca = baca + ratus(juta, 3) + "juta "
        End If
        'Baca bagian ribu dan ditambah akhiran ribu
        If ribu > 0 Then
           baca = baca + ratus(ribu, 2) + "ribu "
        End If
        'Baca bagian rupiah dan ditambah akhiran rupiah
        If satu > 0 Then
           baca = baca + ratus(satu, 1)
        End If
        'Baca bagian sen dan ditambah akhiran sen
        If sen > 0 Then
           baca = baca + ratus(sen, 0)
        End If
     End If
     ubah = UCase(Left(baca, 1)) & LCase(Mid(baca, 2))
End Function

Function ratus(x As Currency, Posisi As Integer) As String
     Dim a100 As Integer, a10 As Integer, a1 As Integer
     Dim baca As String
     a100 = Int(x * 0.01)
     a10 = Int((x - a100 * 100) * 0.1)
     a1 = Int(x - a100 * 100 - a10 * 10)
     'Baca Bagian Ratus
     If a100 = 1 Then
        baca = "Seratus "
     Else
        If a100 > 0 Then
           baca = angka(a100, Posisi) + "ratus "
        End If
     End If
     'Baca Bagian Puluh dan Satuan
     If a10 = 1 Then
        baca = baca + angka(a10 * 10 + a1, Posisi)
     Else
        If a10 > 0 Then
           baca = baca + angka(a10, Posisi) + "puluh "
        End If
        If a1 > 0 Then
           baca = baca + angka(a1, Posisi)
        End If
     End If
     ratus = baca
End Function

Function angka(x As Integer, Posisi As Integer)
     Select Case x
         Case 0: angka = "Nol"
         Case 1:
             If Posisi <= 1 Or Posisi > 2 Then
                angka = "Satu "
             Else
                angka = "Satu "
             End If
         Case 2: angka = "Dua "
         Case 3: angka = "Tiga "
         Case 4: angka = "Empat "
         Case 5: angka = "Lima "
         Case 6: angka = "Enam "
         Case 7: angka = "Tujuh "
         Case 8: angka = "Delapan "
         Case 9: angka = "Sembilan "
         Case 10: angka = "Sepuluh "
         Case 11: angka = "Sebelas "
         Case 12: angka = "Duabelas "
         Case 13: angka = "Tigabelas "
         Case 14: angka = "Empatbelas "
         Case 15: angka = "Limabelas "
         Case 16: angka = "Enambelas "
         Case 17: angka = "Tujuhbelas "
         Case 18: angka = "Delapanbelas "
         Case 19: angka = "Sembilanbelas "
     End Select
End Function

toto wrote (10-08-11 09:23)

I'm using macro's function and it doesn't start when the document start.

You can change that via Tools > Customize > Events.
Find the OnOpen event and link it to the macro for the document.

Regards,

The macro is active, but the function in the cell not active. Somehow I have to change the function variable with another value then I have to change it back again. So in the cell if I put "=ubah(E7)" I have to change it to "=ubah(100)" and then change it back again to "=ubah(E7)" to get the result that I want.

toto wrote (10-08-11 09:23)

I'm using macro's function and it doesn't start when the document start.

You can change that via Tools > Customize > Events.
Find the OnOpen event and link it to the macro for the document.

I think the function is supposed to be used as a cell function. Then
the event thing is not needed.

Regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

I'm using macro's function and it doesn't start when the document start. It
would start if I change the value of the variabel at the function. The code
is to change numeric value to string. If I execute the code: ubah(123) the
result is "seratus dua puluh tiga". Any idea how to make the function
executed when I open the document?

The macro is used as a cell function right?
In that case, where did you put the macro? It should be placed in My
Macros, I think, so you need to move it there if it's not there
already.

Regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

Am 11.08.2011 10:30, Johnny Rosenberg wrote:

The macro is used as a cell function right?
In that case, where did you put the macro? It should be placed in My
Macros, I think, so you need to move it there if it's not there
already.

Wrong. It has to be stored in library "Standard", either the document's one or the global one. "Standard" is loaded automatically and a cell function will be found immediately.
The functions do not contain any references to UNO objects so they *should* work right from the start and they *should* update automatically if menu:Tools>CellContents>Automatic Calculation is enabled and if (and only if) a referenced value has been modified.
Otherwise recalculation should be triggered on key F9.
Ctrl+shift+F9 enforces a complete recalculation of the whole workbook.

Am 11.08.2011 10:30, Johnny Rosenberg wrote:

The macro is used as a cell function right?
In that case, where did you put the macro? It should be placed in My
Macros, I think, so you need to move it there if it's not there
already.

Wrong. It has to be stored in library "Standard", either the document's one
or the global one. "Standard" is loaded automatically and a cell function
will be found immediately.

Well, I have mine in My Macros/Standard but maybe that's not necessary.

At the moment I have one cell function there and it works perfectly,
but when I add another one, LibreOffice refuse to recognise it. What
ever I do, the cell only displays ”#NAME?” no matter if I have it in
the document's Standard library or in My Macros/Standard.

Can someone give a step-by-step instruction how to create a simple
cell function? Do I need to restart LibreOffice (did that too, but it
didn't help)?
I know I didn't create the thread, but still…

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

Strange… I fiddled with it a bit and suddenly it works, I don't have a clue why…

It also seems like it looks in the document's Standard library first,
because if I had them at both places the local one was executed.

I made a very simple one, this is the one in the document's standard library:
Function DoStuff As Long
  DoStuff=3
End Function

Here's the one in My Macros/Standard:
Function DoStuff As Long
  DoStuff=4
End Function

In a cell:
=DOSTUFF

The result was 3. When I removed the local one, the result was 4 after
a Ctrl+Shift+F9.

But still I don't have a clue why it works now and not a few minutes ago…

Regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

Am 10.08.2011 09:23, toto wrote:

I'm using macro's function and it doesn't start when the document start.
It would start if I change the value of the variabel at the function.
The code is to change numeric value to string. If I execute the code:
ubah(123) the result is "seratus dua puluh tiga". Any idea how to make
the function executed when I open the document?

The code is:

Public Function ubah(x as currency) as String

Currency is not a valid type to be used in a spreadsheet.
From the Basic perspective all spreadsheet values are doubles or string, errors are Null, empty cells are empty strings. There are no "date values" in particular.

This function works as expected:
Function DOUBLE(n as Double)
  DOUBLE = n *2
End Function

This function returns a text value.
Function DOUBLE_CURR(n as Currency)
  DOUBLE_CURR = n *2
End Function

I can not reproduce the recalculation problem, though.
Both versions of the above functions recalculate when I load the document or when I modify A1 with auto-cacluation enabled.
A1 =PI()
=DOUBLE(A1) => 6.2832 (number)
=DOUBLE_CURR(A1) => 6.2832 (text)

Am 10.08.2011 09:23, toto wrote:
> I'm using macro's function and it doesn't start when the document start.
> It would start if I change the value of the variabel at the function.
> The code is to change numeric value to string. If I execute the code:
> ubah(123) the result is "seratus dua puluh tiga". Any idea how to make
> the function executed when I open the document?
>
> The code is:
>
> Public Function ubah(x as currency) as String

Currency is not a valid type to be used in a spreadsheet.
From the Basic perspective all spreadsheet values are doubles or
string, errors are Null, empty cells are empty strings. There are no
"date values" in particular.

Subtle little gotcha, use dates/time and currencies in the spreadsheet but the value must be converted to a valid Basic data type.

Thank you everyone, it solved i'm using new module to execute the function, when I open the document. Eventhough I hadn't try it with another computer. Yes, I change the variable to double, I think there is no different with currency.