Trouble calling Excel Data Analysis ToolPak functions from Access VBA

Question

Trouble calling Excel Data Analysis ToolPak functions from Access VBA

Rick Thackeray 0

I am trying to run Excel's Data Analysis ToolPak functions from Access VBA, but I receive an error message: Application-Defined or Object-Defined error. Here is my code: objExcel.Run "ATPVBAEN.XLAM!Regress", objSheet.Range(rng_y), objSheet.Range(rng_x), objSheet.Range(rng_reg_out), True, True, 1, 0.95, True, True, False, False, False I start an instance of Excel with the CreateObject method in a Sub prior to the above line and pass a pointer to the function trying to access the toolpak.. The Excel created shows the Data Analysis ToolPak is loaded when checked under Options, but does not show on the Data toolbar ribbon. I have checked for the toolpak functions being loaded and they are. If I try doing the call against a fully qualified path to ATPBAEN.XLAM I get a message that effectively says I can't do that because of security considerations. If I start Excel from my taskbar shortcut, it shows the toolpak in the Data section. Any help would be greatly appreciated. Even if I could understand the differences in the way Excel is started, it would be helpful. Thank you. Rick

George Hepworth 23,120 Reputation points Volunteer Moderator

2026-08-19T16:20:11.3566667+00:00

It would help anyone trying to follow the logic of your VBA procedure if you copy and paste it exactly as it is here in a followup.

We don't need a screenshot of it. A copy/paste of the code is better.

That does two things. It show exactly what the code does, instead of having to guess from a narrative description. It also allows someone to try it out on their own computer, which is always a good thing when troubleshooting.

Thanks for helping us help you.
Rick Thackeray 0 Reputation points

2026-08-19T17:07:26.8866667+00:00

Thank you, George. I have decided to pursue a workaround. All of the Excel Worksheet functions perform as expected for me when I call them from my VBA code. I'll be able to code the logic to prepare the statistical analyses I require without using the toolpak. In future, I will indeed cut and paste the logic from my code. That is excellent advice. Rick

1 answer

Your answer

George Hepworth 23,120 Reputation points Volunteer Moderator

2026-08-19T16:20:11.3566667+00:00

It would help anyone trying to follow the logic of your VBA procedure if you copy and paste it exactly as it is here in a followup.

We don't need a screenshot of it. A copy/paste of the code is better.

That does two things. It show exactly what the code does, instead of having to guess from a narrative description. It also allows someone to try it out on their own computer, which is always a good thing when troubleshooting.

Thanks for helping us help you.
Rick Thackeray 0 Reputation points

2026-08-19T17:07:26.8866667+00:00

Thank you, George. I have decided to pursue a workaround. All of the Excel Worksheet functions perform as expected for me when I call them from my VBA code. I'll be able to code the logic to prepare the statistical analyses I require without using the toolpak. In future, I will indeed cut and paste the logic from my code. That is excellent advice. Rick

Answer 1

Marcin Policht 106.8K MVP Volunteer Moderator

The likely issue is that CreateObject("Excel.Application") starts a separate Excel instance, and that instance does not necessarily load the Analysis ToolPak VBA add-in the same way as Excel started normally from the taskbar. Seeing the ToolPak enabled in Excel Options does not guarantee that ATPVBAEN.XLAM is loaded in your automation instance.

Try loading the VBA add-in before calling Regress:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Dim ai As Object
For Each ai In objExcel.AddIns
    If InStr(1, ai.Name, "Analysis ToolPak - VBA", vbTextCompare) > 0 Then
        ai.Installed = True
        Exit For
    End If
Next

Then use your existing call:

objExcel.Run "ATPVBAEN.XLAM!Regress", _
    objSheet.Range(rng_y), objSheet.Range(rng_x), _
    objSheet.Range(rng_reg_out), True, True, 1, 0.95, _
    True, True, False, False, False

Also verify that objSheet was created from the same objExcel instance. Do not use the full filesystem path to ATPVBAEN.XLAM; that can trigger Excel's security restrictions.

The key difference is that Excel launched from the taskbar goes through the normal Excel startup process, while CreateObject creates a fresh automation instance with its own add-in state.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Rick Thackeray 0 Reputation points

2026-08-19T16:46:24.58+00:00

Hi Marcin: I think your advice will work for most people. I really believe there is something strange going on with my profile. I've done the full repair of office to no avail. When I run the code to Install the toolpak the routine doesn't find the name. I put a Debug.Print in and the names of the installed addins it printed are:

ANALYS32.XLL, ATPVBAEN.XLAM, EUROTOOL.XLAM, SOLVER.XLAM

I tried substituting the ATPVBAEN.XLAM name into the Instring function. It executed the .Installed logic, but this did not change anything at the call end. The error I get is that the system cannot find ATPVBAEN.XLAM. The default search path it's using is to my OneDrive documents folder. Thank you for your help. It was worth a try. I think I may be better off contacting Microsoft support to see if there are security issues with my account. I do know that I am unable to change the email id associated with my Microsoft account. I think this may be causing me other problems as well. Thanks again, for your help. Rick
Rick Thackeray 0 Reputation points

2026-08-20T21:54:33.73+00:00
Hi Marcin: I have found a way that works for me, so I thought I would post it for others who may experience this issue:

I opened Excel from the Excel Shortcut Icon on my task bar and saved the workbook with a blank sheet1 to my harddrive.

I then added a Command Button to the form that requires the Data Analysis ToolPak and put the location of the blank workbook in the HyperLink Address setting. The user will be required to click this button before they can perform any calculations.

I put this logic in the Sub in back of the button that launches the calculation function: On Error Resume Next
Set objExcel = GetObject(, "Excel.Application") On Error GoTo 0 If objExcel Is Nothing Then MsgBox "Please click the Open Blank Workbook button. This is necessary to enable certain calculations used in the various functions." Exit Sub Else ' objExcel.Visible = 0 objExcel.Visible = -1 Set objWorkbook = objExcel.Workbooks(1) Set objSheet = objWorkbook.Sheets(1) End If

The Data Analysis Toolpak functions are then called from my calculations module. I have the option to make the spreadsheet visible or not. I'll keep it visible until I have fully developed and debugged my code.

Here is the call in the function performing the calculation:

objExcel.Run "ATPVBAEN.XLAM!Regress", _

y_range, _

x_range, _

False, False, 0.95, reg_out_range, False, False, False, False, False

Thanks, again for your help.