Search code examples
excelvbastringvariantlistobject

Pass ListObject to array. type variable String error

The idea is passing the complete content of a listobject.databodyrange to an array to make operations in memory and not having to access the sheets cells values repeatedly which is very time consuming.

this is the code.

 Dim theArray As Variant
     theArray = mylistObject.DataBodyRange.value

MsgBox (theArray(1, 1)) '= column 1 row 1 = first element

It works, so far so good.

but!!! since theArray is dimensioned as Variant, their elements are NOT strings, So when passing every of the values of theArray into a function that requires a string an error appears.

what to do?

Note: I know I might change the data type of the function itself to variant, but this function is called from so many routines that i dont dare to touch it. I rather prefer try to look for the way to transform the content of that variant into a string

like theArray(i,j) to str(thearray(i,j)) (which does not work)

some help, some ideas?

EDIT 1: this is the line of the error:

Dim theclaims As Variant
      theclaims = rawClsTbl.DataBodyRange.value
For i = LBound(theclaims, 1) To UBound(theclaims, 1)
myText = deleteRefSigns(theclaims(i, 2))
etc

error: byref argument type missmatch

where: Function deleteRefSigns(txT As String) As String

i will be trying the solutions proposed.

thx

Related questions: I asked in overflow myself this question some time ago: Passing Listobject Range to array and getting error "out of range" and read also this one Excel VBA Type Mismatch Error passing range to array and several others.

Solution

  • The following should work:

    Dim MyStr As String
MyStr = CStr(TheArray(1, 1))

    Note: Always declare it as a forced array not just as Variant …

    Dim TheArray() As Variant 'Variant array (can only be an array)
Dim TheArray As Variant 'Variant (can be a value or array)

    … to ensure it contains an array. Otherwise it will contain only a value if you do 

    TheArray = Range("A1").Value

    which might easily fail if your range is dynamic.

    If you read a range into an array like

    Dim TheArray() As Variant
TheArray = Range("A1:C20").Value

    then there is no possibility to declare the array as String it is forced to be Variant by design.