how do I use a excel function inside the vba editor

Question

I want to write a Excel function like this. It is an extension of the Dec2Bin function

Public Function Dec2BinEx(x As Long)
  Dec2BinEx = dec2bin(x) + 10
End Function

But I am getting an error when trying to use it. How do I call a excel function inside the visual basic editor

Answer 1

You will first of all have to create a module eg from menu select Insert->Module. Then inside this module create a function named main. This function is run by default when code is run. Now inside this function call your own function like this:

Sub main()
    Call Dec2BinEx(your_value_here)
End Sub

Public Function Dec2BinEx(x As Long)
    Dec2BinEx = dec2bin(x) + 10
End Function

Having done that, make sure that you have the reference to dec2bin function or if you create that too. Thanks

Answer 2

In general, you call Excel functions with Application.WorksheetFunction.SomeFunctionName. However, Dec2Bin is special, as it is an Add-In function, and not a pure Excel function. Hence, Application.WorksheetFunction does not work here. Instead, you have to make the functions of the add-in available to your code. To do so, follow these steps

• In Excel, menu Tools/Add-Ins, make sure that the add-in Analysis ToolPak - VBA is imported.

• Then, set a reference to this add-in in your code: in the VBA editor, menu Tools/References, add a reference to atpvbaen.xls.

Then, your original code, as posted in your quesiton should work just fine.