I’m trying to integrate the OpenSolver add-in with my VBA code in Excel on MacOS. My goal is to use OpenSolver to solve a traveling salesperson problem (TSP). However, I’m running into several issues with getting the OpenSolver methods to execute correctly. Here’s a detailed description of my setup and the problems I’m encountering:

My Setup:
Excel Version: Microsoft Excel 16.0 on MacOS.
OpenSolver Version: OpenSolver 2.9.3 Linear.

Steps Taken:
Loaded OpenSolver Add-In:
Opened OpenSolver.xlam and confirmed it is visible in the VBA Project Explorer.
Set References:
Checked the OpenSolver reference in the VBA editor.

VBA Code:
I’ve written the following VBA code to define and solve the TSP using OpenSolver:

Sub SolveTSP_OpenSolver()
    On Error GoTo ErrorHandler
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    ' Define the range of distances and decision variables
    Dim distRange As Range, xRange1 As Range, xRange2 As Range, uRange As Range, objCell As Range
    Set distRange = ws.Range("I3:V16") ' Distance matrix
    Set xRange1 = ws.Range("I20:V33") ' Decision variables range 1
    Set xRange2 = ws.Range("I40:V53") ' Decision variables range 2
    Set uRange = ws.Range("W20:W33") ' Order variables
    Set objCell = ws.Range("D23") ' Objective function
    
    ' Define the objective function to minimize the total distance
    objCell.Formula = "=SUMPRODUCT(I20:V33, I3:V16) + SUMPRODUCT(I40:V53, I3:V16)"
    MsgBox "Objective function defined."
    
    ' Clear any previous model definitions
    Debug.Print "Clearing previous model definitions"
    MsgBox "Clearing previous model definitions"
    OpenSolver_DeleteAllConstraints
    OpenSolver_ClearObjective
    
    ' Set the objective function
    Debug.Print "Setting the objective function"
    MsgBox "Setting the objective function"
    OpenSolver_SetObjective "MIN", objCell.Address
    MsgBox "Objective function set successfully"
    
    ' Add constraints: Sum of each row in xRange1 + xRange2 = 1
    Debug.Print "Adding row constraints"
    MsgBox "Adding row constraints"
    Dim i As Integer, j As Integer
    For i = 20 To 33
        Debug.Print "Adding row constraint for row " & i
        OpenSolver_AddConstraint ws.Cells(i, 9).Resize(1, 14).Address & " + " & ws.Cells(i + 20, 9).Resize(1, 14).Address & " = 1"
    Next i
    MsgBox "Row constraints added successfully"
    
    ' Add constraints: Sum of each column in xRange1 + xRange2 = 1
    Debug.Print "Adding column constraints"
    MsgBox "Adding column constraints"
    For j = 9 To 22
        Debug.Print "Adding column constraint for column " & j
        OpenSolver_AddConstraint ws.Cells(20, j).Resize(14, 1).Address & " + " & ws.Cells(40,   j).Resize(14, 1).Address & " = 1"
    Next j
    MsgBox "Column constraints added successfully"

    ' Add sub-tour elimination constraints u_i + 1 <= u_j + N(1 - x_ij)
    Debug.Print "Adding sub-tour elimination constraints"
    MsgBox "Adding sub-tour elimination constraints"
    Dim N As Integer
    N = 14 ' Number of nodes (0 to 13, inclusive)
    For i = 21 To 33
        For j = 10 To 22
            If i <> j Then
                Debug.Print "Adding sub-tour elimination constraint for i = " & i & ", j = " & j
                OpenSolver_AddConstraint ws.Cells(i, 23).Address & " + 1 <= " & ws.Cells(j - 12 + 20, 23).Address & " + " & CStr(N) & " * (1 - " & ws.Cells(i - 1 + 20, j).Address & ")"
            End If
        Next j
    Next i
    MsgBox "Sub-tour elimination constraints added successfully"
    
    ' Ensure decision variables are binary
    Debug.Print "Setting decision variables as binary"
    MsgBox "Setting decision variables as binary"
    OpenSolver_SetBinary xRange1.Address
    OpenSolver_SetBinary xRange2.Address
    MsgBox "Decision variables set as binary successfully"

    ' Solve the problem
    Debug.Print "Solving the problem"
    MsgBox "Solving the problem"
    OpenSolver_Solve
    MsgBox "Solver run completed successfully!", vbInformation
    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
End Sub

Problems Encountered:

Run-time Error 1004: “Method ‘Run’ of object ‘_Application’ failed” when attempting to run OpenSolver methods.

Ensuring OpenSolver Methods: Tried confirming the methods directly via the Immediate Window, but encountering the same issues.

References: Verified that OpenSolver is listed and checked in the References dialog box in VBA.
Questions:

How can I ensure that the OpenSolver methods are correctly accessible in my VBA code on MacOS?
Is there a more reliable way to integrate OpenSolver with VBA without referencing the file path directly?

Are there any specific settings or permissions required on MacOS to allow VBA to execute these OpenSolver methods?

Any help or suggestions would be greatly appreciated. Thank you!

Steps Taken:
Loaded OpenSolver Add-In:
Opened OpenSolver.xlam and confirmed it is visible in the VBA Project Explorer.
Set References:
Checked the OpenSolver reference in the VBA editor.