[Solved] [Calc] Function does not pass value to Sub

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
theMoose
Posts: 8
Joined: Wed Mar 09, 2016 5:36 pm

[Solved] [Calc] Function does not pass value to Sub

Post by theMoose »

Hello all,

I'm having some trouble with passing a value from a Function to a Sub.
Below is the code I wrote.
I'm trying to convert a number (integer) to a letter (string) to use for something else later on.
I put in some MsgBox's to see what's happening, trying to understand the problem....

Code: Select all

Sub TESTMacro()
	Dim sNumToLetter As String
	sNumToLetter = NumToLetter(2)
	MsgBox "NumToLetter=" & sNumToLetter
End Sub

Function NumToLetter (iNum As Integer)
	Dim oLet As String
	Select Case iNum
		Case 1: oLet = "A"
		Case 2: oLet = "B"
		Case 3: oLet = "C"
		Case 4: oLet = "D"
		Case 5: oLet = "E"
		Case 6: oLet = "F"
		Case 7: oLet = "G"
		Case 8: oLet = "H"
		Case 9: oLet = "I"
		Case 10: oLet = "J"
		Case 11: oLet = "K"
		Case 12: oLet = "L"
		Case 13: oLet = "M"
		Case 14: oLet = "N"
		Case 15: oLet = "O"
		Case 16: oLet = "P"
		Case 17: oLet = "Q"
		Case 18: oLet = "R"
		Case 19: oLet = "S"
		Case 20: oLet = "T"
		Case 21: oLet = "U"
		Case 22: oLet = "V"
		Case 23: oLet = "W"
		Case 24: oLet = "X"
		Case 25: oLet = "Y"
		Case 26: oLet = "Z"
	End Select
	MsgBox "iNum=" & iNum  & " ; oLet=" & oLet
End Function
Using NumToLetter(2) should result in getting the letter "B".
iNum (input) value is 2, oLet (output) value is B according to the MsgBox within the Function.
However the value for sNumToLetter is empty according to the MsgBox within the Sub.

What am I missing here?
Last edited by theMoose on Thu Dec 22, 2016 2:06 pm, edited 1 time in total.
=======================================================
AOO 4.1.2 & LibreOffice 5.2.1.2 on Windows 7 & Linux Mint 17.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [CALC] Function does not pass value to Sub

Post by Villeroy »

=CHAR(A1+64) does the job far more efficiently.
Basic is a stupid language. You have to assign the return value to the function name.

Code: Select all

Function NumToLetter (iNum As Integer)
   NumToLetter = chr(iNum +64)
End Function
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
JohnV
Volunteer
Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: [Calc] Function does not pass value to Sub

Post by JohnV »

Put another way, the last line of your Function should be as follows.
NumToLetter = oLet
theMoose
Posts: 8
Joined: Wed Mar 09, 2016 5:36 pm

Re: [Calc] Function does not pass value to Sub

Post by theMoose »

Thanks Villeroy and JohnV for your help. This is one to keep in mind... assigning the return value to the function name.
Also thanks for the more efficient way to go.
=======================================================
AOO 4.1.2 & LibreOffice 5.2.1.2 on Windows 7 & Linux Mint 17.3
Post Reply