Function returning null value instead of string value

[shivboy]

Hi,

I have a combo box with some values in it. Now, I want to make a function wherein I pass the value of the combo box as a string parameter and return a specific string value corresponding to the string parameter passed. I am able to pass the parametric value, but the value returned is "blank" or "null" instead of the string value that the function should be returning. Why is this happening? Below is the code that I am using for the function and how I am passing the value into it. Please help.



Function convertDiv(oDiv As String) As String
Dim wDiv, oDivID As String
wDiv = oDiv
If wDiv = "A" Then
oDivID = "1"
ElseIf wDiv = "B" Then
oDivID = "2"
ElseIf wDiv = "C" Then
oDivID = "3"
ElseIf wDiv = "D" Then
oDivID = "4"
ElseIf wDiv = "E" Then
oDivID = "5"
ElseIf wDiv = "F" Then
oDivID = "6"
ElseIf wDiv = "G" Then
oDivID = "7"
End If
convertDiv = oDivID
End Function




Sub init()
Dim a As String
a = convertDiv(Trim(combo1.Value))
lblType.Caption = a
End Sub


Peace,

Shivboy

[Jacob Hilderbrand]

What is the value of combo1.Value?

I am thinking it could be a case problem, i.e. "a" vs. "A". You can take care of that by forcing the case (UCase), or adding Option Compare Text to the top of the module.

If you step through the macro, is any part of the If statement evaluated to True? Probably not since the function is returning "" which is the default value for oDivID.

Also, if the IDs are really sequential and correspond to the order of the items in the ComboBox, you can use the ListIndex to return the value directly. The list index starts at 0 for the first item so you would just need to add one, i.e.

a = combo1.ListIndex + 1

[Ken Puls]

Agree with Jake here. I've added a line to the top of your function to deal with the case issue:

Function convertDiv(oDiv As String) As String
Dim wDiv As String, oDivID As String
oDiv = UCase(oDiv)
wDiv = oDiv
If wDiv = "A" Then
oDivID = "1"
ElseIf wDiv = "B" Then
oDivID = "2"
ElseIf wDiv = "C" Then
oDivID = "3"
ElseIf wDiv = "D" Then
oDivID = "4"
ElseIf wDiv = "E" Then
oDivID = "5"
ElseIf wDiv = "F" Then
oDivID = "6"
ElseIf wDiv = "G" Then
oDivID = "7"
End If
convertDiv = oDivID
End Function
Sub init()
Dim a As String
a = convertDiv(Trim(Me.combo1.Value))
Me.lbltype.Caption = a
End Sub

Also, I made a couple of other little changes. I declared wDiv as Long. You might have thought you had it as a string, but actually it was a Variant. If you don't specify, VBA automatically assigns it an inefficient variant type.

Also, I fully qualified your combobox and label (I assume you're working froma userform here.) This is just a good practice to get in to.

HTH,

[Cyberdude]

You might consider using translation code like this:
Select Case wDiv
Case Is "A": convertDiv = "1"
Case Is "B": convertDiv = "2"
Case Is "C": convertDiv = "3"
Case Is "D": convertDiv = "4"
Case Is "E": convertDiv = "5"
Case Is "F": convertDiv = "6"
Case Is "G": convertDiv = "7"
End Select

[Cyberdude]

An even better translation technique (if it makes sense for your data) is to use a single line of code:
convertDiv = InStr(1,"ABCDEF", wDiv) in which case you could dispense with using a function altogether. Of course, you will need some error checking and use the UCase to standardize the input. Just a thought.

[mdmackillop]

Hi Sid,
Lump it into the same line


convertDiv = InStr(1, "ABCDEFG", UCase(oDiv))