Using dec2hex inside a Macro

[p3r3gr1nus]

I am trying to use the function HEX2DEC in a macro. While I haven't any issue with other functions (e.g. AVERAGE or HEX2DEC does not work and I get the exception: "com.sun.star.container.NoSuchElementException"

This is my function:

Code: Select all

Function fnDec2Hex(nDec AS Integer) AS String
   Dim oFunctionAccess : oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
     
   arg = Array(nDec)
   fnDec2Hex = oFunctionAccess.CallFunction( "DEC2HEX", arg)
End Function

[JeJe]

Works for me. But basic has a Hex function - why not use that.

Code: Select all

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

Sub Main
msgbox hex(500) '1F4
msgbox fnDec2Hex(500) '1F4
End Sub


Function fnDec2Hex(nDec AS Integer) AS String
   Dim oFunctionAccess : oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
   arg = Array(nDec)
   fnDec2Hex = oFunctionAccess.CallFunction( "DEC2HEX", arg)
End Function

Edit: if the numbers outside the integer range it will fail as that's what you've declared the parameter as.

[p3r3gr1nus]

Thank you. I have not idea of the reason why it is not working on my Spreadsheet. There are other function that does not work when imported with FunctionAccess as for instance QUOTIENT.
I wrote by myself a function to convert dec to hex.
In any case, since I am a newbie in AOO Macros I would like to ask where to find the documentation regarding the "basic" functions I googled a lot trying to solve my problem, but i did't found any reference to the "hex()".
Thank you

[RoryOF]

The OpenOffice BASIC manual at

https://wiki.openoffice.org/wiki/Docume ... ASIC_Guide

Will give you sufficient to delve into OO macro programming.

A good place to learn about OpenOffice macros in much greater detail is http://www.pitonyak.org/oo.php

[JeJe]

There's information, and examples in the OO IDE help file. A search for hexadecimal number would have found it. A browse through the index would help you learn what's available.

[JeJe]

QUOTIENT needs an add-in and again has a basic alternative too explained here:
https://wiki.openoffice.org/wiki/Docume ... T_function


HEX2DEC also has simple basic alternatives

Code: Select all

   Dim oFunctionAccess : oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
    arg = Array("500")
   	msgbox oFunctionAccess.CallFunction( "HEX2DEC", arg)
	
	msgbox val("&H" & 500)
	msgbox clng("&H" & 500)

[Zizi64]

I have not idea of the reason why it is not working on my Spreadsheet.

Please give us some details about "how you use the created function?". (maybe you passed too large numbers, negative numbers, strings, floats to the custom function...)

[p3r3gr1nus]

I have not idea of the reason why it is not working on my Spreadsheet.

Please give us some details about "how you use the created function?". (maybe you passed too large numbers, negative numbers, strings, floats to the custom function...)

I use it in the function ach2ahx() which is used on my sheet on a string (e.g. "AT#SCFG=1,1,300,0,600,50").
The function has to create a new string where each char of the string is converted in Hex.
For instance "AT#SCFG=1,1,300,0,600,50" becomes "415423534346473D312C312C3330302C302C3630302C3530
"
If I replace fnDec2Hex with my custom function "Dec2HexC", everything is fine.

Code: Select all

Function ach2ahx(instr As Variant) As String
 Dim i%, s$, val1, val2%, val3$
 s$ = ""
 For i% = 1 To LEN(instr)
	  val1 = Mid(instr, i%, 1)
	  val2 = calc_CODE(val1)
	  val3 = fnDec2Hex(val2)
	  s$ = s$ & val3
 Next
	achar2ahex = s$
End Function

Function fnDec2Hex(nDec AS Integer) AS String
   Dim oFunctionAccess : oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
     
   arg = Array(nDec)
   fnDec2Hex = oFunctionAccess.CallFunction( "DEC2HEX", arg)
End Function

Function Dec2HexC(nDec AS Integer) AS String
   Dim ccM$, ccL$, vM%, vL%, valin%
   Dim Hx As Variant
   
   Hx = Array("0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F","X")
      
   vM = 0 
   valin = nDec
   Do While valin >= 16
		vM = vM + 1
		valin = valin - 16
   Loop
   vL = nDec - (vM * 16)
   
   ccM = Hx(vM)
   ccL = Hx(vL)
	    
	Dec2HexC = ccM & ccL    
End Function

Function calc_CODE(vRange)

                     ' The first argument to callFunction() is the Calc Function Name
                     ' The 2nd argument is the parameters of that function as an array
   FuncAcc = createunoservice("com.sun.star.sheet.FunctionAccess")

   calc_CODE = FuncAcc.callFunction("CODE", array(vRange))
   
End Function

[JeJe]

see comments:

Code: Select all

Function ach2ahx(instr As Variant) As String 'DON'T USE INSTR FOR A VARIABLE NAME - ITS A BASIC FUNCTION
Dim i%, s$, val1, val2%, val3$
s$ = ""
For i% = 1 To LEN(instr)
     val1 = Mid(instr, i%, 1)
     val2 = calc_CODE(val1) 'BASIC HAS THE ASC FUNCTION
     val3 = fnDec2Hex(val2) 'AND THE HEX FUNCTION
     s$ = s$ & val3
Next
   achar2ahex = s$ 'achar2ahex IS NOT THE SAME AS THE FUNCTION NAME
End Function

Try:

Code: Select all

Function ach2ahx(st) As String
dim i as long
For i = 1 To LEN(st)
 ach2ahx= ach2ahx & hex(Asc(Mid(st, i, 1)))
Next
End Function

I don't know what you intend doing with the resultant string but if you want to retrieve the numbers again the hex numbers can be different lengths - and you don't know where they begin and end.