CODEWITHSUNDEEP
vbaexcel
cat22
cat22

Reputation: 21

When does VBA change variable type without being asked to?

I am getting a runtime error I don't understand in Excel 2011 for Mac under OS X 10.7.5. Here is a summary of the code:

Dim h, n, k as Integer
Dim report as Workbook
Dim r1 as Worksheet
Dim t, newline as String
Dim line() as String

newline = vbCr
'
' (code to get user input from a text box, to select a worksheet by number)
'
ReDim line(report.Sheets.Count + 10)
MsgBox "Array line has " & UBound(line) & " elements."   '----> 21 elements
line = split(t, newline)
h = UBound(line)
MsgBox "Array line has " & h & " elements."              '----> 16 elements
n = 0
MsgBox TypeName(n)              '----> Integer
For k = h To 1 Step -1
    If IsNumeric(line(k)) Then
        n = line(k)
        Exit For
    End If
Next k
If n > 0 Then
    MsgBox n                    '----> 7
    MsgBox TypeName(n)          '----> String
    Set r1 = report.Sheets(n)   '----> Runtime error "Subscript out of bounds"

So n is declared as an integer, but now VBA thinks it is a string and looks for a worksheet named "7". Is this a platform bug, or is there something I haven't learned yet?

It also surprises me that putting data into the dynamic array reduces its dimension, but perhaps that is normal, or perhaps for dynamic arrays Ubound returns the last used element instead of the dimension, although I have not seen that documented.

Upvotes: 1

Views: 462

Answers (1)

Comintern
Comintern

Reputation: 22185

The first part of your question is answered by @ScottCraner in the comments - the correct syntax for declaring multiple strongly typed variables on one line is:

Dim h As Integer, n As Integer, k As Integer
'...
Dim t As String, newline As String

So, I'll address the second part of your question specific to UBound - unless you've declared Option Base 1 at the top of the module, your arrays start at element 0 by default, not element 1. However, the Split function always returns a 0 based array (unless you split a vbNullString, in which case you get a LBound of -1):

Private Sub ArrayBounds()
    Dim foo() As String

    'Always returns 3, regardless of Option Base:
    foo = Split("zero,one,two,three", ",")
    MsgBox UBound(foo)

    ReDim foo(4)
    'Option Base 1 returns 1,4
    'Option Base 0 (default) returns 0,3
    MsgBox LBound(foo) & "," & UBound(foo)
End Sub

That means this line is extremely misleading...

h = UBound(line)
MsgBox "Array line has " & h & " elements."

...because the Array line actually has h + 1 elements, which means that your loop here...

For k = h To 1 Step -1
    If IsNumeric(line(k)) Then
        n = line(k)
        Exit For
    End If
Next k

...is actually skipping element 0. You don't really even need the h variable at all - you can just make your loop parameter this...

For k = UBound(line) To LBound(line) Step -1
    If IsNumeric(line(k)) Then
        n = line(k)
        Exit For
    End If
Next k

...and not have to worry what the base of the array is.

BTW, not asked, but storing vbCr as a variable here...

newline = vbCr

...isn't necessary at all, and opens the door for all kinds of other problems if you intend that a "newline" is always vbCr. Just use the pre-defined constant vbCr directly.

Upvotes: 3

Related Questions