Macros

A macro is the name used to describe snippets of code that are used to automate repetitive tasks in Microsoft Office.
Macros run inside the office application and require no additional software.
These lines of code are written in a programming language called VBA.
This is an abbreviation for Visual Basic for Application.
The terms 'macro' and 'VBA Code' are equivalent and refer to the same thing.
VBA is the scripting language behind all the Microsoft Office applications (Excel, Word, PowerPoint, Access and Outlook).
Using VBA is the fastest and easiest way to automate tasks and also to customise your Microsoft Office applications.
The better you know the application and its object model, the easier it is to understand the VBA code.
Macros can perform tasks a lot quicker than they can be done manually and often with greater accuracy.
There are two ways to create a macro.
You can either use the Macro Recorder or you can write the statements directly into the Visual Basic Editor (Alt + F11).


Statements

Programs consist of sequences of statements.
Each statement must end with a new line.
A statement that evaluates to a value is called an expression.
A subroutine is a series of statements that performs a specific task.
A function is identical to a subroutine but also has the ability to return a value.
A code module is the name of the component that contains a group of subroutines and functions


Object Models ?

There is a different object model for each of the Office applications and it is organised is a tree like structure.
An object model defines the objects, properties and methods that can be used in your code to control the application.
These can be anything really, workbooks, documents, paragraphs, ranges, slides etc.
All VBA objects are either singular objects or objects in a collection.
Objects in a collection are referenced by an index in the collection.
Most of the time your code can operate on objects without activating or selecting them.
Every object model consists of a list of objects.
Excel has over 300 different objects.


For example in Excel there is a Worksheet object a Chart object and a Cell object.

Application.ActiveSheet 
Application.ActiveChart
Application.ActiveCell


Documentation

The Office 2013 VBA Documentation download provides an offline version of the Visual Basic for Applications (VBA) developer reference for each of the Office client applications.
This also includes the VBA reference content shared amongst all Office client applications.
This can be downloaded from the following link www.microsoft.com/en-gb/download/details.aspx?id=40326
These files must be saved on your C drive and not on a network/shared drive.


This offline version is not installed by default.
This means that when you press Help in the Visual Basic Editor you will be taken to the corresponding MSDN website link.


 


There is no context sensitive help from within the IDE. Pressing F1 will no longer work.
The only way to view the help files are to open them manually from your local drive.
If your help file appears blank, make sure that the "Always ask before opening this file" is unticked.


 



 


Limitations

There are a number of operations that macros/vba cannot perform:
Create ActiveX Controls
Integrate with Visual Source Safe
Build executables
Create ActiveX Dynamic Link Libraries
Use or Integrate between multiple projects
Reuse any Visual Basic Component
Create OLE Automation Servers


© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

Next