VBA Incompatible types Variant/String vs Variant Array

Question

I have a VBA function which works if an array is created, but not is a single value is read:

Dim data()
data = wsData.ListObjects("EvalDevPerson").ListColumns("Funktion").DataBodyRange.Value

This is a table (Listobject) with a column with name "Funktion" and it gives back all values in the column, which works fine if there are more than one value.

Now I get Error 13 because the types are

data: Variant/Variant()
.value: Variant/String

How can I convert between these?

Answer 1

When your listobject has one row only, .DataBodyRange.value returns a single string not an array.

If you always want to return an array you could use this function:

Public Function readFunktion() As Variant

Dim lo As ListObject
Set lo = wsData.ListObjects("EvalDevPerson")

Dim data As Variant
If lo.DataBodyRange.Rows.Count = 1 Then
    ReDim data(1 To 1, 1 To 1)
    data(1, 1) = lo.ListColumns("Funktion").DataBodyRange.Value
Else
    data = lo.ListColumns("Funktion").DataBodyRange.Value
End If

readFunktion = data

End Function