There are a lot of benefits of interacting with other applications using VBA language…

• You can use data generated somewhere in other application, for example: creating new PowerPoint slides using Excel charts and tables
• You can perform commands that don’t exist in your application, e.g. inserting Excel calculations in a Word document
• You can expand the functionality of your program, e.g. you cannot make presentations using Excel, so you send the charts, tables and figures to Power Point; sending 100 emails through Outlook using data from a 100-row Excel table
• and much more

How do you do it?

Let me restate our main question to “How to communicate with other persons in the world using English?”

Can you make any other person in the world act on your instructions?

Well, I am not talking here about influence, I am talking about meaning…my point is, will they understand your commands? You can’t command other people, at least they speak your language (English for our example). The good news is that English is a worldwide used language, non-native-speakers know it so communicating with others is relatively easy.

Let’s get back to our primary question “How to communicate with other applications using VBA?”

You can’t command other applications, at least they speak the programming language you/Excel talks (Visual Basic); Yes, Excel speaks Visual Basic programming language.

The good news is that Visual Basic (VB) is a very popular programming language and many applications speak it. So when Excel talks, VBA-enabled applications will get it, easily.

How is this possible?

Every app has its own objects, e.g. Power Point has slides, animations, etc; Outlook has emails, contacts, etc; Excel has sheets, charts, formulas, etc. You can manipulate those app objects using the user interface, including your mouse and keyboard; e.g. you can add a new slide to an existing PowerPoint presentation, you can add a title to a slide, etc. In Excel, you can add a new sheet, write a new formula in a cell, change the background color of cells, etc.

Ok John, but how those apps make their objects available to other apps?

In a sense, every country makes its citizens available to others through a phone directory, if you know a person’s phone  number you can call it. At the same time, every VBA-enabled application makes its objects available to others in the form of a VBA objects library; for example an Excel sheet is a WorkSheet object, a Pivot Table is a PivotTable object, etc. If you know an object name and syntax you can use it.

Here’s a simple 3-step roadmap to communicate with other applications using VBA…

1) First the first…

1.1. Master the app you want to communicate with

You will hardly control Power Point with VBA if you have never created a presentation. To create efficient VBA code you should be proficient using the real objects, e.g. slides, emails, etc.

1.2. Be sure your app speaks VBA language

Your recipient should be ready to receive your instructions, he/she should speak English. At the same time, your to-be-controlled app should understand VB. Your Office package as well as other Microsoft applications supports VBA. Below is a list of some of the MS programs that are VBA-enabled applications…

• Office: Word, Excel, Power Point, Outlook, Access, etc.
• Office for Mac
• Microsoft project
• Microsoft Front Page

There are other popular non-Microsoft programs that are VBA-enable applications…

• AutoCAd – Technical drawings and design; mostly used by civil engineers, mechanical engineers and architects
• ArcGIS – Geographic information systems; mostly used by environmental engineers, planners, etc.

Here’s the full list of VBA-enabled applications.

1.3. Get the app VBA documentation.

What VBA syntax is needed to open a new Word document? You need to know it; what VBA syntax is needed to add a new slide to an existing Power Point presentation? You need to know it.

You can easily obtain all the VBA information in the Help section of your app; you can also use the objects browser to explore the app objects from within Excel.

These are the main things you can get…

• Excel VBA object model, the full hierarchy of VBA objects
• VBA object members: properties and methods of every VBA object
• Visual Basic commands (the same for all applications)

2) Connect with your VBA-enabled application

When you make an international call, the first thing you need to do is entering the country code, e.g. if you want to call to a landline in Ecuador you need the 593 prefix in addition to the phone number.

Dialing 52634232 won’t communicate you with anybody.

While dialing this way will do it…

593-52634232

Think in the country code as a “master key” that opens the gate of every landline phone in the target country.

If you want to communicate with an external VBA-enabled application, you need a key too; this key is activated when you choose the app library in the “References” dialog. Learn more below…

2.1. Activate the VBA-enabled app library

Activate the VBA-enabled app library by launching the VBA interface from within Excel (CTRL+F11), then by going to: Tools>References…

E.g. In the dialog below I activated the libraries of the programs: Access, Outlook, PowerPoint and Word…

