what's maximum number of line continuations in a vba code

[kylefoley76]

anyone know this. i've got 1200 line continuations and excel and word are saying that that's too much. excel 2007

 

[Richard Schollar]

Are you trying to get that into a single cell in Excel? Maybe you have more than the maximum number of characters in a cell (32,767) and are thus getting data truncated?

 

[kylefoley76]

no, i'm writing it in vba editor. i have an array with about 1200 items. i saw on one blog that even 25 lines is too much. that will be too time consuming. let me post the vba code and you can suggest what i do.

 

[Norie]

Why are you hard-coding an array of so many items?

What are the items?

Are they totally unique?

Could they possibly be generated via code?

Or perhaps you could try storing them somewhere? eg worksheet, text file, outer space.

 

[kylefoley76]

ok, here is the basic macro i'm using. i've identified 1200 unique word combinations that will indicate a german noun's gender. in this array there are only 3 items, but in the ideal array there will be 1200

here is what it looks like

https://docs.google.com/document/pub?id=1t6tYt_LfprKyYfN9SG0AoTn6SM2wyjEdLREn6Ki2sxc

but it's not working. i think i'm just going to have to divide it up into 10 sections but if you know of a different way then please let me know.

findArray = array(" mit der "," mit den "," mit dem ")
replArray = array(" mitder"," mitden"," mitdem")

for i =0 to ubound(findArray)
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = findArray(i)
.Replacement.Text = replArray(i)
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
next i

 

[Norie]

Kyle

Do my spectacles decieve me? Or is that Word code?

 

[kylefoley76]

it's works in both word and excel. i've been using both word and excel for this project.

 

[Norie]

That code won't work in Excel unless you are automating Word.

There is no Find.Execute method in Excel VBA and vbReplaceAll is a Word VBA constant.

There is a line continuation limit - 25 apparently.

Help suggests you make the lines longer, eg have more than 1 item on each line.

I would recommend you find another way to load this array, or perhaps consider another approach that doesn't involve an array.

 

[kylefoley76]

for the moment, i'm dividing it into 10 sections. but i still find that very short-sighted on microsoft's part that they couldn't forsee someone needing more than 25 line continuations. if they can allow for 1 million records in excel then surely they can allow for more line continuations in vba

 

[kylefoley76]

ok, it's working, it's just more time consuming than i would like, that's all