Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

How can I use Visual Basic editor functions in Excel?

user-image
Question added by Samar Saleh , Community Manager , Bayt.com
Date Posted: 2013/09/04

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.

Daanish Rumani
by Daanish Rumani , Product Manager , Publicis Sapient

Be reminded that you need to have Professional/Ultimate versions of Office to be able to write macros.

Badr Ghatasheh
by Badr Ghatasheh , Software Engineer , bayt.com

You create a macro using Tools > Macro > Visual Basic Editor, write you VB function code which is pure VB syntax, then apply it to the cells you want, for example if I wanted to create a macro to add sales tax [which is too simple to be created as a macro but am doing it for the sake of it's readability], you can write:

Function SalesTax(Price As Variant) As Variant

SalesTax = Price + (Price *0.16)

End Function

Then you'd use it in cell C4 for example to calculate that tax of the price in B4 like this:

SalesTax(B4)

More Questions Like This