Dynamic cell references with MLGetMatrix in Excel VBA

Hello,

I am trying to do a dynamic cell reference in Excel VBA with matlab, but my refrences are pasting the data I need from matlab. Currently, I am using:

perf = Array(27, 87, 150, 206, 268, 326, 383, 440, 497, 554, 611, 671, 731, 791)
start_col = 11

For i = 1 To num_factors
MLEvalString "data=Excel_Output.performance(:,:,i)"
MLGetMatrix "data", Sheets("Sheet1").Range(Cells(perf(i - 1), start_col), Cells(perf(i - 1) + 16, start_col + 15))
Next i

But it is not pasting any of my data into the excel file. There is no issue with MLEvalString, or my cell refrences if I just do:

Sheets("Sheet1").Range(Cells(perf(i - 1), start_col), Cells(perf(i - 1) + 15, start_col + 11)).ClearContents

Answer 1

[Ishaan]

Hey,

I understand that you are facing an issue while referencing cells within the Range object in VBA. This issue occurs when you do not fully qualify the Cells method. When you use Cells without fully qualifying it (Sheets("Sheet1").Cells), it points to the active sheet, not necessarily the one you intended to use. As you shared, ClearContents works because it activates the sheet, but MLGetMatrix does not.

Additionally, I noticed that "i" is being passed as the string “i” and not the value associated to “i” in MLEvalString. You might have intended to use the loop variable “i”.

Here is the code you provided with the corrections made.

perf = Array(27, 87, 150, 206, 268, 326, 383, 440, 497, 554, 611, 671, 731, 791)
start_col = 11
For i = 1 To num_factors
MLEvalString "data=Excel_Output.performance(:,:, " & i & ")"
edat = Sheets("Sheet1").Range( _
Sheets("Sheet1").Cells(perf(i - 1),     start_col), _
Sheets("Sheet1").Cells(perf(i - 1) + 16, start_col + 15) _
).Address(False, False)
MLGetMatrix "data", "Sheet1!" & edat
MatlabRequest
Next i

This should fix the issue, but if the data still doesn't paste, verify the following:

1. MATLAB matrix dimensions match the Excel range size. The matrix size is 17 rows x 16 columns in the code you provided.
2. "num_factors" matches the third dimension of "Excel_Output.performance".
3. The perf array indices align with your data structure.

Hope it helped!

[Brooklyn]

Thank you!!

[Ishaan]

@Brooklyn, please mark my answer as accepted. Thanks