VBA Function for alphanumeric function (2000 and 2003)

Topic

New Reply

Attached is a workbook with a VBA function I am trying to create but have run into some unexpected results.
It works in all test situations EXCEPT when the alpha letter to be converted is either “D” or “E”.

It does work for “D” and “E” if the function is reduced to eliminate the If Then and only operates for 3 digit input with a middle letter.

The attached is only a small example. I was hoping that a function could be used because the real example is far more complex (and works except for “D” and “E”) and would be much better than the excel formula that does work but is extremely long and complex..

Regards,

Tom D.

Reply | Quote

Replies

The D and E are used in exponential notation: 3E5 means 3 times 10 to the power 5, i.e. 300000. So IsNumeric(“1D2”) or IsNumeric(“3E7”) both return TRUE. try this variation:

Public Function ConvertIt(x)
If Application.WorksheetFunction.IsText(x) Then
AA = Val(Left(x, 1)) * 1000
BB = Asc(Mid(x, 2, 1)) * 10
CC = Val(Mid(x, 3, 1))
x = 980000 + AA + BB + CC
End If
ConvertIt = x * 1
End Function

By the way, you don’t require variable declaration. This can be dangerous – a small typo may cause your code to return undesired results. See post 380993 for more info. I recommend that you select Tools | Options… in the Visual Basic Editor, and tick “Require Variable Declaration”. This will be annoying in the beginning, because VBA will refuse to run a lot of your code. But in the end, it will save you a lot of grief.

Reply | Quote

Hans:

Thank you explaining the error. Of course you are correct concerning VBA, my version of Excel was just recently upgraded to 2003. This is just another default that I have yet to reset in Excel on the 2003 version.

Regards,

Tom D

Reply | Quote

Hans:

Thank you explaining the error. Of course you are correct concerning VBA, my version of Excel was just recently upgraded to 2003. This is just another default that I have yet to reset in Excel on the 2003 version.

Regards,

Tom D

Reply | Quote

The D and E are used in exponential notation: 3E5 means 3 times 10 to the power 5, i.e. 300000. So IsNumeric(“1D2”) or IsNumeric(“3E7”) both return TRUE. try this variation:

Public Function ConvertIt(x)
If Application.WorksheetFunction.IsText(x) Then
AA = Val(Left(x, 1)) * 1000
BB = Asc(Mid(x, 2, 1)) * 10
CC = Val(Mid(x, 3, 1))
x = 980000 + AA + BB + CC
End If
ConvertIt = x * 1
End Function

By the way, you don’t require variable declaration. This can be dangerous – a small typo may cause your code to return undesired results. See post 380993 for more info. I recommend that you select Tools | Options… in the Visual Basic Editor, and tick “Require Variable Declaration”. This will be annoying in the beginning, because VBA will refuse to run a lot of your code. But in the end, it will save you a lot of grief.

Reply | Quote