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

**PushT** · 01-10-2018, 12:22 PM

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.

Sub XMAEnvelopes()
'
' XMAEnvelopes Macro
'

'
    Sheets("Generic").Select
    Range("D3200:G3453").Select
    ActiveWindow.SmallScroll Down:=-21
    'ActiveChart.ClearToMatchStyle
    ActiveSheet.Shapes.AddChart2(322, xlStockOHLC).Select
    ActiveChart.SetSourceData Source:=Range("Generic!$D$3200:$G$3453")
    With ActiveChart.ChartArea.Format.IncrementLeft - 358.5
    With ActiveChart.ChartArea.Format.IncrementTop - 124.5
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="XMA Envelopes 20"
    ActiveChart.ChartArea.Select
    ActiveChart.ChartArea.Select
    With ActiveSheet.Shapes("Chart 1").Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
    ActiveChart.ChartArea.Select
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 0.25
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0.349999994
        .Transparency = 0
    End With
    ActiveChart.ChartArea.Select
    ActiveChart.PlotArea.Select
    Sheets("Generic").Select
    ActiveWindow.SmallScroll Down:=-288
    ActiveWindow.ScrollRow = 3130
    ActiveWindow.ScrollRow = 3126
    ActiveWindow.ScrollRow = 3123
    ActiveWindow.ScrollRow = 3120
    ActiveWindow.ScrollRow = 3117
    ActiveWindow.ScrollRow = 3108
    ActiveWindow.ScrollRow = 3099
    ActiveWindow.ScrollRow = 3071
    ActiveWindow.ScrollRow = 3028
    ActiveWindow.ScrollRow = 2785
    ActiveWindow.ScrollRow = 2449
    ActiveWindow.ScrollRow = 1999
    ActiveWindow.ScrollRow = 1827
    ActiveWindow.ScrollRow = 1473
    ActiveWindow.ScrollRow = 1303
    ActiveWindow.ScrollRow = 968
    ActiveWindow.ScrollRow = 786
    ActiveWindow.ScrollRow = 496
    ActiveWindow.ScrollRow = 395
    ActiveWindow.ScrollRow = 161
    ActiveWindow.ScrollRow = 108
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 250
    ActiveWindow.ScrollRow = 447
    ActiveWindow.ScrollRow = 543
    ActiveWindow.ScrollRow = 740
    ActiveWindow.ScrollRow = 835
    ActiveWindow.ScrollRow = 1045
    ActiveWindow.ScrollRow = 1115
    ActiveWindow.ScrollRow = 1251
    ActiveWindow.ScrollRow = 1303
    ActiveWindow.ScrollRow = 1454
    ActiveWindow.ScrollRow = 1497
    ActiveWindow.ScrollRow = 1577
    ActiveWindow.ScrollRow = 1642
    ActiveWindow.ScrollRow = 1753
    ActiveWindow.ScrollRow = 1802
    ActiveWindow.ScrollRow = 1907
    ActiveWindow.ScrollRow = 1953
    ActiveWindow.ScrollRow = 2082
    ActiveWindow.ScrollRow = 2135
    ActiveWindow.ScrollRow = 2236
    ActiveWindow.ScrollRow = 2295
    ActiveWindow.ScrollRow = 2443
    ActiveWindow.ScrollRow = 2511
    ActiveWindow.ScrollRow = 2603
    ActiveWindow.ScrollRow = 2634
    ActiveWindow.ScrollRow = 2683
    ActiveWindow.ScrollRow = 2723
    ActiveWindow.ScrollRow = 2803
    ActiveWindow.ScrollRow = 2834
    ActiveWindow.ScrollRow = 2865
    ActiveWindow.ScrollRow = 2874
    ActiveWindow.ScrollRow = 2883
    ActiveWindow.ScrollRow = 2886
    ActiveWindow.ScrollRow = 2896
    ActiveWindow.ScrollRow = 2902
    ActiveWindow.ScrollRow = 2914
    ActiveWindow.ScrollRow = 2951
    ActiveWindow.ScrollRow = 2976
    ActiveWindow.ScrollRow = 3016
    ActiveWindow.ScrollRow = 3046
    ActiveWindow.ScrollRow = 3096
    ActiveWindow.ScrollRow = 3130
    ActiveWindow.ScrollRow = 3182
    ActiveWindow.ScrollRow = 3188
    ActiveWindow.ScrollRow = 3185
    ActiveWindow.ScrollRow = 3182
    ActiveWindow.ScrollRow = 3151
    ActiveWindow.ScrollRow = 3136
    ActiveWindow.ScrollRow = 3102
    ActiveWindow.ScrollRow = 2936
    ActiveWindow.ScrollRow = 2575
    ActiveWindow.ScrollRow = 2430
    ActiveWindow.ScrollRow = 2434
    ActiveWindow.ScrollRow = 2437
    ActiveWindow.ScrollRow = 2446
    ActiveWindow.ScrollRow = 2461
    ActiveWindow.ScrollRow = 2480
    ActiveWindow.ScrollRow = 2535
    ActiveWindow.ScrollRow = 2584
    ActiveWindow.ScrollRow = 2698
    ActiveWindow.ScrollRow = 2720
    ActiveWindow.ScrollRow = 2723
    ActiveWindow.ScrollRow = 2726
    ActiveWindow.ScrollRow = 2729
    ActiveWindow.ScrollRow = 2732
    ActiveWindow.ScrollRow = 2735
    ActiveWindow.ScrollRow = 2738
    ActiveWindow.ScrollRow = 2745
    ActiveWindow.ScrollRow = 2751
    ActiveWindow.ScrollRow = 2757
    ActiveWindow.ScrollRow = 2766
    ActiveWindow.ScrollRow = 2782
    ActiveWindow.ScrollRow = 2806
    ActiveWindow.ScrollRow = 2862
    ActiveWindow.ScrollRow = 2902
    ActiveWindow.ScrollRow = 2939
    ActiveWindow.ScrollRow = 2951
    ActiveWindow.ScrollRow = 2963
    ActiveWindow.ScrollRow = 2972
    ActiveWindow.ScrollRow = 2979
    ActiveWindow.ScrollRow = 2985
    ActiveWindow.ScrollRow = 2994
    ActiveWindow.ScrollRow = 3013
    ActiveWindow.ScrollRow = 3022
    ActiveWindow.ScrollRow = 3037
    ActiveWindow.ScrollRow = 3040
    ActiveWindow.ScrollRow = 3043
    ActiveWindow.ScrollRow = 3046
    ActiveWindow.ScrollRow = 3049
    ActiveWindow.ScrollRow = 3053
    ActiveWindow.ScrollRow = 3056
    ActiveWindow.ScrollRow = 3062
    ActiveWindow.ScrollRow = 3065
    ActiveWindow.ScrollRow = 3071
    ActiveWindow.ScrollRow = 3086
    ActiveWindow.ScrollRow = 3090
    ActiveWindow.ScrollRow = 3102
    ActiveWindow.ScrollRow = 3105
    ActiveWindow.ScrollRow = 3108
    ActiveWindow.ScrollRow = 3120
    ActiveWindow.ScrollRow = 3130
    ActiveWindow.ScrollRow = 3173
    ActiveWindow.ScrollRow = 3207
    ActiveWindow.ScrollRow = 3228
    ActiveWindow.ScrollRow = 3253
    ActiveWindow.ScrollRow = 3256
    ActiveWindow.ScrollRow = 3259
    ActiveWindow.ScrollRow = 3262
    ActiveWindow.ScrollRow = 3265
    ActiveWindow.ScrollRow = 3268
    ActiveWindow.ScrollRow = 3271
    ActiveWindow.ScrollRow = 3274
    ActiveWindow.ScrollRow = 3277
    ActiveWindow.ScrollRow = 3280
    ActiveWindow.ScrollRow = 3284
    ActiveWindow.ScrollRow = 3290
    ActiveWindow.ScrollRow = 3293
    ActiveWindow.ScrollRow = 3296
    ActiveWindow.ScrollRow = 3299
    ActiveWindow.ScrollRow = 3302
    ActiveWindow.ScrollRow = 3299
    ActiveWindow.ScrollRow = 3296
    ActiveWindow.ScrollRow = 3293
    ActiveWindow.ScrollRow = 3290
    ActiveWindow.ScrollRow = 3287
    ActiveWindow.ScrollRow = 3284
    ActiveWindow.ScrollRow = 3280
    ActiveWindow.ScrollRow = 3277
    ActiveWindow.ScrollRow = 3274
    ActiveWindow.ScrollRow = 3271
    ActiveWindow.ScrollRow = 3268
    ActiveWindow.ScrollRow = 3265
    ActiveWindow.ScrollRow = 3262
    ActiveWindow.ScrollRow = 3259
    ActiveWindow.ScrollRow = 3256
    ActiveWindow.ScrollRow = 3253
    ActiveWindow.ScrollRow = 3250
    ActiveWindow.ScrollRow = 3247
    ActiveWindow.ScrollRow = 3244
    ActiveWindow.ScrollRow = 3240
    ActiveWindow.ScrollRow = 3237
    ActiveWindow.ScrollRow = 3234
    ActiveWindow.ScrollRow = 3231
    ActiveWindow.ScrollRow = 3228
    ActiveWindow.ScrollRow = 3225
    ActiveWindow.ScrollRow = 3222
    ActiveWindow.ScrollRow = 3219
    ActiveWindow.ScrollRow = 3216
    ActiveWindow.ScrollRow = 3213
    ActiveWindow.ScrollRow = 3210
    ActiveWindow.ScrollRow = 3207
    ActiveWindow.ScrollRow = 3203
    ActiveWindow.ScrollRow = 3200
    ActiveWindow.ScrollRow = 3197
    ActiveWindow.ScrollRow = 3194
    ActiveWindow.ScrollRow = 3191
    ActiveWindow.ScrollRow = 3188
    ActiveWindow.ScrollRow = 3185
    ActiveWindow.ScrollRow = 3186
    Range("T3200:U3453").Select
    Selection.Copy
    Sheets("XMA Envelopes 20").Select
    ActiveChart.Paste
    ActiveChart.FullSeriesCollection(5).Select
    ActiveChart.FullSeriesCollection(5).AxisGroup = 2
    ActiveChart.ChartArea.Select
    ActiveChart.FullSeriesCollection(5).Select
    ActiveChart.FullSeriesCollection(5).AxisGroup = 1
    ActiveChart.ChartArea.Select
    ActiveChart.FullSeriesCollection(5).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0.349999994
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent5
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 1
    End With
    ActiveChart.FullSeriesCollection(6).Select
    ActiveChart.FullSeriesCollection(6).AxisGroup = 2
    ActiveChart.ChartArea.Select
    ActiveChart.FullSeriesCollection(6).Select
    ActiveChart.FullSeriesCollection(6).AxisGroup = 1
    ActiveChart.ChartArea.Select
    ActiveChart.FullSeriesCollection(6).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent5
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
    End With
    ActiveChart.PlotArea.Select
    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 1
    End With
    ActiveChart.ChartArea.Select
