Excel Toolkit Functions

Topic

New Reply

How can I use Excel Toolkit Functions in VBA code. The Add-ins collection only allows you to load the Add-in, but the functions, such as NetWorkDays, don’t appear under WorkSheetFunction. Andy.

Reply | Quote

Replies

If you are referring to the Analysis ToolPak Add-In, there is a special VBA version (Analysis ToolPak – VBA) which you should install. If you go to Tools,Add-Ins, you should see it, just just make sure the box is checked. You will notice in the function wizard that 2 versions of the AddIn functions appear, one in caps the other (VBA) in proper case.

Hope that helps,

Andrew C

Reply | Quote

Although there is the VBA Toolkit Add-in, I need to know how to refer to the functions in my code. For example, Application.WorksheetFunction.NetWorkDays(…)?

Reply | Quote

Try the Evaluate Method, e.g x = Evaluate(“NetworkDays(Now(),Now()+100)”), which can be shortened to x = [NetworkDays(Now(), Now()+100)].

Hope that helps,

Andrew

Reply | Quote

Cheers, this works Ok. However, I feel sure I should be able to reference the function directly, without creating a string. The function must reside in the Funcres.xla Add-in, although I don’t know the name of the module. I’m look for something like ‘Workbooks(“funcres.xla”)…NetWorkDays(..)’?!

Reply | Quote

Actually these functions are part of Atpvbaen.xla. Howe.ver there can be problems depending on the the version of XL you have.

Check article Q165134 at Microsoft’s knowledge base, and see if it can help you to resolve the problem.

Andrew C

Reply | Quote

Sorted! I hadn’t included a Reference to Aptbean.xla. By including this the Toolkit functions appear in the Object Browser. I don’t then need to qualify the reference to the use the function(s). For example, ‘MsgBox Networkdays(Now(),Now()+10)’ works beautifully!! Cheers.

Reply | Quote