Consulting - Quality
Difference makes the DIFFERENCE
Categorised Menu List
The following are some of the points to compare and contrast VBA with Macro...
- VBA is a subset of Visual Basic Language
- VBA is local to the current application..
- VBA for Ms.Excel is different from that of Ms.Word or Ms.Access
- Enable 'Developer' menu option to avail recording options.
- Macro is a program written by the application itself.
- Application written program is extensive, hence you can edit unnecessary code.
- Conditions can not be written with Application generated code (macro).
- Instead, we can have one recording for each condition.
- Loops can not be written with Application generated (macro-recording)
- In Excel 2003 and before versions '.xls' files has code inclusive.
- Frequently used File types Excel Spread sheets (.xls), Excel Templates (.xlt),
- Excel Addin (.xla), Excel Binary files (.xlb)
- In Excel 2007 and later versions '.xlsm' files should have code
- Frequently used File types Excel Spread sheets (.xlsx),
- Excel File with Code (.xlsm), Excel Templates (.xlst),
- Excel Addin (.xlsa), Excel Binary files (.xlsb)
- Please note, from 2007 and later versions, file extension is of 4 characters.
VISUAL BASIC for APPLICATIONS
WHAT IS VBA, HOW DOES IT WORK?
VBA (Visual Basic for Applications) is a subset of Visual Basic Programming Language.
As the name suggests, those functions that are necessary for an application (like Microsoft Word or Microsoft Excel) are aleniated from Visual Basic Language, and grouped under the title Visual Basic for applications. Hence VBA for Microsoft Word differs from VBA for Microsoft Excel, and VBA for Excel is differs from VBA for OutLook.
Thus we can say that VBA is a subset of VB.
VB Script is a language like Java Script which is dominantly used for internet programming and runs in browser, where as VBA runs from its own application. i.e., VBA Code for Excel runs from Excel, VBA Code for Ms.Word runs from Ms.Word, VBA code for Access runs from Ms.Access etc...also, VB Script is internet programming language from Microsoft Corporation, whereas Java Script is from Sun Microsystems.
The general language constructs of Visual Basic remain the same. For example: 'IF' condition looks the same, as it is in Visual Basic Language, retains the same functionality. Like wise with Looping structures, for example, FOR loop looks the same, as it is in Visual Basic Language.
Meaning: for the people who are familiar with Visual Basic Language, VBA looks fairly simple, but at the same time, it is different as well. Which doesnot necessarily mean, that one should know Visual Basic, before learning VBA., which also doesnot mean that learning to program with VBA is equal to programming in Visual Basic.
How VBA Works?
VBA Code can be generated either by recording (usualy called macro) or by writing code. However, to edit (make changes) to the code, Microsoft provides with VBE (Visual Basic Editor), a work space where, code is written either my application or by user. VBE is another topic, we shall consider it in Event Driven Programming Model. To check, how editor looks like, press ALT + F11.
What is Macro Recording...?
To keep it very simple, it is 'Recording user actions'. May be keyboard activity or Menu activity. Application generates back-ground code for each operation that on the application, and stored in a module. This code can be viewed by pressing Alt + F11.
Screen is displayed for reference which displays application generated code for a simple activity as listed below:
- Select 'Record Macro' from Developer tool bar (menu bar / ribbon)
- Place any text on any of the cells in SpreadSheet
- Press Ctrl + B
- Press Ctrl + I
- Press Ctrl + U
- Press Stop Macro Button
- Press Alt + F11
The above code looks fairly simple, anyhow, we till try to understand, what it means: I have placed numbers for easy refrerence:
- Point 1: 'SUB' indicates that a procedure starts from here, 'Macro1' is the name of the procedure, this is system generated name.
- Point 2: Single quote, is used to comment the text, ie., the code that follows single quote mark any where in the program will not be exectued. Generally it is used to place any remarks on the code.
- Point 3: same as point 2 - System placed a comment to for a reference with the name of the macro as Macro1
- Point 4: Range("E4").Select - Range is a group of cells, where the group may contain a single cell to the entire spreadsheet. eg.,
=> range("A1") implies, this range contains one cell in location A1,
=> range("A2:A4") implies, this range contains cells A2, A3, A4,
=> range("A2:B4") implies, this range contains cells A2, A3, A4, B2, B3, B4,
-
Given context, line 4 - select cell E4.
-
Point 5: after the required cell is selected, change the Font style to Bold.
- Point 5: after the required cell is selected, change the Font style to Italic.
- Point 5: after the required cell is selected, Underline the text.
- Point 6: Select entire column E, set its column width to 11.
- Point 7: END SUB is to end the procedure.
How Macro is different from VBA...?
Macro is dominantly used for linear actions,
VBA is used for taking decisions or repeating the same actions any number of times.
For Macro, a better example is formatting a spread sheet, say for example, you have a spreadsheet, where 1. heading is to be centered, 2. cells to be merged, 3. text to be left aligned, 4. number column should be formatted to two decimal places, 5. date format should be formatted, etc., then Start recording macro, perform each operation in a sequence. Donot repeat the same action, because, when the macro is run, the same action will be repeated twice.
For VBA programing follow Looping Structures with VBA Code link.
Conclusion
Donot get confused with VB script and VBA as generally addressed
There can be any number of macros in a spread sheet. Each macro can be assigned with a short cut key (from keyboard eg: Ctrl + W). This will help to run directly from spreadsheet, instead of running desired macro from VBE.