• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conflicts between Dim, ReDim and Split

B

BobBridges

Active Member
Brand new to the forum; please don't hesitate to blast me if I violate a local taboo. I'm looking for a good active VBA forum; if you don't think this is it, I'll gratefully accept nominations for a better.

Here's a problem I ran into yesterday 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?
 
Last edited by a moderator:
You could use another variable to avoid the error !​
​
As it's far better to post the entire procedure rather than pieces​
with a crystal clear explanation of what you are trying to achieve and the attachment accordingly …​
​
 
Yeah, I had to use a new variable. I just don't understand why. Is there something in the documentation that points out (I didn't see it) that I can't use Split to assign an array to a variable declared as Dim xxx()?

As for posting the whole program, I suppose I could have posted more. But you wouldn't want to see the whole thing; it's only a small part of it that wasn't working. The whole thing is a couple dozen modules long (off-hand I don't know how many dozen), and the module in which this happened is so far probably about 120 lines long.

In a way my question isn't about the program logic; it's about an odd behavior of the interpreter (or at least I don't understand the behavior). If it helps to do it this way:
Code: 
'no Dim statement for ar()
ar = Split(vm)

' Then later in the program:
ReDim ar(1 to rZ, 1 to cZ)

This fails at compile time, saying that ReDim is invalid for ar. But when I include the Dim statement:
Code: 
Dim ar()
ar = Split(vm)

' Then later in the program:
ReDim ar(1 to rZ, 1 to cZ)

...it consents to run the program, but fails at the Split statement saying it's an "invalid assignment".

I "solved" it by changing the name of the variable that received the results of the Split function. I just don't understand why it was necessary.
 
Last edited by a moderator:
That's could be normal for Split but without the entire code I won't argue,​
see the many samples from this forum …​
​
 
Not directly, no, but it makes such a careful distinction between fixed and dynamic arrays that it gets me thinking: When I assign the results of a Split function to a Variant variable, does it create a fixed array, or dynamic? That might—probably would—explain why it won't allow me to ReDim an array once it's received an array from Split. Thanks; I don't know whether that's the answer, but at least it makes sense.
 
Well, as I said, when I didn't declare it in a Dim statement, but just let it be assigned the result of the Split call, then before the program even ran—during the "compile" phase—the VBA editor complained that the later ReDim statement would be invalid. When I added "Dim ar()", it waited until it started executing, and then complained that the Split call is an "invalid assignment". So it sounds like either method is wrong. I could, I suppose, experiment with the Dim statement to see whether it makes any difference if it's not a Variant data type. But no; it kind of has to be variant, because I'm assigning an Excel range to it.
 
Thanks to use the standard font as it's more readable than yours …​
​
Again, as it just depends on how you write the code, post it here entirely or​
better attach the workbook with your best explanation of the context …​
​
 
BobBridges said:
Yeah, I had to use a new variable. I just don't understand why. Is there something in the documentation that points out (I didn't see it) that I can't use Split to assign an array to a variable declared as Dim xxx()?

As for posting the whole program, I suppose I could have posted more. But you wouldn't want to see the whole thing; it's only a small part of it that wasn't working. The whole thing is a couple dozen modules long (off-hand I don't know how many dozen), and the module in which this happened is so far probably about 120 lines long.

In a way my question isn't about the program logic; it's about an odd behavior of the interpreter (or at least I don't understand the behavior). If it helps to do it this way:
Code: 
'no Dim statement for ar()
ar = Split(vm)

' Then later in the program:
ReDim ar(1 to rZ, 1 to cZ)

This fails at compile time, saying that ReDim is invalid for ar. But when I include the Dim statement:
Code: 
Dim ar()
ar = Split(vm)

' Then later in the program:
ReDim ar(1 to rZ, 1 to cZ)

...it consents to run the program, but fails at the Split statement saying it's an "invalid assignment".

I "solved" it by changing the name of the variable that received the results of the Split function. I just don't understand why it was necessary.
Click to expand...
Following code works
Code: 
Dim arSplit() As String
arSplit = Split("a b", " ")
Range("A1:B1").Value = arSplit
It fails at your end on the line specified because you are not assigning the data type. Split generates output in string format. Does that answer a part of your query?
 
Luke M said:
@BobBridges...remember to include links to your cross-post....
Click to expand...

You know, I posted that so long ago I didn't even think of it as a cross-post; I wasn't optimistic even when I posted it there about getting an answer, and after a period of deafening silence I decided it was time to find me a forum where I could post the more advanced VBA questions. So I came here, having abandoned any hope of getting a reply there. Sorry, no offense intended. I'll remember.
 
I don't think that can be it, at least not by itself. This works fine:
Code: 
Sub Main()
  Dim ar
  ar = Split("A B")
  End Sub

It's when I combined Dim ar() with Split and ReDim—oh, wait, though, I forgot to Dim ar(). When I do that...well, whaddya know! This works:
Code: 
Sub Main()
  Dim ar() As String
  ar = Split("A B")
  ReDim ar(1 To 3, 2 To 5)
  End Sub

I don't know why that's necessary, but I'll keep it in mind....I hope :). Thanks.
 
Last edited by a moderator:
Yes, as I wrote in post #7 :​
Marc L said:
As it just depends on how you declare the variable …
Click to expand...
But that seems to have no relation with your original issue, the reason why I asked for the entire code several times since post #2 …​
​
This also works :​
Code: 
Sub Demo0()
    Dim V
        V = Split("A B")
  ReDim V(3, 4)
End Sub
 
BobBridges said:
I don't think that can be it, at least not by itself. This works fine:
Code: 
Sub Main()
  Dim ar
  ar = Split("A B")
  End Sub

It's when I combined Dim ar() with Split and ReDim—oh, wait, though, I forgot to Dim ar(). When I do that...well, whaddya know! This works:
Code: 
Sub Main()
  Dim ar() As String
  ar = Split("A B")
  ReDim ar(1 To 3, 2 To 5)
  End Sub

I don't know why that's necessary, but I'll keep it in mind....I hope :). Thanks.
Click to expand...
In the first piece of code you have posted, variable ar is variant and it can accept all data types.
 
BobBridges said:
I wasn't optimistic even when I posted it there about getting an answer, and after a period of deafening silence I decided it was time to find me a forum where I could post the more advanced VBA questions.
Click to expand...
The fact you apparently marked the thread as 'solved' at MSOfficeForums probably explains why you didn't get a reply there...
 
Hi, Paul! Nah, I marked it "solved" only after I started getting answers here. I like MSOfficeForums, really I do; for Excel functionality it's very good. But when I'm coding in VBA I tend to ask weirdo questions, and for those I often get silence over there.
 
You must log in or register to reply here.
Back
Top