addins in vba?

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
I can't get over the feeling that I have asked this before, :oops: but my search turned up nothing.

I have the analysis toolpak installed. Yet when I try to do

application.worksheetfunction.complex

it (complex) doesn't show up in the function list.

Is there something extra I need to do in VBA (to "load" analysis toolpak)?

Gene, "The Mortgage Man", Klein
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Check your security, Tools- Macro - Security... [Trusted Sources] Allow Addins.

This code will test your addins and let you know which are active. The code then askes you if you want to install any addins that it finds that are not installed. To not install a found addin hit Cancel.


Sub CheckAddins()
'Run from ThisWorkbook module.
Dim x As AddIn
Dim a As AddIn
Dim b As AddIn
Dim myCount As Integer
Dim nameX$, thisAddin$, myCAddins$, myAddins$, nameB$
Dim myMsg$, myTitle$, myDefault, myAns

For Each x In Application.AddIns
'Check for any installed Addins.

nameX = x.Name
If x.Installed = True Then
myCount = myCount + 1
myCAddins = myCAddins + nameX & vbCr
End If
Next x
If myCount > 0 Then
MsgBox "These Addins are" & vbCr & "installed into Excel:" & vbCr & _
vbCr & myCAddins
Else
MsgBox "No Excel Addins are currently installed!"
End If

For Each a In Application.AddIns
'Ask to install any Addins.

If a.Installed = False Then
thisAddin = a.Name
myMsg = "Excel Addin: " & thisAddin & "," & vbCr _
& "may be required for full Excel operation!" & vbCr & vbCr & _
"Do you want to install this addin now," & vbCr _
& "automatically?" 'Set prompt.
myTitle = "Install Excel: ""Addin"" now?" 'Set title.
myDefault = "Yes" 'Set default.
'Display message, title, and default value.
myAns = InputBox(myMsg, myTitle, myDefault)
If UCase(myAns) = "YES" Then
a.Installed = True
End If
End If
Next a

For Each b In Application.AddIns
'Report the Addins now installed.

If b.Installed = True Then
nameB = b.Name
myAddins = myAddins + nameB & vbCr
End If
Next b
MsgBox "These Addins are" & vbCr & "installed into Excel:" & vbCr & _
vbCr & myAddins

End Sub
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
Joe, I'm not sure, but I don't think you answered my question.
The analysis tool pak is loaded in IN EXCEL. The function complex, among others, does work IN EXCEL. However, in VBA, when I try application.worksheetfunction.complex, complex does not show up in the list. This leads me to believe that the addin IS IN Excel, but NOT IN VBA.

Gene, "The Mortgage Man", Klein
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
When you write a Function you have the option of registering the Function in Excel. Some addin code does not take this extra step.

Next only some of the Worksheet Functions work within VBA, VBA help has a list of the functions that are available to VBA. The same is true for addins some Functions only work within the Sheet and do not work from VBA.

So it may work and not be registered or it is a Function that is not available to VBA.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
Gene

As far as I know you can't use WorksheetFunction to access functions from an Add-In.

If you want to use functions from the Analysis ToolPak in VBA then make sure you check Analysis ToolPak - VBA under Tools>Add-Ins...

Then goto the VBA Editor and under Tools>References... select ATPVBAEN.XLS.

You should then be able to access those functions in VBA.

If you look in the Object Browser (F2 in the VBA editor) you should find them.
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
That was it Norie. And I was right, I did ask this question before, and you were one of the ones who answered it then! (If only I had remembered to search on atpvbaen. But if I had remembered to search on it I would have remembered to use it!)


Deja Vu.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,126
Messages
5,857,522
Members
431,883
Latest member
Hien

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top