none
VBA, user defined function, VBA variants and long strings RRS feed

  • Question

  • Hello,

    we have a XLL which declares a udf named "myfunc" with type UUUUUU (expecting five XLOPER12 and returning one Xloper12).

    This function can now be used also in VBA.

    Dim cell_array(2) As Variant, data_array(2) As Variant
    ' fill cell_array and data_array
     On Error Resume Next
    Application.Run("myfunc", "localhost/Biker", "Comment", cell_array, data_array, 0)
    If Err Then
    	MsgBox "Error " & CStr(Err.Number) & ", " & Err.Description, vbCritical, "Test"
    Else
    	MsgBox "ok.", vbInformation, "Test"
    

    If data_array(i) for i=0 to 2 is either a double or a string of length < 256 then myfunc is called.

    But if data_array(0) is Null or a string of length 519 then myfunc is not called and err says type mismatch.

    Is there a way to get this working ?

    tia

      Hendrik Schmieder

    Wednesday, September 2, 2015 2:19 PM

All replies

  • Hi h_schmieder,

    I am not very familiar with XLOPER12, if there is something wrong in my post, please feel free to correct me.

    As my research about XLOPER12, it seems that there is something limitations about XLOPER12. According the link below, it seems it could not be null and max length is 255.

    # C/C++ strings
    https://msdn.microsoft.com/en-us/library/office/bb687840.aspx
    With the link below, it seems the limitation of 255 has been fixed for excel 2007 with KB956836.

    # Excel 2007 XLL SDK Unicode String Support Update
    http://blogs.msdn.com/b/vsofficedeveloper/archive/2008/09/10/xl12-xll-unicode-update.aspx

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, September 3, 2015 7:17 AM
  • The Problem are not the XLOPER12, but the VBA handling of variant Arrays when an entry of the array is a string longer than 255.

    In this case myfunc isn't even called by Excel.

    Thursday, September 3, 2015 8:28 AM
  • Hi h_schmieder,

    >> The Problem are not the XLOPER12, but the VBA handling of variant Arrays when an entry of the array is a string longer than 255.

    As far as I know, VBA do not have the 255 limit in variant Arrays, you could define a new variant Array, and use it in a simple function like print it. With your issue, I assume it is related with the function myfunc in xll.

    >> In this case myfunc isn't even called by Excel.
    I am not sure what do you mean by this? Where is myfunc called.

    It would be helpful if you could share us a simple demo to reproduce your issue at our side.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, September 7, 2015 11:49 AM
  • Sorry for the delay.

    There's definitly a problem  when such a variant array is used as argument  of  application.run.

    myfunc is called via application.run see my vba example code in my original post.

    With

    Dim data_array(2) As String
    
    myfunc is called  even if one of the string in data_array is longer than 255.

    Wednesday, September 16, 2015 8:26 AM