VBA Object variable or With block variable not set

Question

The point of this code is to create a chart in a new worksheet, which it does. After this, when I click the button to generate the chart again in a new worksheet named the same, it's supposed to delete that sheet and create a new generated chart.

It creates the chart but when I go back to click the button, it generates the chart in the sheet where the button is and throws the error 91: Object variable or With block variable not set.

Debugging points me to the following line:

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Demand Line Chart"

Below is the code:

Sub HistoricalDemand()
' Creates a line chart for the demand column

For Each ws In Worksheets
         If ws.Name = "Demand Line Chart" Then
                Application.DisplayAlerts = False
                   Sheets("Demand Line Chart").Delete
                Application.DisplayAlerts = True
                Exit For
         End If
        Next

Columns("A:A").Select
Selection.NumberFormat = "[$-en-US]mmm-yy;@"

Range("A:A,E:E").Select
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
' Try With command here
ActiveChart.SetSourceData Source:=Range("A:A,E:E")
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Demand Line Chart"
' Places line chart in a new worksheet called Demand Line Chart
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.Orientation = 70
Selection.MajorTickMark = xlNone
With ActiveChart
    .Axes(xlCategory).Select
    .Axes(xlCategory).MajorUnit = 2
    .ChartTitle.Select
    .ChartTitle.Text = "Historical Demand"
    .SetElement (msoElementLegendRight)
Selection.Format.TextFrame2.TextRange.Characters.Text = "Historical Demand"
With Selection.Format.TextFrame2.TextRange.Characters(1, 17).ParagraphFormat
    .TextDirection = msoTextDirectionLeftToRight
    .Alignment = msoAlignCenter
End With
ActiveChart.ChartArea.Select
End With
End Sub

In what sheet (name) is the chart supposed to be created? a new sheet? and what's the name of the sheet that holds the source data for the chart? — Ricardo Diaz, Feb 08 '20 at 00:32
@RicardoDiaz Sheet name that the chart is supposed to be created in is a new sheet called "Demand Line Chart". The name of the sheet that holds the source data for the chart is called "DATA". Here is a picture of the spreadsheet: https://imgur.com/a/8WWPxNQ — PaperCupStraw, Feb 08 '20 at 00:38
Saw that you have previous questions with good answers and didn't mark them. Remember to check the mark at the left of each answer if they solve your question so others may find them. — Ricardo Diaz, Feb 08 '20 at 01:11

score 0 · Answer 1 · answered Feb 08 '20 at 01:02

Some suggestions when you code in VBA:

Use option explicit so you don't have unexpected behavior with undefined variables
Always indent your code (see www.rubberduckvba.com a free tool that help you with data)
Try to separate your logic defining variables and the reusing them
Avoid using select unless it's strictly necessary

Review and customize the code so it fits your needs.

You can see what's happening in the code by pressing F8 and executing it line by line.

Code:

Public Sub HistoricalDemand()
    ' Creates a line chart for the demand column
    Dim targetSheet As Worksheet
    Dim sourceDataSheet As Worksheet
    Dim sourceDataRange As Range
    Dim targetChart As Shape

    Dim targetSheetName As String
    Dim targetChartTitle As String
    Dim dataLastRow  As Long

    ' Define parameters
    targetSheetName = "Demand Line Chart"
    targetChartTitle = "Historical Demand"

    ' Set a reference to the source data sheet
    Set sourceDataSheet = ThisWorkbook.Worksheets("DATA")

    ' Find last row in data sheet
    dataLastRow = sourceDataSheet.Cells(sourceDataSheet.Rows.Count, 1).End(xlUp).Row

    ' Set reference to the source range
    Set sourceDataRange = sourceDataSheet.Range("A1:A" & dataLastRow & ",E1:E" & dataLastRow)

    ' Delete the sheet if exists
    For Each targetSheet In Worksheets
        If targetSheet.Name = targetSheetName Then
            Application.DisplayAlerts = False
            ' This will delete the matching sheet
            targetSheet.Delete
            Application.DisplayAlerts = True
            Exit For
        End If
    Next

    ' Apply format to the first column (A)
    sourceDataSheet.Range("A1:A" & dataLastRow).NumberFormat = "[$-en-US]mmm-yy;@"

    ' Add the chart
    Set targetChart = sourceDataSheet.Shapes.AddChart2(332, xlLineMarkers)

    ' Apply the target chart settings
    With targetChart.Chart
        .SetSourceData Source:=sourceDataRange
        .Location Where:=xlLocationAsNewSheet, Name:=targetSheetName
        ' Places line chart in a new worksheet called Demand Line Chart
        .Axes(xlCategory).TickLabels.Orientation = 70
        .Axes(xlCategory).MajorTickMark = xlNone

        .Axes(xlCategory).Select
        .Axes(xlCategory).MajorUnit = 2

        .ChartTitle.Text = targetChartTitle
        .SetElement (msoElementLegendRight)
        .ChartTitle.Format.TextFrame2.TextRange.Characters.Text = targetChartTitle
        With .ChartTitle.Format.TextFrame2.TextRange.Characters(1, 17).ParagraphFormat
            .TextDirection = msoTextDirectionLeftToRight
            .Alignment = msoAlignCenter
        End With
    End With

End Sub

Let me know if it works

Thank you SO much for this, it's clear you put thought into answering me and I appreciate that. I still cannot seem to fix my code. I haven't see much of what you typed before so it's kind of out of my league in terms of readability. BUT ruberduckvba is excellent! — PaperCupStraw, Feb 08 '20 at 02:39
Remember you can use `F8`to see what the code is doing. At what line is the code not working and we can debug it. The idea is to make it work. — Ricardo Diaz, Feb 08 '20 at 02:49
After debugging it a couple of times, the trouble seems to happen here: ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select It is inserting a chart onto the DATA worksheet before inserting it into a new one. When removing it I am presented with the error: object variable With block variable not set. — PaperCupStraw, Feb 08 '20 at 02:55
Yeah, use my code and debug it. What I did was took your code and refactored it to apply the suggestions I mentioned, but I preserved your logic. When you read the comments, the actions are explained. Just step through it. Using your macro recorded code leads to the problem you're having which is that the activesheet doesn't support adding and selecting the chart — Ricardo Diaz, Feb 08 '20 at 02:59
I've went through it a few times and it gives me the same problem on the same line of: Location Where:=xlLocationAsNewSheet, Name:=targetSheetName It creates the chart in the DATA worksheet. — PaperCupStraw, Feb 08 '20 at 04:14
The chart is created in the `DATA` sheet, but then moved to the new sheet with that line. Do you happen to have the workbook protected? — Ricardo Diaz, Feb 08 '20 at 04:24

VBA Object variable or With block variable not set

1 Answers1