Friday, October 7, 2016

Create User Define Function and Macro as an Add-in


We work lot in excel and manage our work with functions and macros. I am here creating the process that will save your time and effort to re-create again and again the same functions or macros.

Here we are going to create Add-in that will automatically load when you open your excel, and the function will be available all the time. So let's start.

Step 1: Quickly create an empty add-in for excel


  1. Open excel and Save a blank workbook as a Excel Add-In (*.xlam) in your Add-In folder (%UserProfile%\AppData\Roaming\Microsoft\AddIns)
  2. I named it PersonalAddIn.xlam (Save it with any name you like)
  3. Click Office button.
  4. Click "Excel options" button.
  5. Click "Add-Ins" tab.
  6. Select "Excel Add-ins" from manage dropdown list and click GO
  7. Click "Browse" button
  8. Select the (.xlam) file that you just saved in step 1
  9. Click OK (Make sure you enable)



Step 2: Add custom functions and macros to your personal add-in

  1. Go to the Developer Tab on the ribbon.
  2. Click "View Code"
  3. Click on the project panel you see VBAProject (*.xlam)
  4. Click Insert Menu --> Module
  5. Copy and paste custom functions and macros to code window.


That's it... we are done.

Step 3: Use your user define function

  1. Go to the cell
  2. Click "Insert Function" in formula bar
  3. Select category "User Defined"
  4. Select your custom function
  5. Click OK


Step 4: Use your user define Macro

  1. Go to "Developer" tab
  2. Click "Macros" button
  3. Select/Type the macro name
  4. Click "Run"


Hope this will help you in you daily work, In case of any query you can write to me. Thanks for reading.

No comments:

Post a Comment

Your feedback is always appreciated. I will try to reply to your queries as soon as time allows.Please don't spam,spam comments will be deleted upon reviews.