VBA fails after recent Windows update...

Question

VBA fails after recent Windows update...

Jake104E 40

I have this old MS-Access program (from 2002) where i use a matrix routine from Excel.

It has worked until recently - but now fails on my Windows11-PC. I have an older bakup-PC with Windows10. They share the same OneDrive, and the program works on the old PC.

On WIndows-11 it fails with:

Run-time error '1004'

Application-defined or object-defined error

in line:

Set RangeMatrixA = AnyWorkSheet.range(AnyWorkSheet.Cells(1), AnyWorkSheet.Cells(MatDim, MatDim)).Cells

The entire routine is (I most likely found it somewhere back then - as I did not write this code):

Public Sub SolveMatrix(MatDim As Byte)

Dim I As Byte, j As Byte

Dim AnyWorkSheet As Object

Dim RangeMatrixA As Object, RangeMatrixB As Object



If MatDim > 0 Then

    ' Step 1: Open Excel (will stay invisible)

    Set XL = CreateObject("Excel.Application")



    Set AnyWorkSheet = XL.Workbooks.Add.Sheets(1)

    Set RangeMatrixA = AnyWorkSheet.range(AnyWorkSheet.Cells(1), _

                       AnyWorkSheet.Cells(MatDim, MatDim)).Cells

    

    Set RangeMatrixB = AnyWorkSheet.range(AnyWorkSheet.Cells(1, MatDim + 1), _

                       AnyWorkSheet.Cells(MatDim, MatDim + 1)).Cells



    ' Step 2: Fill the Excel-Sheet with the values of the simultaneous equations

    For I = 1 To MatDim

        For j = 1 To MatDim

            RangeMatrixA(I, j) = EQs(I).a(j) ' fill in the correct value

        Next

        RangeMatrixB(I) = -EQs(I).a(0) ' fill in the correct value

    Next

    

    ' Step 3: Let Excel calculate the results

    RangeMatrixB.offset(0, 1).FormulaArray = "=MMULT(MINVERSE(" & _

    RangeMatrixA.Address & ")," & RangeMatrixB.Address & ")"

    

    ' Step 4: Pick the results from the Excel worksheet

    For I = 1 To MatDim

        DP.Zs(I).Tspray = AnyWorkSheet.Cells(I, MatDim + 2)

        DP.Zs(DP.Zs(I).C).Tspray = DP.Zs(I).Tspray - DP.Zs(I).dT

    Next I



    ' Step 5: Goodbye Excel

    AnyWorkSheet.Parent.Close False    ' Close Excel Worksheet without saving

    XL.Quit                            ' Quit Excel

End If

End Sub

How do I fix this?

If relevant - the two PCs are:

Edition Windows 10 Pro

Version 22H2

OS Build 19045.7291

Edition Windows 11 Pro

Version 25H2

OS Build 26200.8655

Experience 1000.26100.315.0

0 comments

4 answers

Your answer

Answer 1

Jake104E 40

After decomposing the statement:

Set RangeMatrixA = AnyWorkSheet.range(AnyWorkSheet.Cells(1, 1), AnyWorkSheet.Cells(MatDim, MatDim)).Cells

into single parts:

    Set r1 = AnyWorkSheet.Cells(1)

    Set r2 = AnyWorkSheet.Cells(5, 5)

    Set r2 = AnyWorkSheet.Cells(MatDim, MatDim)

    Set r3 = AnyWorkSheet.range(r1, r2)

    Set RangeMatrixA = r3.Cells

it fails in: Set r2 = AnyWorkSheet.Cells(MatDim, MatDim)

It turns out that the variable MatDim cannot be of the "byte" type (as it is in the definition of the Sub).

If Cint(MatDim) is used instead - then it works....

And I can even specify the first cell just with one parameter: AnyWorkSheet.Cells(1)

I cant find the data type requirement for the "Cells" parameters - but converting to Integer works...

Hin-V 16,830 Reputation points Microsoft External Staff Moderator

2026-06-11T22:14:37.9366667+00:00

Hi @Jake104E
Thank you for your response and update, I’m really glad to hear that you were able to resolve your issue.

