Several Macros run from VBsccript, 1 throws compile error " Excpected End With"

I am not well versed in either vbscript or VBA, but I must admit I am a little bit annoyed by the macro recorder... Ok, enough about that.

I have quite a few Excel charts that are created in a fairly similar manner. The macros all run, except 1 of the, which insists the is missing an "End With" statement.

Please Login or Register  to view this content.

There is no difference between this and the code for the other macros that I can see, but of course my language of choice is C++, so this doesn't quite compute in my brain....
Anybody ?

Good afternoon PushT

... and welcome to the forum!!

Don't be too hard on the macro recorder - it's a great aid for learning, and without it you wouldn't have got this far.

Each "With" statement requries an "End With", and all but the first two "Withs" - two consecutive lines early on beginning "With ActiveChart." - have them.

You should be able to delete the "With" part of these two lines, just leaving the "ActiveChart" part.

HTH

DominicB

Originally Posted by Zer0Cool

Just to further elaborate on the recording of code. To my point regarding it recording things not needed:

Please Login or Register  to view this content.

You could almost 99.9% sure remove all of the lines like this above from your code. Essentially they recorder records EVERYTHING you do (that VBA has access to), including things like scrolling, clicking a cell (not required 99% to act upon it), etc.

I wouldnt be surprised if the code you have could be re-written to be ~80% less code while doing the same thing, while possibly being more flexible (like finding the needed range instead of static ranges).

VBA can do wonderful things, but it can also cause alot of issues if used incorrectly. As a programmer I often ask myself "Should I do this?" not "Can I do this?". If there is a feature or formula that can accomplish your goals its generally a better approach. VBA shines when: 1) the process needs to be repeated a tedious number of times or 2) you cannot accomplish the same result with built in formula/features.

It looks like your macro does mostly formatting. Alot of people use macros to do that type of a thing without realizing features built in like styles, table styles, chart templates, etc. can accomplish the same thing. It may be that you are aiming to meet my point #1 about repetition with the macro and if your macro serves you well then keep using it. However as people get more comfortable with recording code and even writing code they tend to use it more and more even when they do not need to. Just be careful not to fall into the habit of trying to use code to do everything.

That's valuable advice there, and I can absolutely relate to it. I will keep it in mind, sir.
Your comment about repetition is spot on, as there are A LOT of data that will reuse the same set of macros to continuously produce charts with the same "template". Creating the charts by hand is fun the first time I do them, but interactive chart creation is not an option for me at all.