Read this easy and quick article on what is Excel Macros, how to use macros and where it can be used.
What is VBA?
VBA is Visual Basic for Applications
VBA is Editor in Excel, Word and PowerPoint
VBA is a part of the application, it cannot run independently as VB
VBA is used in Desktop-based applications and VBS is a script language used in the web applications.
What is Macros?
Macros is object oriented program
Macros is part of coding that is written in VBA
Macros is a programming language
Why Macros?
For repeated task
To record the change
To create new formulae
To simplify complicated task
The Object Model
VBA is object-oriented, before you perform any actions you need to specify what object to perform on. VBA syntax is object-first then action. Objects can have relationships with other objects. This relationship is called the object hierarchy. Macros are also called a subroutine, procedure, program.
Ways to run Macros (VBA Code)
Use shortcut key Alt + F8 get to view macro and then select your macro and run
Go to Tab Developers / Macros
Go to Tab View/Macros
Use the shortcut key you assigned
Insert any shape or image and assign the macro to it (Right mouse click and Assign Macro)
Insert a Form Control Button from Developer Tab, Insert, Form Controls
How to Record a Macros
We can start Recording a Macro in the 4 ways mentioned below:
Click on the record macro button at the bottom left hand side
Use Shortcut Alt + T + M + R
Go to Developer tab – Record Macro
Go to View Tab – Record Macro
Now name your macros, assign a shortcut key and decide where to store them (workbook or personal macro workbook)
Get the latest and working keyboard shortcuts for Excel. Learn these Easy Excel Shortcuts.
We’ve put together a list of keyboard shortcuts for Microsoft Excel. Although you can do all of these manoeuvres manually, knowing these tricks will help save you time so you can focus on the stuff that really matters.
Navigation Shortcuts:
Ctrl + 1 : Open the “Format Cells” dialog.
Ctrl + T: “Convert selected cells to a table. You can also select any cell in a range of related data, and pressing Ctrl + T will make it a table.
ARROW KEY: Move one cell in a given direction
CTRL+Arrow Key: Move to the edge of the current data region
HOME: Move to the beginning of the row
CTRL+HOME: Move to the beginning of the worksheet
CTRL+END: Move to the last cell in the worksheet in the lower right corner PAGE DOWN: Move one screen down
PAGE UP: Move one screen up
ALT+PAGE DOWN: Move one screen to the right
ALT+PAGE UP: Move one screen to the left
CTRL+PAGE DOWN: Move to the next sheet in the workbook
CTRL+PAGE UP: Move to the previous sheet in the workbook Enter: Move down selection
SHIFT+ENTER: Move back through a selection
TAB: Move from left to right within the selection
SHIFT+TAB: Move from right to left within the selection
CTRL+G: Goto
Data Selection Shortcuts:
SHIFT+Arrow Key: Extend the selection by one cell
CTRL+SHIFT+Arrow Key: Extend the selection to the edge of the current data region
SHIFT+HOME: Extend the selection to the beginning of the row
CTRL+SHIFT+HOME: Extend the selection to the beginning of the Worksheets
CTRL+SHIFT+END: Extend the selection to the last cell in the Worksheet
CTRL+SPACEBAR: Select the entire column
SHIFT+SPACEBAR: Select the entire row
CTRL+A: Select the entire worksheet, Select all
CTRL+SHIFT+* : Select the current region
CTRL+SHIFT+SPACEBAR: With an object selected, select all objects on a worksheet
Formatting Shortcuts:
CTRL+1: Format cells (display the Format Cells dialogue box) CTRL+SHIFT+$: Apply the Currency format with no decimal (negative numbers appear in parentheses) CTRL+SHIFT+%: Apply the Percentage format with no decimal places CTRL+SHIFT+@: Apply the time format with the hour and minute and AM or PM CTRL+SHIFT+!: Apply the two decimal place format with commas CTRL+SHIFT+&: Apply the outline border CTRL+SHIFT+_: Remove the borders CTRL+B: Apply or remove bold formatting CTRL+ I: Apply or remove italic formatting CTRL+U: Apply or remove an underline CTRL+5: Apply or remove strikethrough ALT+o,c, a: Fit width to cell (Quickly fit column width to the width of the current cell)
Data Entry Shortcuts:
ALT+ENTER: Start a new line of text entry in the same cell
CTRL+DELETE: Delete text to the end of the line SHIFT+F2: Edit a cell comment
CTRL+D: Fill down CTRL+R: Fill to the right
CTRL+ENTER: Fill the selected cell range with entry BACKSPACE: Activate and clear the formula bar when a cell is selected or delete the preceding character in the formula bar
EQUAL SIGN: Start a formula
CTRL+SHIFT+ENTER: Enter a formula as an array formula
ALT+EQUALSIGN: Insert the Auto Sum Formula
ALT+e,s, command: Paste special (Copy part of cell or multiply, divide, add, or subtract selected cells by one number.
CTRL+SHIFT+PLUS SIGN: Insert blank cells Shift+F3: Function wizard
Other Operation Shortcuts:
CTRL+9: Hide rows
CTRL+SHIFT+9: Unhide rows
CTRL+Zero: Hide columns
CTRL+SHIFT+Zero: Unhide columns
CTRL+F9: Minimize workbook
CTRL+F10: Maximize workbook
SHIFT+F10: Show a shortcut menu
CTRL+W: Close window
F10 or ALT: Make the menu bar active
Now that you know Excel Shortcuts, Learn Advance Excel, and step into a new world that can lead you into a job from multiple domains. Also, now that you about Introduction to SAS, learn How to Install SAS University Edition.