SPREADが提供する「ExcelのVBA互換のAPIセット」のメリット

SPREAD for Windows Forms(スプレッド)」でシートやセルを操作する場合、バージョン「1.0J」リリース当初から提供している従来のAPIだけではなく、バージョン「12.0J」で追加された「ExcelのVBAと互換性のあるAPIセット」が使用できます。本記事では、このVBA互換のAPIセットを使用した場合のメリットについてご紹介します。

ExcelのVBAとSPREADのVB.NET

Microsoft OfficeにはExcelなどの機能をプログラミングで拡張できるVBA(Visual Basic for Applications)という機能があります。

VBAとVB.NETの間には言語仕様の違いがあり、VBAに慣れたプログラマがVB.NETに最初に接するときには戸惑うこともあると思います。

さらに、Excelで実現していた動作をSPREADの従来のAPIを使用して実現しようとすると、それぞれの設計・構造の違いから生じるコーディング手法の非互換性に悩まされることになります。

一方、SPREADのVBA互換のAPIを活用すれば、VB.NETでSPREADを扱うときにも、VBAでシートやセルを操作する場合や、VSTO(Visual Studio Tools for Office)でソリューションを開発する場合に近い感覚でコーディングできます。

SPREADのVBA互換APIを使ってみよう!

SPREADのVBA互換のAPIセットは、SPREADのGrapeCity.Spreadsheet名前空間にあるクラスやインタフェースなどで構成されています。

ここからは、よくあるシートやセルの操作を、次の3つのコードで記述・比較することで、SPREADのVBA互換APIとVBAとの互換性について説明します。

  • VBAのコード(Excel)
  • VBA互換APIのコード(SPREAD)
  • 従来のコード(SPREAD)

※ SPREADのVBA互換APIを使用するには互換機能(LegacyBehaviors)の内、Styleメンバを無効にして新しいスタイルを採用する必要があります。旧バージョンから移行したプロジェクトでは使用できない場合があるのでご注意ください。
※ コードでのインデックス番号は、VBAでは「1ベース」ですが、SPREADのVBA互換APIではVB.NETで採用されている「0ベース」になっているのでご注意ください。

検証に使用したプロジェクトは以下よりダウンロード可能です。SPREAD for Windows Forms 12.0J SP8を使用しています。同梱の「VBA.xlsx」にはVBAのコードを組み込んでいます。

値とスタイルの設定

セル範囲に値を設定するとき、それぞれのコードがどのようになるかを以下に示します。

VBA互換のAPIでは、VBAと同じRangeプロパティを使うことができますが、SPREADの従来のコードではRangeの代わりにCellsプロパティを使います。

なお、VBA互換APIでは、セル範囲をインデックス番号で指定する方法がVBAとは少し異なっています。VBAではRangeプロパティの引数に「Cells(3,3),Cells(4,4)」を設定しますが、VBA互換APIではReference構造体を使う必要があります。

Private Sub SetValue()
    With Worksheets("Sheet1")
        ' 文字列でセル範囲を指定する方法
        .Range("A1:B2").Value = "ABC"
        
        ' インデックス番号でセル範囲を指定する方法
        .Range(Cells(3, 3), Cells(4, 4)).Value = "XYZ"
    End With
End Sub
Private Sub SetValueA()
    With FpSpread1.Sheets(0).AsWorksheet()
        ' 文字列でセル範囲を指定する方法
        .Range("A1:B2").Value = "ABC"

        ' インデックス番号でセル範囲を指定する方法
        .Range({New GrapeCity.Spreadsheet.Reference(2, 2, 3, 3)}).Value = "XYZ"
    End With
End Sub
Private Sub SetValueB()
    With FpSpread1.Sheets(0)
        ' 文字列でセル範囲を指定する方法
        .Cells("A1:B2").Value = "ABC"

        ' インデックス番号でセル範囲を指定する方法
        .Cells(2, 2, 3, 3).Value = "XYZ"
    End With
End Sub

以下は、SPREADでVBA互換APIのSetValueAメソッドを実行したときの画像です。

SPREADのVBA互換APIで値を設定

次に、セル範囲の背景色と文字色を設定する例を紹介します。

VBAとVBA互換APIのコードでは、セルやセル範囲の背景色は、Interior.ColorプロパティやInterior.ColorIndexプロパティで設定しますが、従来のコードではVB.NETの標準的なプロパティであるBackColorプロパティを使います。

また、文字色を設定するプロパティもFont.ColorとForeColorのように異なっています。なお、VBAにはFont.ColorIndexプロパティがありますが、VBA互換APIのコードではこの表現は使えません。Font.Colorプロパティだけが有効となっていますのでご留意ください。

Private Sub SetStyle()
    With Worksheets("Sheet1")
        ' 文字列でセル範囲を指定する方法
        .Range("A1:B2").Interior.Color = RGB(230, 230, 250)
        .Range("A1:B2").Font.Color = RGB(0, 0, 255)
        
        ' インデックス番号でセル範囲を指定する方法
        .Range(Cells(3, 3), Cells(4, 4)).Interior.ColorIndex = 3
        .Range(Cells(3, 3), Cells(4, 4)).Font.ColorIndex = 2
    End With
