VBA maximum line length and workarounds

**knewbry** · 02-16-2012, 06:16 PM

Hello all,

I have an extremely long (1440 characters) formula that I'm trying to add to a VBA script to paste into a range of cells. The formula itself works fine in a spreadsheet, and the VBA script to select my cell range and paste into the cells also works fine. The problem is that whenever I try to paste my formula into my script, I get an error.

It would seem that there is a maximum character length to lines in VBA, which is too low to accomodate my whole formula. Can someone please confirm if that is the case and if so are there any workarounds?

I considered breaking the formula apart into text strings, pasting them into empty cells, then concatenating the contents of the cell back into a single string and pasting that string into my range of cells, but I couldn't make that work either. And of course it would be nice if I didn't have to go that route.

Here is my VBA script:

Please Login or Register  to view this content.

And here is my unwieldy formula formatted with double quotation marks for pasting into my script:

Please Login or Register  to view this content.

The formula itself is basically irrelevant because it works fine on its own. Only the length of it seems to be significant.

Thanks in advance.

**JapanDave** · 02-16-2012, 08:55 PM

I guess to first question to ask is if what you are after can be done within some VBA code without the formula.(That is one beast of a formula) At the risk of be wrong, I have not seen many instances where some code could not achieve the same result. Maybe posting a dummy book and a brief explanation of what your trying to do and then see if this could be done with some code?

**knewbry** · 02-16-2012, 10:22 PM

As a work around for now I split the formula up into several operations and used the script to paste these smaller chunks into several columns and then concatenated the results to get the output I wanted. But it means I have a bunch of junk columns for the intermediate steps.

I'm sure there's a way to do what I want in script, and I'm also sure there's a much shorter way to have done my formula to get the same result. But for now it works I suppose.

When I get a moment I will paste a dummy book to show what I'm trying to do. Thanks.