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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

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
75,927
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,377
Messages
5,547,587
Members
410,801
Latest member
DataMgmtAnalyst7
Top