Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Returning an Array of Arrays (VBA Excel 97+)

    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 .
    It was confirmed by several people, if you want to see this for yourself, use this little demo routine:

    <pre>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

    </pre>

    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 ?

    <pre>
    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

    </pre>


  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Returning an Array of Arrays (VBA Excel 97+)

    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,

    <pre>Private Type Beverage
    Name As String
    Price As Currency
    Alcoholic as Boolean
    End Type

    Private arrBeverages() As Beverage</pre>

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

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

    Will this work for your application?

  4. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Returning an Array of Arrays (VBA Excel 97+)

    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.
    Charlotte

  5. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning an Array of Arrays (VBA Excel 97+)

    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.

    <pre>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

    </pre>


  6. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Returning an Array of Arrays (VBA Excel 97+)

    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.
    Charlotte

  7. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning an Array of Arrays (VBA Excel 97+)

    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, bitching that it can not assign/coerce because I employed an user defined type. Its a Catch22 situation.

  8. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning an Array of Arrays (VBA Excel 97+)

    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.

  9. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Returning an Array of Arrays (VBA Excel 97+)

    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.
    Charlotte

  10. #9
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning an Array of Arrays (VBA Excel 97+)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •