Excel VBAを使ったバブルチャート作成

「すべてExcelでできる！ 経営力・診療力を高めるDPCデータ活用術」を読んでバブルチャートを作成するのに手間がかかるのでマクロを作成した。
方法として下記のデータを作成してから、バブルチャートにしたいデータを選択してからマクロを実行するとバブルチャートが作成されるというもの。

Sub バブルチャート作成()
    Dim chartObj As ChartObject
    Dim ns As Series
    Dim r As String
    Dim s As String
    Dim sr As Range
    Dim wkAdr As String
    Dim nameAdr As String
    Dim xAdr As String
    Dim yAdr As String
    Dim bAdr As String
    Dim leng As Integer
    Dim line As Integer
    Dim cnt As Integer
    Dim i As Integer
    
    r = Selection.Address
    s = ActiveSheet.Name
    Set sr = Worksheets(s).Range(r)
    
    wkAdr = sr.Cells(1, 1).Address
    leng = InStr(2, wkAdr, "$")
    line = Int(Mid(wkAdr, leng + 1))
    nameAdr = "=" & s + "!" + Left(wkAdr, leng)
    wkAdr = sr.Cells(1, 2).Address
    leng = InStr(2, wkAdr, "$")
    xAdr = "=" & s + "!" + Left(wkAdr, leng)
    wkAdr = sr.Cells(1, 3).Address
    leng = InStr(2, wkAdr, "$")
    yAdr = "=" & s + "!" + Left(wkAdr, leng)
    wkAdr = sr.Cells(1, 4).Address
    leng = InStr(2, wkAdr, "$")
    bAdr = "=" & s + "!" + Left(wkAdr, leng)
    
    cnt = sr.Rows.Count
    
    Set chartObj = ActiveSheet.ChartObjects.Add(100, 80, 500, 300)
    chartObj.Chart.ChartType = xlBubble3DEffect
    chartObj.Chart.Legend.Delete
    
    For i = line To line + cnt - 1
        Set ns = chartObj.Chart.SeriesCollection.NewSeries
        ns.ChartType = xlBubble3DEffect
        ns.Name = nameAdr & i
        ns.XValues = xAdr & i
        ns.Values = yAdr & i
        ns.BubbleSizes = bAdr & i
        ns.ApplyDataLabels
        ns.DataLabels.ShowSeriesName = True
        ns.DataLabels.ShowValue = False
    Next
    
End Sub