none
Got #VALUE on Long string handling in UDF in VBA (excel) RRS feed

  • Question

  • Hi, I've encounter an issue when using an UDF returning an array with long strings (>256 symbols).

    Sample Code:

    Function longString() As Variant
            Dim res(1 To 1, 1 To 2)
            res(1, 1) = "hellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhellohh\n"
            res(1, 2) = "world"
            longString = res
    End Function


    When calling longString()  as an array formula in a cell, the cell got #Value error, but through debugging, longString() returns without error.

    how can i  resolve this issue?


    • Edited by technic_tec Tuesday, December 30, 2014 6:03 AM
    Tuesday, December 30, 2014 6:02 AM

Answers

  • With this UDF:

    Function longString()
            Dim res(1 To 1, 1 To 2) As String
            res(1, 1) = "hellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhellohh\n"
            res(1, 2) = "world"
            longString = res
    End Function
    Note we have DIM'ed res rather than LongString

    Select cells A1 & B1 and enter:

    =LongString()  using CNTRL-SHFT-ENTER and you should see:


    gsnu201408



    Tuesday, December 30, 2014 5:46 PM
    Moderator

All replies

  • The array must be type of string.

    Andreas.

    Tuesday, December 30, 2014 2:09 PM
  • With this UDF:

    Function longString()
            Dim res(1 To 1, 1 To 2) As String
            res(1, 1) = "hellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhellohh\n"
            res(1, 2) = "world"
            longString = res
    End Function
    Note we have DIM'ed res rather than LongString

    Select cells A1 & B1 and enter:

    =LongString()  using CNTRL-SHFT-ENTER and you should see:


    gsnu201408



    Tuesday, December 30, 2014 5:46 PM
    Moderator