addins in vba?

[mortgageman]

I can't get over the feeling that I have asked this before, 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

 

[Joe Was]

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]

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]

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]

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]

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.