whats wrong with this

[raj]

i am new to VBA and VB
i have a function that returns a value
but the retured value is not retained in the caller function
when i step in to the code the callee returns the right value

Sub DataTransformation()
'
Dim LOBCell As Integer
LOBCell = ConvertLetterToNumber("V")
'*************** LOBCell losses value and its 0ed out why??

For x = 2 To 4
lobcode = sourceSheet.Cells(x, LOBCell)
MsgBox ("lobcode is " & lobcode)

Next x

End Sub





Private Function ConvertLetterToNumber(ByVal LtrIn As String) As Integer
Dim TempChar As String
Dim TempNum As Integer
Dim NumArray() As Integer
TempChar = ""
TempNum = 0
LtrIn = UCase(LtrIn)
For i = 1 To Len(LtrIn)
NumString = ""
' Change Ltr to Number Indicating Place in Alphabet from 01 to 26
TempChar = Mid(LtrIn, i, 1)
ReDim Preserve NumArray(i)
NumArray(i) = Asc(TempChar) - 64
Next
' Our Most Significant Digits Occur to the Left
HighPower = UBound(NumArray()) - 1
' Convert the Number Array using Powers of 26
For i = 1 To UBound(NumArray())
TempNum = TempNum + (NumArray(i) * (26 ^ HighPower))
HighPower = HighPower - 1
Next
ConvLtrToNum = TempNum
End Function

 

[Dr. Stephan Kassanke]

i am new to VBA and VB
i have a function that returns a value
but the retured value is not retained in the caller function
when i step in to the code the callee returns the right value

Sub DataTransformation()
'
Dim LOBCell As Integer
LOBCell = ConvertLetterToNumber("V")
'*************** LOBCell losses value and its 0ed out why??

For x = 2 To 4
lobcode = sourceSheet.Cells(x, LOBCell)
MsgBox ("lobcode is " & lobcode)

Next x

End Sub





Private Function ConvertLetterToNumber(ByVal LtrIn As String) As Integer
Dim TempChar As String
Dim TempNum As Integer
Dim NumArray() As Integer
TempChar = ""
TempNum = 0
LtrIn = UCase(LtrIn)
For i = 1 To Len(LtrIn)
NumString = ""
' Change Ltr to Number Indicating Place in Alphabet from 01 to 26
TempChar = Mid(LtrIn, i, 1)
ReDim Preserve NumArray(i)
NumArray(i) = Asc(TempChar) - 64
Next
' Our Most Significant Digits Occur to the Left
HighPower = UBound(NumArray()) - 1
' Convert the Number Array using Powers of 26
For i = 1 To UBound(NumArray())
TempNum = TempNum + (NumArray(i) * (26 ^ HighPower))
HighPower = HighPower - 1
Next
ConvLtrToNum = TempNum
End Function

Hi raj,

assign the return value of your function to the function name

ConvLtrToNum = TempNum

should be

ConvertLetterToNumber= TempNum

Use "Option explicit" at module level to force explicit declaration of all
variables in that module to avoid these kind of errors.

Stephan

 

[Raymond Cruz]

Raj,
I don't think anyone can give an answer unless you show us
ConvertLetterToNumber()

RC

 

[raj]

ah crap i knew it would be something like that small
haha

thanks a lot