"Sheet1.A1:B5" といったセル範囲の文字列からセルオブジェクトを一発で設定する簡単な方法はありますか？
Excel/VBA の場合は Evaluate メソッドで一発変換できます。
コード: 全て選択
Dim MyRng As Range
Set MyRng = Nothing
On Error Resume Next
Set MyRng = Application.Evaluate("Sheet1!A1:B5")
On Error GoTo 0
If (MyRng Is Nothing) Then
MsgBox "Range Error"
End ifVBA のように一発でできる方法は無いでしょうか？
コード: 全て選択
Public Function RefEditRange2Object(ByVal argRangeString As String) As Object
' RangeString's pattern is "Sheet1.A1:B5"(The $ mark is already removed.)
' When Cell-Range-String is not right, [Nothing] returns.
Dim oSheets As Object
Dim oSheet As Object
Dim oRange As Object
Dim vntRangeString As Variant
vntRangeString = Split(argRangeString, ".")
if (UBound(vntRangeString) <> 1) Then
RefEditRange2Object = Nothing
Else
If (vntRangeString(0) = "") or (vntRangeString(1) = "") Then
RefEditRange2Object = Nothing
Else
oSheets = ThisComponent.getSheets()
If oSheets.hasByName(vntRangeString(0)) Then
oSheet = oSheets.getByName(vntRangeString(0))
On Error Resume Next
oRange = Nothing
oRange = oSheet.getCellRangeByName(vntRangeString(1))
On Error Goto 0
If (oRange is Nothing) Then
RefEditRange2Object = Nothing
Else
RefEditRange2Object = oRange 'OK
End If
Else
RefEditRange2Object = Nothing
End If
End If
End If
End Function