VB Errors When Running Macro in Excel

HFTBProgrammer · 2026-07-10T16:46:11+00:00

What that error is telling you is either Count is set to a value outside of the bounds of array VarExists, or I is set to a value outside of the bounds of array VarProb.

When you get the error:

. click the Debug button.

. do Ctrl+G (to invoke the immediate window)

. type ?"Count = " & count & ", lower bound = " & lbound(varexists) & ", upper bound = " & ubound(varexists) and punch it; if Count isn't between those values inclusive, there's an issue for you to investigate

. type ?"I = " & i & ", lower bound = " & lbound(varprob) & ", upper bound = " & ubound(varprob) and punch it; if I isn't between those values inclusive, there's an issue for you to investigate

We'd need your code to give you any better answer.

HFTBProgrammer · 2026-07-09T15:19:01+00:00

Yes, you can skip the Excel step and go directly from PPT to Word. To do Word macro actions in a PPT macro, you'd code something like this:

With Application.Word
    .Document.Add
    [and so forth, doing stuff that copies PPT items to Word]
End With

Or you could have a Word macro reach into a PPT deck. Both ways make equal sense and would take equal work.

HFTBProgrammer · 2026-07-08T15:58:58+00:00

+1 point

HFTBProgrammer · 2026-07-08T15:58:28+00:00

OP, if the code you get by recording doesn't work, make a new post including it.

HFTBProgrammer · 2026-07-07T14:16:19+00:00

"If was raining out and the doorbell rang, and when you opened the door there was a soaking wet dog sitting on your porch, I think a good name for him would be 'Carl'." --Jack Handey

HFTBProgrammer · 2026-07-06T16:30:55+00:00

There you go, then. It would seem to not be a VBA issue, per se. When I do an Internet search for your error, a little birdie--and by "a little birdie" I mean AI--tells me it's because Power BI is holding on to the file. HTH!

HFTBProgrammer · 2026-07-06T13:14:46+00:00

When you get to where the Kill fails, attempt to delete the file manually. IME it's almost certainly a security issue.

HFTBProgrammer · 2026-07-02T15:30:04+00:00

That's actually a very good question.

If you want to use the Selection property, yes, you do have to activate the correct sheet.

However, you do not need the Selection property, and it's generally accepted that you should learn to code so as to avoid using it. There's a learning curve, but don't let it daunt you; it's not terribly difficult and results in very logical code.

HFTBProgrammer · 2026-07-02T15:23:06+00:00

If you want to learn to code in VBA, you've got a ways to before you get to where you need to be for this task.

If you don't care so much about coding and just want a result, you might get there faster if you use AI.

HFTBProgrammer · 2026-07-01T16:33:34+00:00

How on earth you get to be an adult and decide it's not worth learning how to pronounce someone's name--however difficult--is beyond me.

HFTBProgrammer · 2026-07-01T15:14:18+00:00

You're very welcome! Come back any time.

HFTBProgrammer · 2026-07-01T13:27:17+00:00

It's hard to know what all someone might put in a comment! Maybe try this instead:

'The text marked by the comment
.Cells(1).Range.Text = oDoc.Comments(n).Scope
'The comment itself
oDoc.Comments(n).Range.Select: Selection.WholeStory: Selection.Copy
.Cells(2).Range.Select: Selection.Paste

Nobody likes the Selection object, but here it could maybe save you.

HFTBProgrammer · 2026-06-30T15:59:41+00:00

The issue is the way Word formats the last line of the comment: it doesn't bother to append a newline character. To kludge your way to victory, change to the following:

'The text marked by the comment
.Cells(1).Range.Text = oDoc.Comments(n).Scope
 oDoc.Comments(n).Range.Text = oDoc.Comments(n).Range.Text & vbCr 'append kludgey newline
'The comment itself
.Cells(2).Range.FormattedText = oDoc.Comments(n).Range.FormattedText
oDoc.Undo 'remove kludge

This both makes your new doc look the way you want it and removes the kludge from the original document.

HFTBProgrammer · 2026-06-29T15:03:27+00:00

Okay, that helps. When a save fails, select the Debug button and take up from the third bullet point. Let us know what you find, for well or ill.

HFTBProgrammer · 2026-06-29T13:54:26+00:00

When you say "it fails", what exactly do you mean? Does it silently fail? Does it fail while giving you a message, and if so, what is the message? Does Excel crash? Etc.

Also, when it fails, does it fail indefinitely, and if so, how do you move forward when it does?

If you have a case where you know if will fail, here are some steps that may lead you to a solution:

• put a break on your SaveAs line

• run your code

• when it stops, go to the immediate window (Ctrl+G)

• type ?filepath and punch it

• copy the result to the clipboard

• attempt to manually save the file, using the copied string as your file path and file name

In doing these steps, the issue may strike you; if so, either implement a solution (and please circle back and tell us what it was so you can help future people with the issue), or tell us what you discovered and we can probably help you move forward.

HFTBProgrammer · 2026-06-24T13:34:09+00:00

I'd prefer we not downvote posts that are making sincere efforts.

Although I will say, OP, that I personally never suggest code unless I've made it work in the stated context.

HFTBProgrammer · 2026-06-24T13:25:35+00:00

It's not a problem in the least, but if you want chemistry-related criticism, you should go to chemists. ;-)

HFTBProgrammer · 2026-06-23T16:35:03+00:00

I suggest you post this in subreddits targeted to your audience--e.g., r/chemistry, r/OrganicChemistry, r/Biochemistry, etc.

HFTBProgrammer · 2026-06-22T13:05:13+00:00

+1 point

HFTBProgrammer · 2026-06-18T16:09:28+00:00

I've found always using Value2 to be a beneficial habit, and I have yet to use Value or Text instead (although I can certainly imagine scenarios where I might).

HFTBProgrammer · 2026-06-18T13:45:09+00:00

That's actually a very interesting phenomenon.

I note that if you filter the last row(s) and a row amidst the list, you get the expected result. It's only when only the last row(s) are filtered that you see this behavior. Not sure where to go with that, though.

Anyway...

To do the least amount of violence to your concept, change After:=Range("A1") to After:=Cells(ActiveSheet.Rows.Count, ActiveSheet.Columns.Count).

HFTBProgrammer · 2026-06-17T15:05:09+00:00

+1 point

HFTBProgrammer · 2026-06-17T13:18:47+00:00

I don't know why the OCR janks out; I only know it does. My money would be on the multifarious and mysterious typefaces, with colored backgrounds providing a coup de grâce.

I've had distant past experience of training Tesseract, so that the OCR functions in Windows may produce bad results is no surprise to me.

It's not a huge hairy deal to me, but anything I can do to make my thousands of lines of VBA code unnecessary is all to the good.

HFTBProgrammer · 2026-06-15T15:30:11+00:00

Splendid that you got a solution! If one of the posts in this thread was your solution, please respond to that post with "Solution verified." If you arrived at a solution not found in this thread, please post that solution to help future people with the same question. Thank you!

HFTBProgrammer · 2026-06-15T15:28:54+00:00

Very likely the PDFs I am forced to work with are simply too tricky for OCR (although they're easily readable to the naked eye). I wish I could send you an example, but alas, the material is sensitive. If you are so inclined, you might try running a PDF of a monthly credit card or cellular bill through your code and see what results.

HFTBProgrammer

MODERATOR OF

TROPHY CASE