Microsoft Office Forums - View Single Post - [Solved] Conflict between Dim, ReDim and Split

BobBridges · 04-07-2020, 01:00 PM

I'm not having much luck getting answers to my questions here recently. Weird set of questions, maybe. Here's another one, a problem I ran into today while attempting something I thought would be pretty conventional:

Stage 1: I calculate the number of rows and columns I need, populate the array and then dump it into a worksheet. This works:

Code:

Dim ar()
ReDim ar(1 to rZ, 1 to cZ)
'blah, blah, blah
Range(ows.Cells(1, 1), ows.Cells(rZ, cZ)).Value = ar

Stage 2: Now I find I need a quick array, just for a couple of statements before I get started. No reason, as far as I can see, not to use the same variable name. So somewhere above this code I add this statement:

Code:

ar = Split(vm)

Error: duplicate declaration. Oops—of course if I use ar before the "Dim ar" statement, that's a duplicate declaration. But I don't really need the Dim statement, since I'm already defining ar as an array by assigning it with the Split function. So I simply removed the Dim statement.

Didn't work. When I remove the Dim statement, the VBA interpreter complains at "compile" time that the ReDim statement is invalid.

Ok, apparently I still need "Dim ar()", it just needs to be above the first use of ar. So (Stage 3) now my program has the statements in this order (though not all together):

Code:

Dim ar()
ar = Split(vm)
ReDim ar(1 to rZ, 1 to cZ)
'blah, blah, blah
Range(ows.Cells(1, 1), ows.Cells(rZ, cZ)).Value = ar

Now the program complains, at execution time, that using Split to assign an array to ar is an "invalid assignment".

I'll have to use a different variable name, it appears. But why? I looked up Split and ReDim in the language reference and don't see anything there to indicate where the limitation comes in. Does anyone know?