Run-time error '5854' - String parameter too long

[miclkh]

Hello,

When I use the macro, I keep on getting the above error message: Run-time error '5854' - String parameter too long. Here is the error highlighted when I clicked the debug button:

I am not sure what has gone wrong. I use APA style. Also I was using Word365 on a 64bit Windows10.

Please advise on what to try next?

Thanks! Michael

[mabentwickeltsich]

Dear @miclkh,

could you have a look on what is stored in strTempMatch? Just use a MsgBox for that. Also a good idea to check the contents of objMatchBiblio.Value inside the same for loop.

Perhaps the regular expression getting the matches needs to be updated.

Cheers Luis

[miclkh]

Hi @mabentwickeltsich ,

I put my cursor on the strTempMatch and it shows a specific reference in the list:

As for the objMatchBiblio, it shows = ' like below:

I hope these make sense to you. The full reference for this error is:

Nassan, M., Croarkin, P. E., Luby, J. L., Veldic, M., Joshi, P. T., McElroy, S. L., Post, R. M., Walkup, J. T., Cercy, K., Geske, J. R., Wagner, K. D., Cuellar-Barboza, A. B., Casuto, L., Lavebratt, C., Schalling, M., Jensen, P. S., Biernacka, J. M., & Frye, M. A. (2015). Association of brain-derived neurotrophic factor (BDNF) Val66Met polymorphism with early-onset bipolar disorder. Bipolar Disorders, 17(6), 645–652. https://doi.org/10.1111/bdi.12323

Hope you see something in it.

Cheers~ Michael

[miclkh]

In addition, I just realised that I used APA 7th edition in the Mendeley. Not sure if this is compatible. There isn't any APA style in the mendeley that I install.

[mabentwickeltsich]

Dear @miclkh,

thanks for the info. I have a few comments, please, have a try:

1. I tried to find if .Text has some sort of limit in the size of the text it receives. No luck on that though. Could you modify the authors list and leave at most 10 of them. Perhaps the string is too long for the search. Just for testing.

2. Unfortunately, when you put the cursor on the line, the full content of the variable is not shown. Please, try with: MsgBox "objMatchBiblio.Value contents are " & Chr(34) & objMatchBiblio.Value & Chr(34) & " during error." MsgBox "strTempMatch contents are " & Chr(34) & strTempMatch & Chr(34) & " during error." just before the search, in here. It may be annoying if you have hundreds of references. It would be easier if you create a new document with only this particular reference and try the macros.

3. This is the value that I need to see too:

4. And yes, that is the APA style that you should use.

Cheers Luis

[miclkh]

Hi @mabentwickeltsich

1. I dont see anything pop up from the objMatchBiblio.Value, when I hovered over it.

2. After adding the lines into the place you suggested without shortening the references, I still get the same error.

3. Unfortunately, I have to stick to the full list of authors. However, for the sake of trying, I did shorten the author list to 6 and it works when the author list is shorter.

Is there anyway that the script could deal with full author list biblio?

Thanks for helping! Michael

[mabentwickeltsich]

Dear @miclkh,

well, at least we know that the problem comes from the long list of authors in that particular reference.

Comments for your points:

1. To see the value of objMatchBiblio.Value you need the MsgBoxthat I mentioned.
2. You added the lines of code with single quotations, that makes them comments and they will not be executed. Remove the single quotations from the beginning and end of each line. However, these lines are not necessary anymore as we know that the problem is due to the length of the authors list in that reference.
3. I know that the reference shall contain all authors in the final version of your document. I just wanted to verify if the long list of authors was the problem as the error '5854' has the description 'String parameter too long.'

I will need more time to test the macros and come with a fix. Unfortunately, I am very busy at the moment. I hope it is not urgent for you. For the moment, the workaround is to reduce the number of authors until the maximum allowed. My guess is that 256 characters is the limit.

Cheers Luis

[miclkh]

Hi @mabentwickeltsich

1. I don't know how to use the MsgBox - sorry about this

2. When I removed the quotes, it says syntax error. Sorry I am a novice in this and hope you would bear with me.

3. Many thanks for putting your effort into this.

Again, many thanks for looking into this. I can wait. So no worries. Cheers~ Michael

[mabentwickeltsich]

Dear @miclkh,

do not worry for those new lines, you can remove them. We already know where the problem comes from.

Cheers Luis

[mabentwickeltsich]

There is indeed a limitation in Selection.Find.Text of 255 (or 256?) characters. I need to find a work-around to fix the problem. Perhaps something like: Find & Replace (w\ Long Strings)

[miclkh]

Looking forward to the perfect macro! I really appreciate your effort and time spent in the matter!

[mabentwickeltsich]

Dear @miclkh,

Commit 2748452 has a fix that prevents the Run-time error '5854' - String parameter too long.

However, I found another problem while testing the code. The citation style APA 6th edition has a limit of 7 authors, in a reference, before it starts replacing them with ellipsis (…); the limit is 20 when using APA 7th edition. In such cases, the citation will not be linked to the reference entry and an orphan citation message will appear. I cannot handle this situation at this moment, and I am not sure if I will ever do it; it involves too much time and effort due to the way I search and link citations with references. My only advice will be “reduce the number of authors for that reference”.

Cheers Luis

[miclkh]

Dear @mabentwickeltsich

Thank you so much for doing this. The macro is great enough. I will modify the APA 7th to reduce the author list to something similar to APA 6th. Many thanks for that and I wish you a great weekend and week ahead!

Cheers~ Michael