none
The length of string parameters in VBA functions RRS feed

  • Question

  • Hi, everybody. Want to ask a help from the Community to find out which is the max lenght of a string parameter passed to a function. To give a little background, I encountered a problem when developing a macro for Access when passing a string parameter to a function. I used several strings to obtain this parameter. The code did not work right because some characters were left off, yet they were present in the original parts. The length of this parameter might exceed 256 chars, but as declared in VBA manuals the length of a string might be 64K. Does anybody know how to resolve this? Appreciate any help. Thanks.
    Tuesday, January 10, 2012 11:09 AM

Answers

  • Given the fact that I know nothing about Access, my only suggestion is to investigate the alternatives:

    Public Function MyFunc(ByRef s As String) As String

    and

    Public Function MyFunc(ByVal s As String) As String


    gsnu201201
    Tuesday, January 10, 2012 11:20 AM
    Moderator
  • VB/A's own string functions can handle much larger lengths than you're likely to need in practice, eg there are 4 string functions in this lot

    Dim s As String
    s = String(123456789, "A")
    Mid$(s, 123456786, 4) = "-XYZ"
    MsgBox Right$(s, 10) & vbCr & Format(Len(s), "#,##0")

    However object model functions, in your case some Access function, often have limits and are normally documented. Sometimes there are workarounds but it all depends on the particular function and what you are trying to do. I don't know much about Access but maybe others will have suggestions if you explain your objective with a code snippet.

    Peter Thornton

    Tuesday, January 10, 2012 1:28 PM
    Moderator
  • The numbers of characters with using Condition and Arguments in Macro's in Microsoft Access are limited to 255.

     

    So as Peter stated, you might post a sample of what you are trying to accomplish, and see if there is a workaround for your issue.

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Tuesday, January 10, 2012 11:51 PM
    Moderator

All replies

  • Given the fact that I know nothing about Access, my only suggestion is to investigate the alternatives:

    Public Function MyFunc(ByRef s As String) As String

    and

    Public Function MyFunc(ByVal s As String) As String


    gsnu201201
    Tuesday, January 10, 2012 11:20 AM
    Moderator
  • VB/A's own string functions can handle much larger lengths than you're likely to need in practice, eg there are 4 string functions in this lot

    Dim s As String
    s = String(123456789, "A")
    Mid$(s, 123456786, 4) = "-XYZ"
    MsgBox Right$(s, 10) & vbCr & Format(Len(s), "#,##0")

    However object model functions, in your case some Access function, often have limits and are normally documented. Sometimes there are workarounds but it all depends on the particular function and what you are trying to do. I don't know much about Access but maybe others will have suggestions if you explain your objective with a code snippet.

    Peter Thornton

    Tuesday, January 10, 2012 1:28 PM
    Moderator
  • The numbers of characters with using Condition and Arguments in Macro's in Microsoft Access are limited to 255.

     

    So as Peter stated, you might post a sample of what you are trying to accomplish, and see if there is a workaround for your issue.

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Tuesday, January 10, 2012 11:51 PM
    Moderator