Problem loading an array

[ExcelMonkey]

I have an public array. I am dimensioning it in a sub, then trying to load
the first value into it. Example 1 works. However Example 2 does not work
when I try to load the array in another sub. I am getting a Run-Time Error
13 Type Mismatch. Why is this?

Example 1
Public RangeInventoryArray As Variant
----------------------------------------------
Sub1()
ReDim RangeInventoryArray(0 To 0)
RangeInventoryArray(0) = 2
End Sub

Example 2
Public RangeInventoryArray As Variant
------------------------------------------------
Sub1()
ReDim RangeInventoryArray(0 To 0)
Call Sub2
End Sub

Sub2()
RangeInventoryArray(0) = 2
End Sub

Thanks

EM

 

[Bob Phillips]

It all works fine for me. Is all of the code in the same module?

 

[Dave Peterson]

Your code had some typos, but this version worked fine for me:

Option Explicit
Public RangeInventoryArray As Variant
Public RangeInventoryArray2 As Variant
Sub Sub1()
ReDim RangeInventoryArray(0 To 0)
RangeInventoryArray(0) = 2
MsgBox RangeInventoryArray(0)
End Sub
Sub Sub1A()
ReDim RangeInventoryArray2(0 To 0)
Call Sub2
MsgBox RangeInventoryArray2(0)
End Sub
Sub Sub2()
RangeInventoryArray2(0) = 223
End Sub

Any chance you ran Sub2 directly?

Or you ran Sub1A, then reset the variables (some way), then ran Sub2?

 

[ExcelMonkey]

Sorry Bob I had publically declared the RangeInventoryArray variable and then
Dim'd it again in the first sub. I didn't provide this in the orignal post.
I took out the line "Dim RangeInventoryArray As Variant"

Public RangeInventoryArray As Variant
----------------------------------------------
Sub1()
Dim RangeInventoryArray As Variant
ReDim RangeInventoryArray(0 To 0)
Call Sub2
End Sub

Sub2()
RangeInventoryArray(0) = 2
End Sub

 

[Dave Peterson]

So you see why it doesn't work this way.

You've redim'ed the local variable, but never did anything to the public/global
variable. And that's what Sub2 will see. Sub2 doesn't know anything about the
variable that's local to sub1.

You may want to read about "scope and visibility" in VBA's help.