I also appreciate you sharing your findings regarding the MatDim variable not working correctly when declared as a Byte. At this time, I’m not able to find any official documentation that explicitly states that the Cells() method requires a specific data type or rejects Byte yet. This behavior is more likely related to how type conversion is handled between VBA and Excel through COM automation.

That said, your discovery is very valuable. Thank you for taking the time to investigate and share it, this insight could certainly help others encountering a similar issue and contribute to a better understanding of this scenario.

If you need any clarification or further assistance, please feel free to reach out.
Warm thanks and have a nice day.

Answer 2

Hi Hin-V,

Apparently it is related to the definition of the cell range. Both Google, AI and you indicate that I should address the first cell with two "coordinates".

I have then tried the following (where I also set RangeMatrixB first - to see if that works):

    Set AnyWorkSheet = XL.Workbooks.Add.Sheets(1)

    Set RangeMatrixB = AnyWorkSheet.range(AnyWorkSheet.Cells(1, MatDim + 1), AnyWorkSheet.Cells(MatDim, MatDim + 1)).Cells

    Set RangeMatrixA = AnyWorkSheet.range(AnyWorkSheet.Cells(1, 1), AnyWorkSheet.Cells(MatDim, MatDim)).Cells

This fails with in the "Set RangeMatrixA" statement with the same error.

It is strange that the "Set RangeMatrixB" works - but not the "Set RangeMatrixA" - with seemingly identical syntax.

Before all this testing - I have checked the box "Trust access to the VBA project object model", saved - and even rebooted to see if that requires some Office365-sync to take effect.

But still no luck.

Answer 3

Hi @Jake104E

Thank you for reaching out.

Based on your description, the Run-time error '1004' occurs exactly at this line:

Set RangeMatrixA = AnyWorkSheet.range(AnyWorkSheet.Cells(1), AnyWorkSheet.Cells(MatDim, MatDim)).Cells.

I have tried to reproduce this issue on my side with Windows 11 system but it still works as expected. Since this issue relates to the current syntax which might be unstable with recent Windows 11 and Office updates in your device, you could try to replace the two old lines with the following version:

Set RangeMatrixA = AnyWorkSheet.Range(AnyWorkSheet.Cells(1, 1), AnyWorkSheet.Cells(MatDim, MatDim)) 
Set RangeMatrixB = AnyWorkSheet.Range(AnyWorkSheet.Cells(1, MatDim + 1), AnyWorkSheet.Cells(MatDim, MatDim + 1))

Additionally, please ensure that: Open Excel, go to File > Options > Trust Center > Trust Center Settings > Macro Settings, then check the box "Trust access to the VBA project object model" and click OK.

You could give it a try and let me know the result.

Please understand that our initial reply may not always immediately resolve the issue. However, with your help and more detailed information, we can work together to find a solution.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

The error is a classic Excel Automation issue: the code calls Excel objects without fully qualifying them, which can leave hidden references and cause run-time error 1004 when the automation runs on newer environments.

In the failing line:

Set RangeMatrixA = AnyWorkSheet.Range(AnyWorkSheet.Cells(1), _
                       AnyWorkSheet.Cells(MatDim, MatDim)).Cells

Cells is used without being explicitly tied to the worksheet object inside the Range call. This is the pattern described where Visual Basic creates an implicit reference and later fails.

To fix it, qualify all Cells references with the worksheet object, as shown in the documented workaround. For example, change the line to:

Set RangeMatrixA = AnyWorkSheet.Range( _
    AnyWorkSheet.Cells(1, 1), _
    AnyWorkSheet.Cells(MatDim, MatDim) _
).Cells

Similarly, ensure every call to Excel objects, methods, or properties in the routine is fully qualified with the appropriate object variable (such as AnyWorkSheet, RangeMatrixA, or RangeMatrixB). This includes any other Cells, Range, or related calls that might be missing the worksheet qualifier.

This pattern is explicitly documented: unqualified calls to Excel objects cause Visual Basic to establish its own reference to Excel, which is not released until the program ends and can cause automation to fail on subsequent runs. The documented fix is to always precede calls with the correct object variable, as in:

xlSheet.Range(xlSheet.Cells(1,1), xlSheet.Cells(10,2)).Value = "Hello"

Apply the same approach throughout the matrix routine to restore reliable execution on Windows 11.

References:

Excel automation fails second time code runs