End Sub
Private Sub SetStyleA()
    With FpSpread1.Sheets(0).AsWorksheet()
        ' 文字列でセル範囲を指定する方法
        .Range("A1:B2").Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(Color.Lavender.ToArgb())
        .Range("A1:B2").Font.Color = GrapeCity.Spreadsheet.Color.FromArgb(Color.Blue.ToArgb())

        ' インデックス番号でセル範囲を指定する方法
        .Range({New GrapeCity.Spreadsheet.Reference(2, 2, 3, 3)}).Interior.ColorIndex = (3 - 1)
        .Range({New GrapeCity.Spreadsheet.Reference(2, 2, 3, 3)}).Font.Color = GrapeCity.Spreadsheet.Color.FromIndexedColor(2 - 1)
    End With
End Sub
Private Sub SetStyleB()
    With FpSpread1.Sheets(0)
        ' 文字列でセル範囲を指定する方法
        .Cells("A1:B2").BackColor = Color.Lavender
        .Cells("A1:B2").ForeColor = Color.Blue

        ' インデックス番号でセル範囲を指定する方法
        .Cells(2, 2, 3, 3).BackColor = Color.Red
        .Cells(2, 2, 3, 3).ForeColor = Color.White
    End With
End Sub

SPREADでVBA互換APIのSetValueAメソッドを実行してからSetStyleAメソッドを実行したときの画像を以下に示します。

SPREADのVBA互換APIでスタイルを設定

セルのコピーとクリア

ここでは、セル範囲の値とスタイルをコピーおよびクリアする例を紹介します。

VBAとVBA互換APIでは、同じようなCopyメソッドを使ってセルやセル範囲の値とスタイルをまとめてコピーすることができます。しかし、従来のコードでは、インデックス番号を使う場合にはCopyRangeメソッドを使って値とスタイルの両方をコピーできますが、文字列を使う場合には、値と個々のスタイル(背景色や文字色)ごとにコピーする必要があり、まとめてコピーする方法が用意されていません。

Private Sub CopyCell()
    With Worksheets("Sheet1")
        ' 文字列でセル範囲を指定する方法
        .Range("A1:B2").Copy Destination:=.Range("A3")
        
        ' インデックス番号でセル範囲を指定する方法
        .Range(Cells(3, 3), Cells(4, 4)).Copy Destination:=.Cells(5, 3)
    End With
End Sub
Private Sub CopyCellA()
    With FpSpread1.Sheets(0).AsWorksheet()
        ' 文字列でセル範囲を指定する方法
        .Range("A1:B2").Copy(destination:= .Range("A3"))

        ' インデックス番号でセル範囲を指定する方法
        .Range({New GrapeCity.Spreadsheet.Reference(2, 2, 3, 3)}).Copy(destination:= .Range({New GrapeCity.Spreadsheet.Reference(4, 2, 4, 2)}))
    End With
End Sub
Private Sub CopyCellB()
    With FpSpread1.Sheets(0)
        ' 文字列でセル範囲を指定する方法
        .Cells("A3:B4").Value = .Cells("A1:B2").Value
        .Cells("A3:B4").BackColor = .Cells("A1:B2").BackColor
        .Cells("A3:B4").ForeColor = .Cells("A1:B2").ForeColor

        ' インデックス番号でセル範囲を指定する方法
        .CopyRange(2, 2, 4, 2, 2, 2, False)
    End With
End Sub

SPREADでVBA互換APIのSetValueAメソッド、SetStyleAメソッド、そしてCopyCellAの順に実行したときの画像を以下に示します。

SPREADのVBA互換APIで値をコピー

次に、各コードで値とスタイルクリアする例を紹介します。

セルやセル範囲の値とスタイルをクリアするには、VBAとVBA互換APIでは同じClearメソッドを使いますが、従来のコードではClearRangeメソッドを使います。また、ClearRangeメソッドではセル範囲をインデックス番号で指定する必要があり、文字列で指定することはできません。セル範囲を文字列で指定する方法で同等の操作を行う場合には、値や個々のスタイル(背景色や文字色)ごとにReset~メソッドを使用する必要があります。

Private Sub ClearCell()
    With Worksheets("Sheet1")
        ' 文字列でセル範囲を指定する方法
        .Range("A1:B4").Clear
        
        ' インデックス番号でセル範囲を指定する方法
        .Range(Cells(3, 3), Cells(6, 4)).Clear
    End With
End Sub
Private Sub ClearCellA()
    With FpSpread1.Sheets("Sheet1").AsWorksheet()
        ' 文字列でセル範囲を指定する方法
        .Range("A1:B4").Clear()

        ' インデックス番号でセル範囲を指定する方法
        .Range({New GrapeCity.Spreadsheet.Reference(2, 2, 5, 3)}).Clear()
    End With
