0

I am using a couple of arrays defined by e.g.

  public arr1() as variant

and they strictly need the ()-part after the variable name to work in the preexisting code.

however if I try to rearrange them by 1) joining text by a delimiter ":-:" (filltering out unwanted data in a for-loop), the split function don't work:

arr1=Split(tmpString, ":-:")

And again I am strictly restrained to using the variants as defined above.

I am very greatfull for any help!

Hallvard Skrede
  • 13
  • 5
  • `Split` returns an array of `String`s, so you need to convert the result to an array of `Variant`s, as demonstrated in the linked thread. – BigBen Aug 04 '23 at 17:21
  • Thanks, I just found another solution here: https://stackoverflow.com/questions/37515352/how-to-convert-string-type-array-to-variant-type-array-excel-vba – Hallvard Skrede Aug 04 '23 at 18:07
  • That's another option, but note it's limited to Excel. The linked solution is host-agnostic. – BigBen Aug 04 '23 at 18:08
  • There the solution is to insert the string-array into cells in worksheet and then back again from there to a predefined array() in the variant-form. Worked perfectly :) – Hallvard Skrede Aug 04 '23 at 18:09
  • You don't need to write to the worksheet at all. Example: `Dim arr() As Variant` `arr = WorksheetFunction.Index(Split("foo;bar", ";"), 1, 0)`. – BigBen Aug 04 '23 at 18:11
  • Ah, well, yes and no-, It is now an array in the string format, as seen in the "locals"-window where the array-elements are defined as "Variant/String". So if the variables need specifically to be "Variant/Variant", then it doesn't work, unfortunately.... :( – Hallvard Skrede Aug 04 '23 at 18:25
  • That detail was not specified in your question, but you can easily modify the first approach (loop) to cast each `String` to a `Variant`. – BigBen Aug 04 '23 at 18:28
  • 1
    Oh, I am sorry, I found out now that it was indeed variant/string. My bad. I were confusing two variables with each other. Thank you for the help. Then i don't need the intermediate steep anymore either :) Have a good weekend – Hallvard Skrede Aug 04 '23 at 18:30

0 Answers0