+ Reply to Thread
Results 1 to 10 of 10

Anyone know how to get around the max string size in VBA editor?

  1. #1
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Anyone know how to get around the max string size in VBA editor?

    Anyone know how to get around the max string size in VBA editor?

    I need to build a string that is over 5000 chars inside the editor. Anyone know a way to achieve this?
    Last edited by JapanDave; 07-09-2017 at 06:12 PM.
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Anyone know how to get around the max string size in VBA editor?

    as you will see if you run this macro, a VBA string can be much greater than you believe
    - generated string is "A" repeated 20 million times....
    - your limit problem is something else
    what are you trying to do with the string?
    - post your code enclosed in code tags

    Please Login or Register  to view this content.
    Last edited by kev_; 07-07-2017 at 10:49 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Anyone know how to get around the max string size in VBA editor?

    You mean within the limitations of 1023-character line length and 14 continuation lines?

    Even with that, I could create a 15K character string.

    In the alternative, you could create several shorter strings and catenate them, or put up to 32K characters in a cell and retrieve if from there.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Anyone know how to get around the max string size in VBA editor?

    Quote Originally Posted by shg View Post
    You mean within the limitations of 1023-character line length and 14 continuation lines?

    Even with that, I could create a 15K character string.

    In the alternative, you could create several shorter strings and catenate them, or put up to 32K characters in a cell and retrieve if from there.
    Quote Originally Posted by kev_ View Post
    as you will see if you run this macro, a VBA string can be much greater than you believe
    - generated string is "A" repeated 20 million times....
    - your limit problem is something else
    what are you trying to do with the string?
    - post your code enclosed in code tags

    Please Login or Register  to view this content.
    That is all well and good loading it into a variable. What I am talking about is an actual string in the VBA editor.

    ie,

    Please Login or Register  to view this content.
    Paste this into your VB editor and see how you go. See if you can get that whole variable to work for you?
    Last edited by JapanDave; 07-07-2017 at 11:21 AM.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Anyone know how to get around the max string size in VBA editor?

    Please Login or Register  to view this content.

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Anyone know how to get around the max string size in VBA editor?

    Why would you want to type a 5k+ character string directly into your code, if I might ask?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  7. #7
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Anyone know how to get around the max string size in VBA editor?

    Quote Originally Posted by shg View Post
    Please Login or Register  to view this content.
    OK, so you need to apply line breaks to get it to work. Nice work around.

    Quote Originally Posted by xlnitwit View Post
    Why would you want to type a 5k+ character string directly into your code, if I might ask?
    B/c I am working in C# and it is easier to get what data you want there and then pass it to Excel to manipulate the data. I am building a macro with com objects in C# to make a file that people can us.

  8. #8
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Anyone know how to get around the max string size in VBA editor?

    Bear in mind there's a max of 24 line continuations allowed in a statement (25 'physical' lines in the editor). To get around that you'll have to split the string

    Please Login or Register  to view this content.

  9. #9
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Anyone know how to get around the max string size in VBA editor?

    Quote Originally Posted by shg View Post
    Please Login or Register  to view this content.
    This did the trick.
    Thanks

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Anyone know how to get around the max string size in VBA editor?

    You're welcome.

+ 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. [SOLVED] How to change the VBA Editor's font size?
    By Mariano B. in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-13-2020, 12:03 AM
  2. Max string size / best way to loop through 200k rows
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-12-2017, 06:51 AM
  3. [SOLVED] How to return a string of unknown size from DLL to Excel Visual Basic in C ?
    By u24c02 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-15-2017, 11:44 AM
  4. [SOLVED] extract multi-letter string bits from string depending on size and case
    By luv2glyd in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-18-2017, 10:41 PM
  5. split of string with multiple section of various size spaces
    By katto01 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-29-2013, 04:50 PM
  6. VBA String Editor For Excel 2007
    By GWilliams in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-01-2009, 03:52 PM
  7. Excel 2007 : VBA String Editor For Excel 2007
    By GWilliams in forum Excel General
    Replies: 0
    Last Post: 04-07-2009, 12:42 PM

Tags for this Thread

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