Getting full Split array into a VBA variable

Question

Say I have a path in Range("A1") that looks like this:

/data/apps/server/

I would like to get the three elements into a variable. I've thought doing a Split() by the separator / I would get the full array:

Dim myElements()
myElements = Split(Range("A1").Value,"/")
'>>> EXPECTED: myElements is [data, apps, servers]

but I actually get a Type mismatch error on the line myElements = Split(Range("A1").Value,"/"). What does the Split function return? Does it actually return the array or it rather gives read-only access?

I would just like to get the array of the Split method without having to loop through them and build my own array, if possible of course.

You can't use Dim elements() .Change the name of your array and it will work. Use Dim Myelements — genespos
– genespos, Commented May 20, 2015 at 10:24
@genespos that's just a mock name I've used for sampling, but the real variable name is sure not a reserved one. But for sake of clarity I'm going to edit my question, thank you for the remark. — Matteo NNZ
– Matteo NNZ, Commented May 20, 2015 at 10:27
@MatteoNNZ: Make that small change that I suggested and it will work :) — Siddharth Rout
– Siddharth Rout, Commented May 20, 2015 at 10:28

Siddharth Rout · Accepted Answer · 2015-05-20 11:29:22Z

5

Change Dim elements() to Dim elements As Variant

You need to declare it as a Variant.

Explanation:

The data in Excel cell can be anything. So use a Variant. In cases like below, you know it is a String so declare it like a String

Sub Sample()
    Dim myElements() As String
    Dim myString As String

    myString = "aaa/bbb/ccc"

    myElements = Split(myString, "/")

    Debug.Print myElements(0)
    Debug.Print myElements(1)
    Debug.Print myElements(2)
End Sub

Split returns a String Array. You may want to see This

Edit: I have a feeling that I may confuse someone with my explanation so let me explain it a bit more.

Dim myElements() means "Declare myElements as array of Variants". Split returns an array of Strings. Hence, the mismatch.

You can do either Dim myElements or Dim myElements as Variant or Dim myElements() as String to resolve the problem.

Here is why each one of these works:

Dim myElements and Dim myElements as Variant

Both of these means that you declare myElements as Variant. Variants are special types, which can accept anything. As such, they can accept array of strings easily. However, variants have large memory overheads and should be avoided wherever possible.
Dim myElements() as String

This means that you declare myElements as array of strings. Since this is the same type as what is returned by the Split function, it is accepted.

Ideally, if you know the return type of a function, you should specify the correct type for your variables. So in this case, Dim myElements() as String which is the same type returned from the Split funcition.

edited May 20, 2015 at 11:29

answered May 20, 2015 at 10:24

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Matteo NNZ Over a year ago

Thanks a lot Siddhart, it works as a charme. Would you mind explaining me why? I mean, what I get in the variable is not an array?

Pradeep Kumar Over a year ago

++ Wish you were my teacher when I was learning VBA... LOL. You would have explained everything so nicely.. For FREE :D

Siddharth Rout Over a year ago

Stop embarrassing me @PradeepKumar. :D

Pradeep Kumar Over a year ago

Heh Heh. No, Seriously, I like the way you take the time to explain. BTW Congrats on 70k ;)

Siddharth Rout Over a year ago

Damn I didn't realize! Thanky Thanky :)

Collectives™ on Stack Overflow

Getting full Split array into a VBA variable

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related