Using Lotus 123 for Windows 1997 (Yes very old)
I'm trying to do what in Excel VBA is a very simple thing; Offset and resize. But I'm stuck in LotusScript.
'this works fine in Excel
Dim ws As Worksheet
Dim rg As Range
Set ws = ThisWorkbook.Worksheets(1)
Set rg = ws.Range("MyRange").Offset(0, 1)
Set rg = rg.Resize(rg.Rows.Count, rg.Columns.Count - 1)
'how to complete this in LotusScript?
Dim DocLotus As Lotus123.Document
Set DocLotus ...
Dim rg As Lotus123.Range
Set rg = DocLotus.Ranges("MyRange")
'offset one column to the right
'resize one column less
Can you help me? I'm reading the help files but I can't find the right methods to use.
I eventually figured out a way to duplicate the Excel resize method although not exactly. This doesn't go beyond the end of SrcRange but that that was fine for my requirements.
Function Resize(SrcRange As Range, ByVal RowCount As Integer, ByVal ColumnCount As Integer) As Range
'this function resizes the range but cannot go beyond the end of SrcRange
Dim StartCell As Range
Dim EndCell As Range
Set StartCell = SrcRange.cell(0, 0)
Set EndCell = SrcRange.cell(RowCount - 1, ColumnCount - 1)
Dim LeftAddr As String
Dim RightAddr As String
LeftAddr = Left(StartCell.CoordinateString, InStr(StartCell.CoordinateString, "..") + 1)
RightAddr = Left(EndCell.CoordinateString, InStr(EndCell.CoordinateString, "..") - 1)
Dim NewRange As Range
Set NewRange = SrcRange.Parent.Ranges(LeftAddr & RightAddr)
Set Resize = NewRange
End Function
Here is the OffSet method, but again it doesn't go beyond the end of SrcRange
Function OffSet(SrcRange As Range, ByVal byRow As Integer, ByVal byColumn As Integer) As Range
'this function offsets the beginning of the range but does not go beyond the end of SrcRange
Dim StartCell As Range
Dim EndCell As Range
Set StartCell = SrcRange.Cell(byRow, byColumn)
Set EndCell = SrcRange.Cell(SrcRange.EndRow - SrcRange.StartRow, SrcRange.EndColumn - SrcRange.StartColumn)
Dim LeftAddr As String
Dim RightAddr As String
LeftAddr = Left(StartCell.CoordinateString, InStr(StartCell.CoordinateString, "..") + 1)
RightAddr = Left(EndCell.CoordinateString, InStr(EndCell.CoordinateString, "..") - 1)
Dim NewRange As Range
Set NewRange = SrcRange.Parent.Ranges(LeftAddr & RightAddr)
Set OffSet = NewRange
End Function
To extend these to function exactly like Excel, I think I would need a Cells function like this; but again this is incomplete as it only works for the first sheet.
Function Cells(RowNum As Long, ColNum As Long)
Dim rg As Range
Set rg = CurrentDocument.Ranges("A:A1..A:IV8192")
Set rg = rg.Cell(RowNum - 1, ColNum - 1)
Set Cells = rg
End Function
Hope this helps someone.