Conflict between Dim, ReDim and Split

BobBridges · #1 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?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mail Merge - split merged documents and rename each split document based on text in header	FuriousD	Word VBA	1	05-12-2019 04:06 AM
Split word file into several PDF using the bookmarks as split positions and name	Fixxxer	Word VBA	7	10-08-2018 01:10 AM
Split function in Excel (split the screen)	Officer_Bierschnitt	Excel	1	07-05-2017 07:02 AM
Folders in your mailbox have name conflict	baclava	Outlook	0	10-19-2016 08:50 AM
Template conflict	b0x4it	Word	0	05-21-2011 07:44 PM