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 WorkBook
Excel-WorkBook
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
- By using ActiveWorkbook
- 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
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 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:
- Project Window
- Code Window
- 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)
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
How to Enable Macro in Excel
Getting Started with Excel Macro
Excel Macro is a very effective tool. Once you get to know it, you can make wonders. Right from writing … Read more