CODEWITHSUNDEEP
arraysexcelvba
Matteo NNZ
Matteo NNZ

Reputation: 12695

Getting full Split array into a VBA variable

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.

Upvotes: 2

Views: 2963

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

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:

  1. 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.

  2. 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.

Upvotes: 5

Related Questions