Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    263
    Thanks
    0
    Thanked 17 Times in 17 Posts

    VBA Function for alphanumeric function (2000 and 2003)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: VBA Function for alphanumeric function (2000 and 2003)

    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>post 380993</post#> 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.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    263
    Thanks
    0
    Thanked 17 Times in 17 Posts

    Re: VBA Function for alphanumeric function (2000 and 2003)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •