WorkBooks and WorkSheets

Workbook->WorkSheet->Column->Row
WorkBook stays at the top of the hierarchy. If we want to retrieve the value from a cell then we have to follow the hierarchy to reach to that particular cell. The Entire Excel file is known as WorkBook and one WorkBook can contain many sheets, or to be more precise, many WorkSheets.
Note: While saving the Excel File , Save it as “Macro Enabled WorkBook” (Select Excel Macro Enabled WorkBook from the Save as type drop down in Save As Dialog box  ( FileName.xlsm))

Excel-VBA-MACRO-Saving in .xlsm
Excel-VBA-MACRO-Saving in .xlsm

Read more

Introduction to Excel WorkBook

One complete Excel file is called as WorkBook.

Now this is something interesting, for dealing with the WorkBook, in general we have two options

  1. By using ActiveWorkbook
  2. By Storing the instance of ActiveWorkbook to WorkBook reference

By using ActiveWorkbook

ActiveWorkBook.Sheets(“Sheet1”).Range(“A1”).Value=1

By Storing the instance of ActiveWorkbook to WorkBook reference

Read more

Add Tools to Quick Access Tool Bar(Excel Top Bar)

You can also add the “Commands” or “Tools” to “Quick Access Tool Bar” (top nav bar) in your excel sheet and it will be placed next to “Save” or “undo” icon in the excel sheet and their fore it will be easy for you to use it, it will be like shortcut.
Below are the following steps you need to follow to add commands to your excel “Quick Access Tool Bar”
Main Menu -> Excel Options

Excel-VBA-MACRO-Add Tool or command to excel - 1
Excel-VBA-MACRO-Add Tool or command to excel – 1

Read more

Excel Macro – Visual Basic Editor

The first question will be where do we write the code , Open the Excel and then press “Alt+F11” and you will get the another window named as “Microsoft Visual Basic” and press “Alt+F11” and you will get back to Excel window.

VBA Editor has 3 main windows:

  1. Project Window
  2. Code Window
  3. Properties Window

Project window and Properties window generally appears on the left side of the Editor. You can close them any time you want and to get them back all you need to do is

  • For Project Window : View-> Project Explorer ( Shortcut : Ctrl+R)
  • For Properties Window : View->Properties Window ( Shortcut : F4)

 

Read more

VBA Codes – Record Macro

 

There are two most common ways to write VBA codes,  they are-

  • Record the MACRO.
  • Write it in VBA Editor from scratch.

Obviously it’s easy to record the Macro and use it but there are certain disadvantages when you record the Macro like size of the script goes huge which makes it bit difficult to customize it if needed, When we have to deal with the dynamic contents like getting the search results from Google, in such case its recommended to write the VBA.

1. Record a macro:

Recording a macro is one of the powerful feature in Excel, It will record every action done by the user and it will automatically generate the code for the user and user gets the option to create a shortcut for the Macro so the next time when user wants to

Read more