Using Cells() in VBA with Excel via ActiveX

6 views (last 30 days)
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()
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 =
K>> Excel.ActiveSheet.Excel.Cells(1).Address
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)




Community Treasure Hunt

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

Start Hunting!

Translated by