Mac Excel VB Error - Type Mismatch - Variant

Discussion in 'Mac Programming' started by umopapisdn, Dec 8, 2009.

  1. umopapisdn macrumors newbie

    Joined:
    Dec 8, 2009
    #1
    Hi, I have this code working fine on Windows Excel. AFAIK it should work fine with Mac Excel 2004. Currently it throws up and error saying type mismatch on line 11 .
    Does anyone know what is going on?

    1. Public Function NumPPLNumeric() As Integer
    2. Dim Y As Variant
    3. Dim intRow As Integer
    4. Dim intCol As Integer
    5.
    6. intRow = 1
    7. intCol = 1
    8.
    9. Y = Range("weight").Offset(intRow, -3).Value
    10.
    11. Do While (Y > 0 And Y <> " ")
    12. NumPPLNumeric = NumPPLNumeric + 1
    13. intRow = intRow + 1
    14. Y = Range("weight").Offset(intRow, -3).Value
    15. Loop
    16.
    17. End Function
     
  2. mags631 Guest

    Joined:
    Mar 6, 2007
    #2
    Can't explain why it works differently (other then the obvious, VBA must be implemented differently between the two versions). My guess is that there is no short-circuit evaluation in VBA.

    So, a safer implementation might be:
    Code:
    Do While True
       If IsText(Y) Then
          If Trim(Y) = "" Then
             Goto Done
          End If
       ElseIf IsNumeric(Y) Then
           If Y <= 0 Then
              Goto Done
           End If
       End If
       ... Your lines 12-14
    End Loop
    Done:
    
    End Function
    I did not test the above -- apologies in advance if it did not help.
     

Share This Page