+ Reply to Thread
Results 1 to 9 of 9

Workaround to the 32767 character limit for strings in VBA?

  1. #1
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Workaround to the 32767 character limit for strings in VBA?

    Hi guys,

    I'm handling a website .responestext in excel, which is more than the 32767 character limit for strings in VBA.
    Is there a workaround to this, other then splitting the responsetext up in more than 1 string?
    Thanks in advance for any reply.
    Please click the * below if this helps

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Workaround to the 32767 character limit for strings in VBA?

    that character limit is for cells, not String variables in VBA
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Workaround to the 32767 character limit for strings in VBA?

    Hi Joseph,

    I'd think so too, but if I most the .responstext to a string called "rtext" and then look at the string, I cannot find the reference I'm looking for.
    If I do as follows :

    Please Login or Register  to view this content.
    Then my reference is found in rtext2, which led me to believe there's a 32767 character limit...
    (It's worksheetcode and not in a module)
    What am I missing here?

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Workaround to the 32767 character limit for strings in VBA?

    Hi,

    As far as I'm aware the string limit in VBA is 2^31.

    Are you really asking about the cell limit for characters in the Excel application which is indeed 32767.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Workaround to the 32767 character limit for strings in VBA?

    Hi Richard,

    thanks so much for your reply.
    Indeed, I was under the same impression. Weirdly enough, I nowhere put the .responsetext in any cell / worksheet. I only handle it in the code.
    I'm using Office Standard 2010.
    It really doesn't make sense to me, but if there's no direct solution, I'll just split the responsetext over several strings, I guess
    Thanks!

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Workaround to the 32767 character limit for strings in VBA?

    how are you searching the string? have you tested the length of the string variable using len?

  7. #7
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Workaround to the 32767 character limit for strings in VBA?

    Jasper:

    You can run something like this to show that the 32767 limit doesn't apply:
    Please Login or Register  to view this content.

  8. #8
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Workaround to the 32767 character limit for strings in VBA?

    Hi Joseph,

    once you said "how are you searching the string", you put the finger on the sore spot which I had missed.
    I was using Instr, which I then referred to ... an integer....

    I changed this into LONG and it's working fine... Stupid me.
    Thanks for shaking up my brain and sorry for wasting your time, guys!

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Workaround to the 32767 character limit for strings in VBA?

    @ Jasper

    Don't beat yourself up. We've all been there and got many T shirts.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Workaround for HYPERLINK argument length limit
    By aboorkuma in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-09-2012, 11:20 AM
  2. Workaround needed for 255 character find limit
    By NukedWhale in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-27-2009, 04:10 PM
  3. VB 255 character limit for strings
    By Brett in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-11-2006, 04:15 PM
  4. Cell of sheet Not accepting 32767 character
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-09-2006, 11:55 AM
  5. busted workaround for 255 character limit in cells
    By mklapp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-11-2005, 05:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1