フィルをマクロで操作し、その戻り値をRangeとする関数（データ予測への対応）

昨日は、フィルをマクロで操作し、その戻り値をRangeとする関数を作成してみた。
infoment.hatenablog.com

しかしこの関数、現時点でまだ幾つか問題を抱えており未完成。そこで、この解決に取り組んでみる。

まずおさらいとして、前回の関数を再掲する。

Function DataSeriesRange(destination_range As Range, _
                Optional dsStart As Long = 1, _
                Optional dsStep As Long = 1, _
                Optional dsStop As Long = 10, _
                Optional dsRowCol As XlRowCol = xlRows, _
                Optional dsType As XlTrendlineType = xlLinear, _
                Optional dsDate As XlDataSeriesDate = xlDay, _
                Optional dsTrend As Boolean = False) As Range

        On Error GoTo er:

        With destination_range
            .Value = dsStart
            .DataSeries dsRowCol, dsType, dsDate, dsStep, dsStop, dsTrend
        End With
    
    Dim ResizeIndex As Long
        ResizeIndex = WorksheetFunction.RoundDown((dsStop - dsStart) / dsStep, 0) + 1
    
        Select Case dsRowCol
            Case xlRows
                Set DataSeriesRange = destination_range.Resize(, ResizeIndex)
            Case xlColumns
                Set DataSeriesRange = destination_range.Resize(ResizeIndex)
        End Select
        Exit Function
er:
    Set DataSeriesRange = Nothing
End Function

この中の「dsTrend」という引数は、↓こちらの「データ予測」に相当する。

このように停止値を指定するのではなく、選択範囲内で値を増やすよう指定することができる。

しかし上記関数を用いて同様のことを試みると、意図しない結果になった。

Sub test()
    DataSeriesRange destination_range:=Range("A1").Resize(11), _
                    dsRowCol:=xlColumns, _
                    dsTrend:=True
End Sub

これは考えてみれば当たり前の話で、最初のこの部分で

        With destination_range
            .Value = dsStart

指定範囲を全て初期値で埋めてしまっていた。これでは変化のしようがない。
ややこしくなる元凶と考え、開始値は必ず指定する仕様に変更した。

ということで、現時点での最新版がこちら。

Function DataSeriesRange(destination_range As Range, _
                Optional dsStep As Long = 1, _
                Optional dsStop As Long = 10, _
                Optional dsRowCol As XlRowCol = xlRows, _
                Optional dsType As XlTrendlineType = xlLinear, _
                Optional dsDate As XlDataSeriesDate = xlDay, _
                Optional dsTrend As Boolean = False) As Range

        On Error GoTo er:

        destination_range.DataSeries dsRowCol, dsType, dsDate, dsStep, dsStop, dsTrend
    
    Dim dsStart As Variant
        dsStart = destination_range.Cells(1).Value
    
    Dim ResizeIndex As Long
        ResizeIndex = WorksheetFunction.RoundDown((dsStop - dsStart) / dsStep, 0) + 1
    
        Select Case dsRowCol
            Case xlRows
                Set DataSeriesRange = destination_range.Resize(, ResizeIndex)
            Case xlColumns
                Set DataSeriesRange = destination_range.Resize(ResizeIndex)
        End Select
        Exit Function
er:
    Set DataSeriesRange = Nothing
End Function

こちらでテストしたところ、意図したように動作した。

Sub test()
    DataSeriesRange destination_range:=Range("A1").Resize(15), _
                    dsRowCol:=xlColumns, _
                    dsTrend:=True
End Sub

停止値の初期値は10で、指定範囲の15より小さいが、データ予測の場合は停止値が無視されるようだ。

でも、まだ問題が残っている。

明日に続きます。

参考まで。