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

GSerg · Accepted Answer · 2016-03-29 22:16:30Z

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, 2016 at 22:16

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:24:12Z

2

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

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered Mar 29, 2016 at 22:15

Ryan

8,0632 gold badges32 silver badges38 bronze badges

1 Comment

Ali_R4v3n Over a year ago

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

Hallvard Skrede · Accepted Answer · 2023-08-04 18:22:16Z

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

Collectives™ on Stack Overflow

VBA Convert array Type

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related