VBA Convert array Type

Question

In VBA, how do I convert An array from one type to another?, in my case i want to convert an array of type "String" to type "variant" because i have a function parameter that needs an array of that type.

here is an example code,

Sub test_highlighfind()

    Dim Rng As Range: Set Rng = ThisDocument.Range.Paragraphs(6).Range

    Dim arr() As String: arr = Split(Rng.Text)

    Call highlightWordsUsingFind(arr, ThisDocument, 7)

End Sub

Sub highlightWordsUsingFind(ByRef arr() As Variant, ByRef doc As Document, _
                            Optional ByVal HighlightColor As Integer = 6)

    Dim i As Long, SearchRange As Range

    Set SearchRange = doc.Range

    With SearchRange.Find
        .Format = True
        .MatchCase = False
        .MatchWholeWord = True
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        .Forward = True
        .Wrap = wdFindContinue
        .ClearFormatting

        For i = LBound(arr) To UBound(arr)
            .Text = arr(i)
            .Execute    'Execute// Runs the find operation. Returns True if the find operation is successful
            SearchRange.HighlightColorIndex = HighlightColor
        Next
    End With
End Sub

i know i can change the parameter type to "string" ByRef arr() As String but i have other functions that return an array of type "variant" and i need the output to the function above

score 3 · Answer 1 · answered Mar 29 '16 at 22:16

3

Remove the parentheses in your declaration.

Sub highlightWordsUsingFind(ByRef arr As Variant, ...)

Then you can pass arr() directly. It will be wrapped into a Variant/Array that will refer to the original array (so no copying will happen).

answered Mar 29 '16 at 22:16

GSerg

76,472
17
159
346

score 2 · Accepted Answer · edited May 23 '17 at 12:24

Since you wrote the highlightWordsUsingFind sub, the easiest way is to change the parameter to Variant as already mentioned by GSerg. It might be a good idea to ensure the parameter is actually an array with the IsArray function. Here are some other conversion examples.

Another option is to create a new Variant array:

Function StringToVariantArray(ByRef arr() As String) As Variant()
    Dim index As Integer
    Dim result() As Variant
    Redim result(LBound(arr) To UBound(arr))
    For index = LBound(arr) To UBound(arr)
        result(index) = arr(index)
    Next
    StringToVariantArray = result
End Function

Sub test_highlighfind()
    Dim Rng As Range
    Dim varr() As Variant

    Set Rng = ThisDocument.Range.Paragraphs(6).Range
    varr = StringToVariantArray(Split(Rng.Text))

    Call highlightWordsUsingFind(varr, ThisDocument, 7)
End Sub

thanks very much, i had this idea but i kinda hoped that there was a solution without any looping ..thanks again, and hopefully i'll learn new things from you — Ali_R4v3n, Mar 29 '16 at 22:23

score 0 · Answer 3 · answered Aug 04 '23 at 18:22

I just wanted to let people know that another solution has been provided here too:

How to convert string type array to variant type array - Excel VBA

There the solution is to insert the string-array into cells in the worksheet and then back again to the predefined array() => Which is in the "() variant"-form. Worked perfectly for me. :)

It was super important that my array kept its definition in my code, as there was already a lot of preexisting code which used the "array() as variant"-format.

So this worked perfectly for me. I could also started doing some loops and redim/redim preserve but I thought that this solution was best. => To just overwrite the variable with the new array retrieved from the sheet.cells

VBA Convert array Type

3 Answers3

Linked

Related