数式のSMALL関数の第2引数にセル参照を設定するとエラーになる
対象製品
SPREAD for Windows Forms 7.0J
状況
修正済み
詳細
数式のSMALL関数の第2引数にセル参照を設定するとエラー(#VALUE!)になります。
【手順】
1.新規フォームにSPREADを配置します
2.下記サンプルコードをコピーし、アプリケーションを実行します
--A6セルにエラー(#VALUE!)が表示されます
【サンプルコード】
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
' テストデータの設定
FpSpread1.ActiveSheet.Cells(0, 0).Value = 1
FpSpread1.ActiveSheet.Cells(1, 0).Value = 4
FpSpread1.ActiveSheet.Cells(2, 0).Value = 8
FpSpread1.ActiveSheet.Cells(3, 0).Value = 3
FpSpread1.ActiveSheet.Cells(0, 2).Value = 2
' 数式の設定
FpSpread1.ActiveSheet.Cells(5, 0).Formula = "SMALL(A1:A4,C1)"
End Sub
【手順】
1.新規フォームにSPREADを配置します
2.下記サンプルコードをコピーし、アプリケーションを実行します
--A6セルにエラー(#VALUE!)が表示されます
【サンプルコード】
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
' テストデータの設定
FpSpread1.ActiveSheet.Cells(0, 0).Value = 1
FpSpread1.ActiveSheet.Cells(1, 0).Value = 4
FpSpread1.ActiveSheet.Cells(2, 0).Value = 8
FpSpread1.ActiveSheet.Cells(3, 0).Value = 3
FpSpread1.ActiveSheet.Cells(0, 2).Value = 2
' 数式の設定
FpSpread1.ActiveSheet.Cells(5, 0).Formula = "SMALL(A1:A4,C1)"
End Sub
回避方法
Service Pack 6(v7.0.2019.2008)で修正済み。
Service Pack 6(v7.0.2019.2008)より前のバージョンでは次の回避方法が有効です。
------------------------------------------
以下のサンプルコードのように、独自の関数を作成します。
【サンプルコード】
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
' テストデータの設定
FpSpread1.ActiveSheet.Cells(0, 0).Value = 1
FpSpread1.ActiveSheet.Cells(1, 0).Value = 4
FpSpread1.ActiveSheet.Cells(2, 0).Value = 8
FpSpread1.ActiveSheet.Cells(3, 0).Value = 3
FpSpread1.ActiveSheet.Cells(0, 2).Value = 2
' 数式の設定
FpSpread1.ActiveSheet.Cells(5, 0).Formula = "SMALL(A1:A4,C1)"
' カスタム関数の登録
DirectCast(FpSpread1.ActiveSheet.Models.Data, FarPoint.Win.Spread.Model.DefaultSheetDataModel).AddCustomFunction(New CustomSmallFunction())
Dim lastROw As Integer = FpSpread1.ActiveSheet.GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data)
Dim lastCol As Integer = FpSpread1.ActiveSheet.GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data)
For i As Integer = 0 To lastROw
For j As Integer = 0 To lastCol
Dim cell As FarPoint.Win.Spread.Cell = FpSpread1.ActiveSheet.Cells(i, j)
If Not String.IsNullOrEmpty(cell.Formula) Then
Dim old As String = cell.Formula
cell.Formula = ""
cell.Formula = old
End If
Next
Next
End Sub
Public Class CustomSmallFunction
Inherits FarPoint.CalcEngine.SmallFunctionInfo
Public Overrides Function Evaluate(args As Object()) As Object
If args.Length > 1 Then
args(1) = FarPoint.CalcEngine.CalcConvert.ToInt(FormularHelper.GetValue(args(1), 0))
End If
Return MyBase.Evaluate(args)
End Function
End Class
Private Class FormularHelper
Public Shared Function GetValue(o As Object, i As Integer) As Object
If TypeOf o Is FarPoint.CalcEngine.CalcArray Then
Dim array As FarPoint.CalcEngine.CalcArray = DirectCast(o, FarPoint.CalcEngine.CalcArray)
Dim columnCount As Integer = array.ColumnCount
Return array.GetValue(i ¥ columnCount, i Mod columnCount)
ElseIf TypeOf o Is FarPoint.CalcEngine.CalcReference Then
Dim range As FarPoint.CalcEngine.CalcReference = DirectCast(o, FarPoint.CalcEngine.CalcReference)
Dim columnCount As Integer = range.ColumnCount
Return range.GetValue(range.Row + i ¥ columnCount, range.Column + i Mod columnCount)
Else
Return o
End If
End Function
End Class
Service Pack 6(v7.0.2019.2008)より前のバージョンでは次の回避方法が有効です。
------------------------------------------
以下のサンプルコードのように、独自の関数を作成します。
【サンプルコード】
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
' テストデータの設定
FpSpread1.ActiveSheet.Cells(0, 0).Value = 1
FpSpread1.ActiveSheet.Cells(1, 0).Value = 4
FpSpread1.ActiveSheet.Cells(2, 0).Value = 8
FpSpread1.ActiveSheet.Cells(3, 0).Value = 3
FpSpread1.ActiveSheet.Cells(0, 2).Value = 2
' 数式の設定
FpSpread1.ActiveSheet.Cells(5, 0).Formula = "SMALL(A1:A4,C1)"
' カスタム関数の登録
DirectCast(FpSpread1.ActiveSheet.Models.Data, FarPoint.Win.Spread.Model.DefaultSheetDataModel).AddCustomFunction(New CustomSmallFunction())
Dim lastROw As Integer = FpSpread1.ActiveSheet.GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data)
Dim lastCol As Integer = FpSpread1.ActiveSheet.GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data)
For i As Integer = 0 To lastROw
For j As Integer = 0 To lastCol
Dim cell As FarPoint.Win.Spread.Cell = FpSpread1.ActiveSheet.Cells(i, j)
If Not String.IsNullOrEmpty(cell.Formula) Then
Dim old As String = cell.Formula
cell.Formula = ""
cell.Formula = old
End If
Next
Next
End Sub
Public Class CustomSmallFunction
Inherits FarPoint.CalcEngine.SmallFunctionInfo
Public Overrides Function Evaluate(args As Object()) As Object
If args.Length > 1 Then
args(1) = FarPoint.CalcEngine.CalcConvert.ToInt(FormularHelper.GetValue(args(1), 0))
End If
Return MyBase.Evaluate(args)
End Function
End Class
Private Class FormularHelper
Public Shared Function GetValue(o As Object, i As Integer) As Object
If TypeOf o Is FarPoint.CalcEngine.CalcArray Then
Dim array As FarPoint.CalcEngine.CalcArray = DirectCast(o, FarPoint.CalcEngine.CalcArray)
Dim columnCount As Integer = array.ColumnCount
Return array.GetValue(i ¥ columnCount, i Mod columnCount)
ElseIf TypeOf o Is FarPoint.CalcEngine.CalcReference Then
Dim range As FarPoint.CalcEngine.CalcReference = DirectCast(o, FarPoint.CalcEngine.CalcReference)
Dim columnCount As Integer = range.ColumnCount
Return range.GetValue(range.Row + i ¥ columnCount, range.Column + i Mod columnCount)
Else
Return o
End If
End Function
End Class