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
@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: imgur.com/a/8WWPxNQ — PaperCupStraw
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

Ricardo Diaz Ricardo Diaz · Accepted Answer · 2020-02-08T01:02:22

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

VBA Object variable or With block variable not set

1 Answers