Using Cells() in VBA with Excel via ActiveX

20 views (last 30 days)
dpb
dpb on 12 Jan 2022
Edited: dpb on 13 Jan 2022
While off topic, there are enough using Excel and ActiveX that it is a pretty active subject area, so I'll add one more I couldn't solve syntax for --
The Range object Cells addressing mode in VBA allows one to address ranges by numeric calculations and pass row, column instead of a range expression. This is, as the documentation notes, extremely handy when need to calculate addresses to avoid string manipulations to build those address expressions.
So, can anybody figure out how to implement the example code with ActiveX in MATLAB?
Sub SetUpTable()
Worksheets("Sheet1").Activate
For TheYear = 1 To 5
Cells(1, TheYear + 1).Value = 1990 + TheYear
Next TheYear
For TheQuarter = 1 To 4
Cells(TheQuarter + 1, 1).Value = "Q" & TheQuarter
Next TheQuarter
End Sub
I've had no success, all my tries have boiled down to something like
K>> Excel.ActiveSheet.Cells(2,2).Address
Index in position 1 exceeds array bounds (must not exceed 1).
K>> Excel.ActiveSheet.Cells(2).Address
Index exceeds the number of array elements (1).
K>> Excel.ActiveSheet.Cells(1).Address
ans =
'$1:$1048576'
K>> Excel.ActiveSheet.Excel.Cells(1).Address
K>>
where cannot get row,column addressing offsets to be used.
Would be a useful adjunct to @ImageAnalyst's Excel_Utils class...I have gotten the Find method to work to return a cell location of interest and then wanted/needed to reference locations relative to it that raised the issue.

Answers (0)

Products


Release

R2021b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!