You can also reference missing app libraries using the browse button of the “References – VBAProject” dialog. App libraries have a .dll extension.

When you try to access an international phone number without the master key (country code), you get a message like: “Enter the country code first and try again”; the same happens in VBA, you cannot access external app objects without activating its library first. You will get an error like the one below…

Important notice: each VBA-enabled application library is installed at the moment you install the program in your computer.

2.2. Create a VBA-enabled app object

Using the master key (country code) is not enough, you need to dial the phone number so you establish communication with the right house and so with the right person. The same in VBA, you need to create an application VBA master object (phone number) so you can access every VBA object member (persons in the house), do it this way…

This line of code establishes communication with Word.

Set MyWordApp = CreateObject("Word.Application")

This line of code establishes communication with Outlook.

Set myOutlookApp = CreateObject("Outlook.Application")

This line of code establishes communication with Excel (from other application)…

Set MyExcelApp = CreateObject("Excel.Application")

3) Manipulate the VBA-enabled app objects

You are doing it very well, here is the 3-step roadmap…

1. Enter the country code so you can communicate with it, in VBA terms, enable the app VBA library so you can access the other app
2. Dial the phone number so you can start talking with the persons in the house, in VBA terms, create the app object so you start manipulating its objects
3. It’s time to communicate with the persons in the house, in VBA terms, manipulate any app VBA object by using the objects hierarchy, object properties and methods, you can also use any VB command

You can see all these VBA concepts in action below…

VBA macro examples that manipulate other applications

VBA macro example 1 – Create a new PowerPoint presentation slide with an Excel chart

After activating the PowerPoint library, you create a PowerPoint app object (code line 8 below) and then you manipulate PowerPoint objects (code lines >8)…

Here’s the macro code…

Public Sub PasteCharttoSlide()
'Pastes the active chart into a new power point presentation
Dim MyPowerPointApp As PowerPoint.Application
Dim MyPresentation As PowerPoint.Presentation
Dim MySlide As PowerPoint.Slide

'Creates instance of PowerPoint
Set MyPowerPointApp = CreateObject("Powerpoint.Application")
MyPowerPointApp.Visible = True

'Creates a presentation
Set MyPresentation = MyPowerPointApp.Presentations.Add

'Adds one slide
Set MySlide = MyPresentation.Slides.Add(1, ppLayoutBlank)

'Copies active chart as a picture
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture

'Pastes chart into slide
MySlide.Shapes.Paste.Select
MyPresentation.Slides(1).Shapes(1).Left = 100
MyPresentation.Slides(1).Shapes(1).Top = 100

'Saves the presentation
MyPresentation.SaveAs "C:/MyPPT.ppt"
MyPowerPointApp.Quit ' close Power Point application
Set MyPowerPointApp = Nothing 'frees memory
Set MyPresentation = Nothing

End Sub

This is the end result…

Download the Excel macro file below…

{filelink=7}

VBA macro example 2 – Send an email from Excel

After activating the Outlook library, you create an Outlook app object (code line 10 below) and then you manipulate Outlook objects (code lines >10)…

Public Sub SendEmail()
'www.innateexcel.com - "Make Excel your second nature"
'John Franco
'Feb 08 2012

'Sends an email using the data of the current row (name in column A, email in column B,sales in column D)
Dim myOutlookApp As Outlook.Application
Dim myeMail As Outlook.MailItem

Set myOutlookApp = New Outlook.Application 'sets a new instance of Outlook application
'Set myOutlookApp = CreateObject("Outlook.Application")
Set myeMail = myOutlookApp.CreateItem(olMailItem) 'sets a new email item

 SalesAmount = Cells(ActiveCell.Row, 4)

 With myeMail
 .Subject = Cells(ActiveCell.Row, 1) & ", your sales amount..." 'sets the subject line, e.g. "Laura, your sales amount"
 .To = Cells(ActiveCell.Row, 2) 'reads the salesman email from active row
 .body = "Dear " & Cells(ActiveCell.Row, 1) & "Your sales amount is: " & Cells(ActiveCell.Row, 4) 'sets the body of email
 .send 'sends email
 End With

 Set myOutlookApp = Nothing 'frees memory
End Sub

This is the result…

Download the Excel macro file below…

{filelink=8}

Resources

• Communicating with other applications