"Deadline-Beating Blueprints Trusted by Intermediate and Advanced Excel Users"

How to communicate with other applications using VBA?

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…

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…

References...

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…

User-defined type not defined

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…

Send any Excel data like tables, Pivot tables, etc.

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

The Excel object model demystified

The reason why many Excel VBA beginners fail is that they try to learn Excel VBA without understanding the Excel object model first or even ignoring there’s such as fundamental VBA concept.

If you want to get started with Excel VBA and macros but are still wondering, “what the hell the Excel object model is”, then this simple Excel VBA lesson is for you because today you will finally discover:

  1. Why you need an Excel object model?
  2. What’s the Excel object model?
  3. How to control Excel VBA objects (members) in a hierarchy (Excel object model)
  4. Excel object model resources

Why you need an Excel object model?

You will understand the Excel object model using a simple analogy. In the picture below you see two rivers, 1 and 2, with fishes and the trees 1 and 2 with hanging fruits.

Excel object model - Objects identification and disambiguation

Please identify the fish #1 and fruit #2 in the picture above.

You quickly realize that you can’t find those objects until you know from what tree you want the fruit (tree 1 or 2) and from what river you want the fish (river 1 or 2). This simple problem arises when you deal with a collection of many trees containing a collection of many fruits, and a collection of many rivers containing a collection of many fishes.

To refer to the objects of collections (Excel has the collections of books, sheets, charts, etc.), we need something else. Just think for a moment in a phonebook with just first names…

  • Name, Phone, address
  • Joe, 111111111, Moriarty, NM
  • Joe, 22222222, Shiloh Ranch Rd Lancaster, SC
  • Joe, 23412233, Oakland Dr Lancaster, SC

You could not be sure if any particular Joe is your Joe. You need a sort of disambiguation attribute. In this particular case a last name attribute. We use last names to differentiate every “Joe” from each other.

OK, let’s do it better this time. Please identify the fruit #2 of tree #1 and the fish #1 of river #2 in the picture below…

Excel object model - Objects identification and disambiguation

Now you could do it easily because I specified the collection from which to pick each item.

For now we can conclude that for identifying the element of a collection unequivocally, you need to specify first what collection the item belongs to and then its relative position in that collection.

Referring to any object of the world is easy if we organize all the earth collections and their items in an “earth object model”, all the countries and their items in a “countries object model”, and so on. You can also call it “hierarchy of objects”.

You need an object model to precisely manage the items of collections.

What’s the Excel object model?

Please identify the cell A1 in the Excel spreadsheets below…

Different workbooks and sheets contain an A1 cell

You quickly realize you can’t find the Excel cell object until you know from what Workbook (Book4 or Book5) and Sheet (Sheet1, Sheet2 or Sheet3). The same ambiguity problem arises in Excel because you have a collection of many workbooks containing a collection of many sheets, and each sheet containing one A1 cell.

You should be specific about what A1 cell you refer to. You need a sort of disambiguation attribute. In this particular case the workbook and sheet name.

OK, now please identify the cell A1 of the sheet 2 of the book 5 in the Excel spreadsheets below…

Specifying A1 cell uniquely

Very easy, right? Now you can find any Excel object because you know the exact object from which to pick the cell A1.

Excel macros manipulate Excel VBA objects, which are organized in collections. Here are some of the most important Excel collections…

  • Workbooks collection
  • Sheets collection
  • Charts collection
  • Pivot Tables collection
  • Tables collection
  • Range collection

So guess what?

When you manipulate objects in Excel through VBA code in macros, you need to point to the right objects. You can only control objects by understanding and using an Excel object model or hierarchy.

See a snapshot of the Excel object model below

Excel object model map

How to control Excel VBA objects (members) in a hierarchy (Excel object model)

Here are some VBA code examples:

How to point to a Range Object in Excel?

Workbooks(item).Worksheets(item).Range(item)

  • Workbooks(1).Worksheets(1).Range(“A1”)
  • Workbooks(3).Worksheets(1).Range(“A1”)
  • Workbooks(2).Worksheets(“Sheet1”).Range(“A1:B32”)

How to point to a Chart Object in Excel?

Workbooks(item).Worksheets (item).ChartObjects(item)

  • Workbooks(1).Worksheets(1).ChartObjects(1)
  • Workbooks(3).Worksheets(1).ChartObjects(“Chart1”)
  • Workbooks(1).Worksheets(“Sheet1”).ChartObjects(“Chart1”)

Excel object model resources:

Download an Excel 2003 object model map (pdf) here

Explore more about the Excel object model below: