Why won't split work for me?

[JenniferMurphy]

I copied this example from one of the M$FT pages, but when I try it, it gets a Run-time error "13": Type mismatch.

Dim sPrioAskCols As String
sPrioAskCols = "1 4 7 10 13"
Dim PrioAskCols() As Variant
PrioAskCols = Split(sPrioAskCols)

What am I doing wrong?

 

[maabadi]

I think it should be:

Dim sPrioAskCols As String
sPrioAskCols = "1 4 7 10 13"
Dim PrioAskCols() As String
PrioAskCols = Split(sPrioAskCols)

Then Use CLng or CDbl to convert them to number

 

[JenniferMurphy]

I think I figured out the problem. I took my example from this page3:

Split function (Visual Basic for Applications)

It clearly says to declare the array as Variant. I found another page that says it must be a String. I changed it and now it works.

 

[JenniferMurphy]

I think it should be:

Dim sPrioAskCols As String
sPrioAskCols = "1 4 7 10 13"
Dim PrioAskCols() As String
PrioAskCols = Split(sPrioAskCols)

Then Use CLng or CDbl to convert them to number

Yes, thanks. I found that on another page.

 

[JenniferMurphy]

Then Use CLng or CDbl to convert them to number

Do I really need to convert them? They seem to work without conversion:

Const sPrioAskCols As String = " 1 4 7 10 13"
Dim PrioAskCols() As String
PrioAskCols = Split(sPrioAskCols)

?prioaskcols(1)
1
?prioaskcols(1)+5
 6

Anything wrong with that?

 

[maabadi]

No. No problem. Sometimes we have. But in your case don't need.

 

[Rick Rothstein]

I copied this example from one of the M$FT pages, but when I try it, it gets a Run-time error "13": Type mismatch.

Dim sPrioAskCols As String
sPrioAskCols = "1 4 7 10 13"
Dim PrioAskCols() As Variant
PrioAskCols = Split(sPrioAskCols)

What am I doing wrong?

Your code will work fine if you simply remove the parentheses from the PrioAskCols declaration (regular Variants can hold anything including arrays).

  Dim sPrioAskCols As String
  sPrioAskCols = "1 4 7 10 13"
  Dim PrioAskCols As Variant
  PrioAskCols = Split(sPrioAskCols)

 

[JenniferMurphy]

No. No problem. Sometimes we have. But in your case don't need.

OK, thanks. If I did want to convert the data to long integers, how would I do that? Would I need to define another array variable?

 

[JenniferMurphy]

Your code will work fine if you simply remove the parentheses from the PrioAskCols declaration (regular Variants can hold anything including arrays).

  Dim sPrioAskCols As String
  sPrioAskCols = "1 4 7 10 13"
  Dim PrioAskCols As Variant
  PrioAskCols = Split(sPrioAskCols)

Thank you. That works. I now have three methods. Is any one of them preferable?

1. Declare it as Variant (without the parens)? This is how I plan to go.
2. Declare it as a String array?
3. Declare it as a String array and then convert it to long integer?

 

[Rick Rothstein]

I don't think the method is all that important although my preference is for declaring the variable as Variant (like you plan to do)... that way, VB will use the value as needed (you will not have to convert it to Longs first). And as you noted earlier, VB will convert a string number to a real number when you use it in a calculation but I think doing that may require an extra internal step on VB's part.