End Sub
Private Sub ClearCellB()
    With FpSpread1.Sheets("Sheet1")
        ' 文字列でセル範囲を指定する方法
        .Cells("A1:B4").ResetValue()
        .Cells("A1:B4").ResetBackColor()
        .Cells("A1:B4").ResetForeColor()

        ' インデックス番号でセル範囲を指定する方法
        .ClearRange(2, 2, 4, 2, False)
    End With
End Sub

VBA互換APIのClearCellAを実行したときの画像を以下に示します。指定したセル範囲の値とスタイルがすべてクリアされています。

SPREADのVBA互換APIで値のクリア

セルの選択

セルやセル範囲を選択するときには、VBAとVBA互換APIではSelectメソッドを使います。一方、従来のコードではAddSelectionメソッドを使います。また、AddSelectionメソッドではセル範囲をインデックス番号で指定する必要があり、文字列でセル範囲を指定することはできません。

Private Sub SelectCell()
    With Worksheets("Sheet1")
        ' 文字列でセル範囲を指定する方法
        .Range("C3:D4").Select
        
        ' インデックス番号でセル範囲を指定する方法
        '.Range(Cells(3, 3), Cells(4, 4)).Select
    End With
End Sub
Private Sub SelectCellA()
    With FpSpread1.Sheets("Sheet1").AsWorksheet()
        '' 文字列でセル範囲を指定する方法
        '.Range("C3:D4").Select()

        ' インデックス番号でセル範囲を指定する方法
        .Range({New GrapeCity.Spreadsheet.Reference(2, 2, 3, 3)}).Select()
    End With
End Sub
Private Sub SelectCellB()
    With FpSpread1.Sheets("Sheet1")
        ' 文字列でセル範囲を指定する方法
        ' 該当する機能はありません

        ' インデックス番号でセル範囲を指定する方法
        '.SetActiveCell(2, 2) ' <== 明示的なアクティブ枠の設定
        .AddSelection(2, 2, 2, 2)
    End With
End Sub

以下は、VBA互換APIのSelectCellAメソッドを実行したときの画像で、選択範囲にアクティブ枠が設定されています。VBAのSelectCellを実行したときも、この画像と同じ状態になります。

SPREADのVBA互換APIでセルの選択

一方、従来のコードのSelectCellBを実行したときには、アクティブ枠は移動しないでセル範囲が選択状態に設定されるだけです。アクティブ枠を移動するには、あらかじめSetAcitiveCellメソッドを呼び出しておく必要があります。

SPREADの従来のAPIでセルの選択

シートのコピー

最後に、シートをコピーして現在のシートの前に挿入する例を紹介します。

VBAではコピー元シートの前か後にしかコピーしたシートを挿入できませんが、VBA互換APIでは、コピーしたシートを任意の位置に挿入できます。また、従来のコードには、シートのコピーと挿入をまとめて実行する機能がないので、シートのコピーと挿入をそれぞれ別個に実行する必要があります。

Private Sub CopySheet()
    With Worksheets("Sheet1")
        .Copy Before:=Worksheets("Sheet1")
    End With
End Sub
Private Sub CopySheetA()
    With FpSpread1.Sheets("Sheet1").AsWorksheet()
        ' ユニークなシート名が自動的に設定されます
        .Copy(position:=0)
    End With
End Sub
Private Sub CopySheetB()
    With FpSpread1.Sheets("Sheet1")
        ' ユニークなシート名を明示的に設定しないと例外が発生します
        Dim sheet = DirectCast(.Clone(), FarPoint.Win.Spread.SheetView)
        sheet.SheetName = "Sheet2"
        FpSpread1.Sheets.Insert(0, sheet)
        'FpSpread1.ActiveSheetIndex = 0 ' アクティブシートの指定
    End With
End Sub

VBA互換APIのCopySheetAメソッドを実行したときの画像を以下に示します。自動的にユニークなシート名が割り当てられているほか、挿入したシートが自動的にアクティブになっています。

SPREADのVBA互換APIでシートのコピー

以下は、従来のコードのCopySheetBメソッドを実行したときのものです。シート名が重複しないように、コピー先のシートの名称を明示的に変更してから挿入する必要があります。また、挿入されたシートが自動的にアクティブになることはありません。

SPREADの従来のAPIでシートのコピー

おわりに

以上がSPREADの「ExcelのVBA互換のAPIセット」、「SPREADの従来のAPI」、「ExcelのVBA」、それぞれでコーディングした場合の比較でした。

VBA互換のAPIを使用することにより、VBAとほとんど同じコードで各種操作が実現可能なので、VBAの開発経験や資産を活かすことができます。また、従来のAPIと比較してコードがシンプルになるので、コーディング量の削減にも寄与します。

VBA互換のAPIはバージョン「12.0J」以降で使用可能なので、まだお試しいただいていない方はご利用を是非検討いただけますと幸いです。