End Sub

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 ?

**dominicb** · 01-10-2018, 12:28 PM

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

**Zer0Cool** · 01-10-2018, 12:30 PM

As the error states, you have some with statements without the closing end with statement.

Correct:

    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 0.25
    End With

Incorrect:

    ActiveChart.SetSourceData Source:=Range("Generic!$D$3200:$G$3453")
    With ActiveChart.ChartArea.Format.IncrementLeft - 358.5
    With ActiveChart.ChartArea.Format.IncrementTop - 124.5
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="XMA Envelopes 20"

The recorder will do for the most simple of tasks. Its mostly useful for learning. It however generates terrible code. In has a habit of including alot of things not required (like .select and .activate) and is purely static (the exact range you did something to).

You can for the time correct your issue by closing all the open with statements (those without a matching end with).

**PushT** · 01-10-2018, 12:40 PM

Tanks so much, both of you !! I am very new to this, so I promise to use the recorder only for learning ;-)
It is grear functionality, so I do appreciate it :-)
And I greatly appreciate that level of fast response :-)

**Zer0Cool** · 01-10-2018, 01:51 PM

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

ActiveWindow.ScrollRow =

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.

**PushT** · 01-10-2018, 02:10 PM

Originally Posted by Zer0Cool

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

ActiveWindow.ScrollRow =

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.

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Excel 2010 -- "Visual Basic" "Macros" and "Record Macro" all disabled.

[SOLVED] Choice between "Compile Error" and "Error 91 - Object Variable Not Defined"

[SOLVED] Insert Row throws error "Insert Method of Range class failed"

[SOLVED] Error Msg: "Compile error: Method or data member not found" in VBA code

[SOLVED] emptyRow code giving Compile Error "Variable not defined"

VBA - Formula generating "Compile error/ Syntax Error"

[SOLVED] How do I get rid of "Compile error in hidden module" error message

Bookmarks

Bookmarks

Posting Permissions