CODEWITHSUNDEEP
exceltype-mismatchvba
Dedwards
Dedwards

Reputation: 73

Making Charts with Named Ranges in VBA

I am trying to write some code that will work through the named ranges I have established already in my Excel Workbook. Each sheet in the workbook has a different layout, which made writing code for the names the lion's share of the work. I have already done that and now would like to write code that will cycle through the names in each sheet and make charts using the named ranges I have already established. I have run into a bunch of errors in the following code and would appreciate some assistance fixing them and making the code more efficient!

ChartName = ActiveSheet.Range(n).Offset(0, -6) & " " & ActiveSheet.Range(n).Offset(0, -5)

I get an error in the above line for a type mismatch that I just cannot figure out how to deal with. Also, ActiveSheet.ChartObjects.Add draws an invalid property assignment!

Sub WEO_DevCharts()

Sheets("WEO").Activate

Dim objChart As ChartObject
Dim n As Name
Dim ChartName As String

For Each n In ActiveSheet.Names

    If n.Name <> "DateRange" Then

    ChartName = ActiveSheet.Range(n).Offset(0, -6) & " " & ActiveSheet.Range(n).Offset(0, -5)

        Set objChart = ActiveSheet.ChartObjects.Add

        With objChart.Chart
            .chartType = xlXYScatterLines
            .SeriesCollection.Values = n.Value
            .SeriesCollection.XValues = ActiveSheet.Range("DateRange").Value
            .SeriesCollection.Name = ChartName
            .legend.Delete
        End With
    End If
    Next n
    End Sub

Upvotes: 2

Views: 8586

Answers (2)

brettdj
brettdj

Reputation: 55692

I have made a number of changes below

(Updated to handle two separate ranges on the sheet, a range called DateRange and one called Test. Both ranges are local to the sheet to satisfy the ActiveSheet test, else they would belong to the ActiveWorkbook {but DateRange can be global or local and wil still graph fine either way})

  1. Rather than ActiveSheet.Range(n).Offset(0, -6) you need a single cell, probably the first in the n range, ie Range(n).Cells(1).Offset(0, -6) (plus this means your title lies 6 columns to the left of your range name - ensure there is space)
  2. Try adding your chart like ActiveSheet.ChartObjects.Add(Left:=500, Width:=300, Top:=50, Height:=400)
  3. Once you have set the xy type you can set your sourcerange directly to your range name n then add the DateRangerange as the x series
  4. Add your series name to the first series, ie .SeriesCollection(1).Name = ChartName not .SeriesCollection.Name = ChartName
  5. I have used InStr(n.Name, "DateRange") = 0 rather than your n.Name <> "DateRange" to avoid processing any local name versions of DateRange ie WEO!DateRange

I suggest you look at Jon Peltier's excellent site for further detailed code examples

Sample screenshot of my test code for a local sheet name

  • Test in H10:H13
  • DateRange in D14:D17

enter image description here 

    Sub WEO_DevCharts()
    Sheets("WEO").Activate
    Dim objChart As ChartObject
    Dim n As Name
    Dim ChartName As String
    For Each n In ActiveSheet.Names
        If InStr(n.Name, "DateRange") = 0 Then
            ChartName = Range(n).Cells(1).Offset(0, -6) & " " & Range(n).Cells(1).Offset(0, -5)
            Set objChart = ActiveSheet.ChartObjects.Add(Left:=500, Width:=300, Top:=50, Height:=400)
            With objChart.Chart
                .ChartType = xlXYScatterLines
                .SetSourceData Range(n)
                .SeriesCollection(1).XValues = Range("DateRange")
                .SeriesCollection(1).Name = ChartName
                .Legend.Delete
            End With
        End If
    Next n
End Sub

Upvotes: 2

chris neilsen
chris neilsen

Reputation: 53126

Instead of ActiveSheet.Range(n) or n.Value, use n.RefersToRange to get the range the name refers to

Upvotes: 0

Related Questions