Returning an Array of Arrays (VBA Excel 97+)

Topic

New Reply

I seem to have found a bug in Excel, again.

Apparently it is not possible to return an array from a user defined function containing strings longer than 255 chars if the array also contains numbers.

I have asked this question in http://www.experts-exchange.com/Applicatio…Q_20659869.html%5B/url%5D .
It was confirmed by several people, if you want to see this for yourself, use this little demo routine:

Function cseStrDemo(strToRepeat As String, blnRedimAsString As Boolean)
  'Demo to show there is a 255 char limit unless 'mixed' array declared as Str
  
  'Att: Array function, hence the the ctrl+shift+enter reminder
  '     Setup for a size of 25 rows and 3 columns

  Dim arrStr As Variant, i As Integer, strTemp As String
  
  If blnRedimAsString = True Then
    ReDim arrStr(1 To 25, 1 To 3) As String
  Else
    ReDim arrStr(1 To 25, 1 To 3)
  End If
  
  For i = 1 To 25
    strTemp = strTemp & strToRepeat
    arrStr(i, 1) = i
    arrStr(i, 2) = Len(strTemp)
    arrStr(i, 3) = strTemp
  Next i
  cseStrDemo = arrStr
End Function
     

The work-around I (finally) came up with, using an array of arrays, does not work if I want to return the arrays column-wise.
I do not know what I am doing wrong (other than using Excel). The MSKB does not show anything other than returning
arrays to objects, an internet seach did not help either. And the Search here is not possible, so I might asking about
a known problem. Does anybody see a solution here or is this hopeless ?

 
Function cseStrDemo2(strToRepeat As String)
  'Work-around for the 255 char limit for 'mixed' arrays
  
  'Attention: Array function, hence the ctrl+shift+enter prefix as reminder
  '           The columnwise return does not work, why ?
  '           Using Transpose will *CRASH* Excel 97 if resulting string > 255 chars
  
  Dim arrArray As Variant, arr1 As Variant, arr2 As Variant, arr3 As Variant
  Dim i As Integer, strTemp As String
  
  ReDim arrArray(1 To 3) As Variant
  ReDim arr1(1 To 25) As Integer
  ReDim arr2(1 To 25) As Long
  ReDim arr3(1 To 25) As String
  
  For i = 1 To 25
    strTemp = strTemp & strToRepeat
    arr1(i) = i
    arr2(i) = Len(strTemp)
    arr3(i) = strTemp
  Next i
  
  arrArray(1) = arr1
  arrArray(2) = arr2
  arrArray(3) = arr3
  
  'Return values
  cseStrDemo2 = arrArray
End Function

Reply | Quote

Replies

If I ever used a variant “array” (other than in VBScript, I can’t recall doing it), it certainly never occurred to me that it could be redimensioned as another kind of array. I think it’s an unstable foundation.

Why do you need to build an array of mixed types? Maybe there’s another solution. When I want to build an array of different (strict) types, without using a formal database structure, I create a user-defined type for my data records and then an array of that type. So, for example,

Private Type Beverage
    Name As String
    Price As Currency
    Alcoholic as Boolean
End Type
 
Private arrBeverages() As Beverage

You then assign the individual array elements as through they were object properties:

ReDim arrBeverages(0 To 9)
With arrBeverages(0)
    .Name = "Iced Tea"
    .Price = "1.5"
    .Alcoholic = False
End With
'etc.

Will this work for your application?

Reply | Quote

Thank you for tackling this one.

I can not see how to _return_ the content of my array defined as user defined type to the calling worksheet cells containing the user defined array function. Whew. This example is only a simplification of what I am trying to do. Using Excel as interface I write a input file to a old DOS program, wait for the results (as asynchronous process), open the result file, parse it and return the information to Excel. I could do this as a Sub, but, for several reasons, I want/need this as a function. It runs more or less nicely (250 milliseconds response time on my PC). But it is not totally stable, especially on one PC. Further I really would like to put the output file into a cell (2 kB, it fits for Excel 97), because of documentation. I know, making a DLL out of the source would be faster in the long run, but this is not an option for now.

Private Type IntLngStr
  Test As Integer
  CharNum As Long
  MyTestString As String
End Type
  
Private arrArray() As IntLngStr
  
  
Function cseStrDemo3(strToRepeat As String)
  'Function cseStrDemo3(strToRepeat As String) as IntLngStr 'does not work, either
  
  'Purpose: Example work-around for the 255 char limit for 'mixed' arrays
  
  'Att: Array function, hence ctrl+shift+enter prefix as reminder
  '     Uses array arrArray of user defined type IntLngStr
  
  'Status: Does not even compile. Error message:
  '        "Can't assign or coerce array of fixed-length string or
  '        user defined type to Variant"
  '        If function defined as IntLngStr too, then type mismatch
  
  
  Dim i As Integer, strTemp As String
  ReDim arrArray(1 To 25)

  For i = 1 To 25
    strTemp = strTemp & strToRepeat
    With arrArray(i)
      .Test = i
      .CharNum = Len(strTemp)
      'MsgBox .CharNum 'testing only
      .MyTestString = strTemp
    End With
  Next i
  'Return values
  cseStrDemo3 = arrArray 'does not work
End Function
    

Reply | Quote

What are you doing with the return value of the function? If you have code that it calling it and expecting to receive an array back and is assigning it to a variable, you’ll need to declare the variable in the calling routine as a variant.

Reply | Quote

Charlotte, this VBA function IS an array function which I intend to call from within the worksheet. I return the values to a worksheet range, not to another function, like LINEST or similar Excel functions. If I define the function as Variant it will not compile, complaining that it can not assign/coerce because I employed an user defined type. Its a Catch22 situation.

Reply | Quote

It wasn’t clear from your initial post exactly what you were trying to do with the array. My assumption, and probably Jefferson’s as well, was that you we writing the the values from the array into a location, not that you were trying to insert the array directly into a worksheet range. I haven’t used Excel seriously since version 5, so I can’t be much help to you. However, VBA arrays are very temporary constructs and they don’t really correlate to a range array in Excel.

Reply | Quote

I thought the term was user defined function (UDF). I thank you for your time.

Reply | Quote

Additionally: The demo subroutine is a bit silly, but I can not post my real task, the DOS executable is copyrighted by the corporation I work for. And it has much more bells and whistles. But I am pretty sure these features are not the cause, because the simple example here does not work either.

Reply | Quote

Like Jefferson, I would use a custom type for this. It is the usual approach to handling multiple data types within an array, and I’ve used it many times both in VB and in VBA.

Reply | Quote