Ask Your Question
0

How do I use another function in a sub?

asked 2019-08-21 03:31:51 +0200

chrismg12 gravatar image

Hi I'm pretty new to LibreOffice Calc and macros, so please forgive me if this question is very dumb. However I was trying to make some functions in relation to color. However the following code just doesnt work:

Function getCellText(sAddress As String) As String
Dim oSheets As Variant
Dim oCellRangesByName As Variant
Dim oXCellRange As Variant
Dim oCellByPosition As Variant
    On Error GoTo Wrong_parm
    oSheets = ThisComponent.getSheets()
    oCellRangesByName = oSheets.getCellRangesByName(sAddress)
    oXCellRange = oCellRangesByName(0)
    oCellByPosition = oXCellRange.getCellByPosition(0, 0)
    getCellText = oCellByPosition.getString()
    Exit Function
Wrong_parm:
    getCellText = "<wrong parameter="">"
End Function

sub H2D(sAddress As String)
dim oFunction as variant
oFunction = createUnoService("com.sun.star.sheet.FunctionAccess")
dim aArgument(0) as variant
dim hArgs(0)
dim result
hex = getCellText(sAddress)
aArgument(0)=hex
result = oFunction.callFunction( "HEX2DEC", aArgument())
H2D = result
end sub

The function is supposed to take the cell value as a string and return the hex value to base 10. However this returns #NAME?.

Any help is appreciated. Thanks.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2019-08-21 04:16:59 +0200

updated 2019-08-21 15:07:03 +0200

Not using the wrong function?

image description

Complement

Want to convert to the same cell?

Define a receiving cell with a function with reference to the previous cell.

The macro Copies the cell to be converted, pastes into the receiving cell, copies of the cell with the reference, and pastes value into the starting cell.

Following is an example file, fill in cell C5, leave it selected and click the button below.

image description

REM  *****  BASIC  *****

' Attribute VBA_ModuleType=VBADocumentModule
Option VBASupport 1
Option Explicit

sub Main
dim document, dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

Dim Var1 As string
 Var1 = ActiveCell.Address ' (VBA)
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$F$5"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$F$8"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = Var1
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())
dim args7(5) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Flags"
args7(0).Value = "SVD"
args7(1).Name = "FormulaCommand"
args7(1).Value = 0
args7(2).Name = "SkipEmptyCells"
args7(2).Value = false
args7(3).Name = "Transpose"
args7(3).Value = false
args7(4).Name = "AsLink"
args7(4).Value = false
args7(5).Name = "MoveMode"
args7(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args7())
end Sub

You can trigger the macro with any active cell, the value returns to it.

-----------------------------------------------------------------------------

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more
0

answered 2019-08-21 11:55:35 +0200

Opaque gravatar image

updated 2019-08-21 12:17:08 +0200

Hello,

you cannot use =H2D(<reference>) as a formula in a cell. User-defined functions to be used in formulas must be defined as Function .... That also explains #NAME? error - the function H2D is not defined at all.

And some more remarks:

  • See answer of @Gilberto Schiavinatto
  • Are you aware that Calc passes values to functions, so defining the function parameter As string may be counterproductive.
  • Functions have limited permissions on cells. They cannot be used to change any formats.

If the answer is correct or helped you to fix your problem, please click the check mark (✔) next to the answer.

edit flag offensive delete link more

Comments

To be honest - I don't understand the intention at all due to the existence of the internal functions DEC2HEX and HEX2DEC

Opaque gravatar imageOpaque ( 2019-08-21 12:13:29 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-08-21 03:31:51 +0200

Seen: 46 times

Last updated: Aug 21