Understanding the concept of binding

Each program in the Microsoft Office suite comes with its own Object Library. As you know, the Object Library is a kind of encyclopedia of all the objects, methods, and properties available in each Office application. Excel has its own object library, just as all the other Office applications have their own object library.

In order for Excel to be able to speak to another Office program, you have to bind it to that program. Binding is the process of exposing the Object Library for a server application to a client application. There are two types of binding: early binding and late binding.

Early binding

With early binding, you explicitly point a client application to the server application’s Object Library in order to expose its object model during design-time or while programming. Then you use the exposed objects in your code to call a new instance of the application as such:

    Dim XL As Excel.Application
    Set XL = New Excel.Application

Early binding has several advantages:

• Because the objects are exposed at design-time, the client application can compile your code before execution. This allows your code to run considerably faster than with late binding.
• Since the object library is exposed during design time, you have full access to the server application’s object model in the Object Browser.
• You have the benefit of using intellisense. Intellisense is the functionality you experience when you type a keyword and a dot (.) or an equal sign (=) and you see a popup list of the methods and properties available to you.
• You automatically have access to the server application’s built-in constants.

To use early binding, you will need to create a reference to the appropriate object library by choosing the Tools ➜ References command in Visual Basic Editor (VBE). In the References dialog box (shown in Figure 10.1), find the Office application you want to automate, and then place a check next to it. The version of the available library on your system will be equal to your version of Office. So for instance, if you are working with Office 2016, you will have the PowerPoint 16.0 library. If you have Office 2013, you will have the PowerPoint 15.0 library.

    Dim XL As Object
    Set XL = CreateObject("Excel.Application")

A simple automation example

The following example demonstrates how to create a Word object by using late binding. This procedure creates an instance of Word, displays the current version number, closes the Word application, and then destroys the object (thus freeing the memory that it used):

Sub GetWordVersion()
    Dim WordApp As Object
    Set WordApp = CreateObject("Word.Application")
    MsgBox WordApp.Version
    WordApp.Quit
    Set WordApp = Nothing
End Sub

This example can be adjusted to use early binding. Before doing so, we will need to activate the References dialog box in VBE (Tools ➜ References) and set a reference to the Word object library. Once we set the appropriate reference, we can use the following code:

Sub GetWordVersion()
    Dim WordApp As New Word.Application
    MsgBox WordApp.Version
    WordApp.Quit
    Set WordApp = Nothing
End Sub

Running an Access Query from Excel

Here’s a nifty macro for those of you who often copy and paste the results of your Microsoft Access queries to Excel. In this macro, you use DAO (Data Access Object) to open and run an Access query in the background and output the results into Excel.

In this macro, you are pointing Excel to an Access database and pulling data from an existing Access query. You then store that query in a Recordset object, which you can use to populate your Excel spreadsheet.

Sub RunAccessQuery()
'Declare your variables
    Dim MyDatabase As DAO.Database
    Dim MyQueryDef As DAO.QueryDef
    Dim MyRecordset As DAO.Recordset
    Dim i As Integer
'Identify the database and query
    Set MyDatabase = DBEngine.OpenDatabase _
                     ("C:TempYourAccessDatabse.accdb")
    Set MyQueryDef = MyDatabase.QueryDefs("Your Query Name")
'Open the query
    Set MyRecordset = MyQueryDef.OpenRecordset
'Clear previous contents
     Sheets("Sheet1").Select
     ActiveSheet.Range("A6:K10000").ClearContents
'Copy the recordset to Excel
     ActiveSheet.Range("A7").CopyFromRecordset MyRecordset
'Add column heading names to the spreadsheet
    For i = 1 To MyRecordset.Fields.Count
    ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
    Next i
End Sub

Running an Access macro from Excel

You can run Access macros from Excel, using automation to fire the macro without opening Access. This technique can not only be useful for running those epic macros that involve a multi-step series of 20 queries, but can also come in handy for everyday tasks like outputting Access data to an Excel file.

The following macro is a simple way to trigger an Access macro programmatically.

Sub RunAccessMacro()
'Declare your variables
    Dim AC As Access.Application
'Start Access and open the target database
    Set AC = New Access.Application
             AC.OpenCurrentDatabase _
            ("C:TempYourAccessDatabse.accdb")
'Run the Target Macro
    With AC
        .DoCmd.RunMacro"MyMacro"
        .Quit
    End With
End Sub

Sending Excel data to a Word document

If you find that you are constantly copying and pasting data into Microsoft Word, you can use a macro to automate this task.

Before walking through the macro, it’s important to go over a few set up steps.

To set up for a process like this, you must have a template Word document already created. In that document, create a bookmark tagging the location where you want your Excel data to be copied.

To create a bookmark in a Word document, place your cursor where you want the bookmark, select the Insert tab, and select Bookmark (found under the Links group). This will activate the Bookmark dialog box where you assign a name for your bookmark. Once the name has been assigned, click the Add button.

Sub SendDataToWord()
'Declare your variables
    Dim MyRange As Excel.Range
    Dim wd As Word.Application
    Dim wdDoc As Word.Document
    Dim WdRange As Word.Range
'Copy the defined range
   Sheets("Revenue Table").Range("B4:F10").Copy
'Open the target Word document
    Set wd = New Word.Application
    Set wdDoc = wd.Documents.Open _
    (ThisWorkbook.Path &"" &"PasteTable.docx")
    wd.Visible = True
'Set focus on the target bookmark
    Set WdRange = wdDoc.Bookmarks("DataTableHere").Range
'Delete the old table and paste new
    On Error Resume Next
    WdRange.Tables(1).Delete
    WdRange.Paste 'paste in the table
'Adjust column widths
    WdRange.Tables(1).Columns.SetWidth _
    (MyRange.Width / MyRange.Columns.Count), wdAdjustSameWidth
'Reinsert the bookmark
    wdDoc.Bookmarks.Add"DataTableHere", WdRange
'Memory cleanup
    Set wd = Nothing
    Set wdDoc = Nothing
    Set WdRange = Nothing
End Sub

Simulating mail merge with a Word document

One of the most requested forms of integration with Word is the mail merge. In most cases, mail merge refers to the process of creating one letter or document for each customer in a list of customers. For example, suppose you had a list of customers and you wanted to compose a letter to each customer. With mail merge, you can write the body of the letter one time and then run the Mail Merge feature in Word to automatically create a letter for each customer, affixing the appropriate, address, name, and other information to each letter.

If you are an automation buff, you can use a macro to simulate the Word Mail Merge function from Excel. The idea is relatively simple. You start with a template that contains bookmarks identifying where each element of contact information will go. With the template set to go, the idea is to simply loop through each contact in your contact list, assigning the component pieces of their contact information to the respective bookmarks.

Sub WordMailMerge()
'Declare your variables
    Dim wd As Word.Application
    Dim wdDoc As Word.Document
    Dim MyRange As Excel.Range
    Dim MyCell As Excel.Range
    Dim txtAddress As String
    Dim txtCity As String
    Dim txtState As String
    Dim txtPostalCode As String
    Dim txtFname As String
    Dim txtFullname As String
'Start Word and add a new document
    Set wd = New Word.Application
    Set wdDoc = wd.Documents.Add
    wd.Visible = True
'Set the range of your contact list
    Set MyRange = Sheets("Contact List").Range("A5:A24")
'Start the loop through each cell
    For Each MyCell In MyRange.Cells
'Assign values to each component of the letter
    txtAddress = MyCell.Value
    txtCity = MyCell.Offset(, 1).Value
    txtState = MyCell.Offset(, 2).Value
    txtPostalCode = MyCell.Offset(, 3).Value
    txtFname = MyCell.Offset(, 5).Value
    txtFullname = MyCell.Offset(, 6).Value
'Insert the structure of template document
    wd.Selection.InsertFile _
    ThisWorkbook.Path &"" &"MailMerge.docx"
'Fill each relevant bookmark with respective value
    wd.Selection.Goto What:=wdGoToBookmark, Name:="Customer"
    wd.Selection.TypeText Text:=txtFullname
    wd.Selection.Goto What:=wdGoToBookmark, Name:="Address"
    wd.Selection.TypeText Text:=txtAddress
    wd.Selection.Goto What:=wdGoToBookmark, Name:="City"
    wd.Selection.TypeText Text:=txtCity
    wd.Selection.Goto What:=wdGoToBookmark, Name:="State"
    wd.Selection.TypeText Text:=txtState
    wd.Selection.Goto What:=wdGoToBookmark, Name:="Zip"
    wd.Selection.TypeText Text:=txtPostalCode
    wd.Selection.Goto What:=wdGoToBookmark, Name:="FirstName"
    wd.Selection.TypeText Text:=txtFname
'Clear any remaining bookmarks
    On Error Resume Next
    wdDoc.Bookmarks("Address").Delete
    wdDoc.Bookmarks("Customer").Delete
    wdDoc.Bookmarks("City").Delete
    wdDoc.Bookmarks("State").Delete
    wdDoc.Bookmarks("FirstName").Delete
    wdDoc.Bookmarks("Zip").Delete
'Go to the end, insert new page, and start with the next cell
    wd.Selection.EndKey Unit:=wdStory
    wd.Selection.InsertBreak Type:=wdPageBreak
    Next MyCell
'Set cursor to beginning and clean up memory
    wd.Selection.HomeKey Unit:=wdStory
    wd.Activate
    Set wd = Nothing
    Set wdDoc = Nothing
End Sub

Sending Excel data to a PowerPoint presentation

To help get a few fundamentals down, let’s start simple and automate the creation of a PowerPoint presentation containing one slide with a title. In this example, you are copying a range from an Excel file and pasting that range to a slide in a newly created PowerPoint presentation.

Sub CopyRangeToPresentation ()
'Declare your variables
    Dim PP As PowerPoint.Application
    Dim PPPres As PowerPoint.Presentation
    Dim PPSlide As PowerPoint.Slide
    Dim SlideTitle As String
'Open PowerPoint and create new presentation
    Set PP = New PowerPoint.Application
    Set PPPres = PP.Presentations.Add
    PP.Visible = True
Add new slide as slide 1 and set focus to it
    Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)
    PPSlide.Select
'Copy the range as a picture
    Sheets("Slide Data").Range("A1:J28").CopyPicture _
    Appearance:=xlScreen, Format:=xlPicture
'Paste the picture and adjust its position
    PPSlide.Shapes.Paste.Select
    PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
'Add the title to the slide
    SlideTitle ="My First PowerPoint Slide"
    PPSlide.Shapes.Title.TextFrame.TextRange.Text = SlideTitle
'Memory Cleanup
    PP.Activate
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PP = Nothing
End sub

Sending all Excel charts to a PowerPoint presentation

It’s not uncommon to see multiple charts on one worksheet. Many people have the need to copy charts to PowerPoint presentations. The macro here assists in that task, effectively automating the process of copying each one of these charts into its own slide.

In this macro, we loop through the Activesheet.ChartObjects collection to copy each chart as a picture into its own page in a newly created PowerPoint presentation.

Sub CopyAllChartsToPresentation()
'Declare your variables
    Dim PP As PowerPoint.Application
    Dim PPPres As PowerPoint.Presentation
    Dim PPSlide As PowerPoint.Slide
    Dim i As Integer
'Check for charts; exit if no charts exist
    Sheets("Slide Data").Select
    If ActiveSheet.ChartObjects.Count < 1 Then
    MsgBox"No charts existing the active sheet"
    Exit Sub
    End If
'Open PowerPoint and create new presentation
    Set PP = New PowerPoint.Application
    Set PPPres = PP.Presentations.Add
    PP.Visible = True
'Start the loop based on chart count
    For i = 1 To ActiveSheet.ChartObjects.Count
'Copy the chart as a picture
    ActiveSheet.ChartObjects(i).Chart.CopyPicture _
    Size:=xlScreen, Format:=xlPicture
    Application.Wait (Now + TimeValue("0:00:1"))
'Count slides and add new slide as next available slide number
    ppSlideCount = PPPres.Slides.Count
    Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)
    PPSlide.Select
'Paste the picture and adjust its position; Go to next chart
    PPSlide.Shapes.Paste.Select
    PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
    Next i
'Memory Cleanup
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PP = Nothing
End Sub

Convert a workbook into a PowerPoint presentation

This last macro takes the concept of using Excel data in PowerPoint to the extreme. Open the sample workbook called Convert a Workbook into a PowerPoint Presentation.xlsm. In this workbook, you will notice that each worksheet contains its own data about a region. It’s almost like each worksheet is its own separate slide, providing information on a particular region.

The idea here is that you can build a workbook in such a way that it mimics a PowerPoint presentation; the workbook is the presentation itself and each worksheet becomes a slide in the presentation. Once you do that, you can easily convert that workbook into an actual PowerPoint presentation using a bit of automation.

With this technique, you can build entire presentations in Excel where you have better analytical and automation tools. Then you can simply convert the Excel version of your presentation to a PowerPoint presentation.

Sub SendWorkbookToPowerPoint()
'Declare your variables
    Dim pp As PowerPoint.Application
    Dim PPPres As PowerPoint.Presentation
    Dim PPSlide As PowerPoint.Slide
    Dim xlwksht As Excel.Worksheet
    Dim MyRange As String
    Dim MyTitle As String
'Open PowerPoint, add a new presentation and make visible
    Set pp = New PowerPoint.Application
    Set PPPres = pp.Presentations.Add
    pp.Visible = True
'Set the ranges for your data and title
    MyRange ="A1:I27"
'Start the loop through each worksheet
    For Each xlwksht In ActiveWorkbook.Worksheets
    xlwksht.Select
    Application.Wait (Now + TimeValue("0:00:1"))
    MyTitle = xlwksht.Range("C19").Value
'Copy the range as picture
    xlwksht.Range(MyRange).CopyPicture _
    Appearance:=xlScreen, Format:=xlPicture
'Count slides and add new slide as next available slide number
    SlideCount = PPPres.Slides.Count
    Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutTitleOnly)
    PPSlide.Select
'Paste the picture and adjust its position
    PPSlide.Shapes.Paste.Select
    pp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    pp.ActiveWindow.Selection.ShapeRange.Top = 100
'Add the title to the slide then move to next worksheet
    PPSlide.Shapes.Title.TextFrame.TextRange.Text = MyTitle
    Next xlwksht
'Memory Cleanup
    pp.Activate
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set pp = Nothing
End Sub

Mailing the Active Workbook as an Attachment

The most fundamental Outlook task we can perform through automation is sending an email. In the example code shown here, the active workbook is sent to two email recipients as an attachment.

Sub EmailWorkbook()
'Declare our variables
    Dim OLApp As Outlook.Application
    Dim OLMail As Object
'Open Outlook start a new mail item
    Set OLApp = New Outlook.Application
    Set OLMail = OLApp.CreateItem(0)
    OLApp.Session.Logon
'Build our mail item and send
    With OLMail
    .To ="[email protected]; [email protected]"
    .CC =""
    .BCC =""
    .Subject ="This is the Subject line"
    .Body ="Sample File Attached"
    .Attachments.Add ActiveWorkbook.FullName
    .Display
    End With
'Memory cleanup
    Set OLMail = Nothing
    Set OLApp = Nothing
End Sub

Mailing a Specific Range as an Attachment

You can imagine that we may not always want to send our entire workbook through email. This macro demonstrates how we would send a specific range of data rather than the entire workbook.

Sub EmailRange()
'Declare our variables
    Dim OLApp As Outlook.Application
    Dim OLMail As Object
'Copy range, paste to new workbook, and save it
    Sheets("Revenue Table").Range("A1:E7").Copy
    Workbooks.Add
    Range("A1").PasteSpecial xlPasteValues
    Range("A1").PasteSpecial xlPasteFormats
    ActiveWorkbook.SaveAs ThisWorkbook.Path &"TempRangeForEmail.xlsx"
'Open Outlook start a new mail item
    Set OLApp = New Outlook.Application
    Set OLMail = OLApp.CreateItem(0)
    OLApp.Session.Logon
'Build our mail item and send
    With OLMail
    .To ="[email protected]; [email protected]"
    .CC =""
    .BCC =""
    .Subject ="This is the Subject line"
    .Body ="Sample File Attached"
    .Attachments.Add (ThisWorkbook.Path &"TempRangeForEmail.xlsx")
    .Display
    End With
'Delete the temporary Excel file
    ActiveWorkbook.Close SaveChanges:=True
    Kill ThisWorkbook.Path &"TempRangeForEmail.xlsx"
'Memory cleanup
    Set OLMail = Nothing
    Set OLApp = Nothing
End Sub

Mailing a Single Sheet as an Attachment

This example demonstrates how we would send a specific worksheet of data rather than the entire workbook.

Sub EmailWorkSheet()
'Declare our variables
    Dim OLApp As Outlook.Application
    Dim OLMail As Object
'Copy Worksheet, paste to new workbook, and save it
    Sheets("Revenue Table").Copy
    ActiveWorkbook.SaveAs ThisWorkbook.Path &"TempRangeForEmail.xlsx"
'Open Outlook start a new mail item
    Set OLApp = New Outlook.Application
    Set OLMail = OLApp.CreateItem(0)
    OLApp.Session.Logon
'Build our mail item and send
    With OLMail
    .To ="[email protected]; [email protected]"
    .CC =""
    .BCC =""
    .Subject ="This is the Subject line"
    .Body ="Sample File Attached"
    .Attachments.Add (ThisWorkbook.Path &"TempRangeForEmail.xlsx")
    .Display
    End With
'Delete the temporary Excel file
    ActiveWorkbook.Close SaveChanges:=True
    Kill ThisWorkbook.Path &"TempRangeForEmail.xlsx"
'Memory cleanup
    Set OLMail = Nothing
    Set OLApp = Nothing
End Sub

Mailing All Email Addresses in Your Contact List

Ever need to send out a mass mailing such as a newsletter or a memo? Instead of manually entering each of your contacts’ email addresses, you can run the following procedure. In this procedure, you send out one email, automatically adding all the email addresses in your contact list to your email.

Sub EmailContactList()
'Declare our variables
    Dim OLApp As Outlook.Application
    Dim OLMail As Object
    Dim MyCell As Range
    Dim MyContacts As Range
'Define the range to loop through
    Set MyContacts = Sheets("Contact List").Range("H2:H21")
'Open Outlook
    Set OLApp = New Outlook.Application
    Set OLMail = OLApp.CreateItem(0)
    OLApp.Session.Logon
'Add each address in the contact list
    With OLMail
          For Each MyCell In MyContacts
          .BCC = .BCC & Chr(59) & MyCell.Value
          Next MyCell
        .Subject ="Sample File Attached"
        .Body ="Sample file is attached"
        .Attachments.Add ActiveWorkbook.FullName
        .Display
    End With
'Memory cleanup
    Set OLMail = Nothing
    Set OLApp = Nothing
End Sub

Using the VBA Shell function

The VBA Shell function makes launching other programs relatively easy. Following is an example of VBA code that launches the Windows Calculator:

Sub StartCalc()
    Dim Program As String
    Dim TaskID As Double
    On Error Resume Next
    Program ="calc.exe"
    TaskID = Shell(Program, 1)
    If Err <> 0 Then
        MsgBox"Cannot start" & Program, vbCritical,"Error"
    End If
End Sub

The Shell function returns a task identification number for the application specified in the first argument. You can use this number later to activate the task. The second argument for the Shell function determines how the application is displayed. (1 is the code for a normal-size window that has the focus.) Refer to the Help system for other values for this argument.

If the Shell function isn’t successful, it generates an error. Therefore, this procedure uses an On Error statement to display a message if the executable file can’t be found or if some other error occurs.

It’s important to understand that your VBA code doesn’t pause while the application that was started with the Shell function is running. In other words, the Shell function runs the application asynchronously. If the procedure has more instructions after the Shell function is executed, these instructions are executed concurrently with the newly loaded program. If any instruction requires user intervention (for example, displaying a message box), Excel’s title bar flashes while the other application is active.

In some cases, you may want to launch an application with the Shell function, but you need your VBA code to pause until the application is closed. For example, the launched application might generate a file that is used later in your code. Although you can’t pause the execution of your code, you can create a loop that does nothing except monitor the application’s status. The example that follows displays a message box when the application launched by the Shell function has ended:

Declare PtrSafe Function OpenProcess Lib"kernel32" _
    (ByVal dwDesiredAccess As Long, _
    ByVal bInheritHandle As Long, _
    ByVal dwProcessId As Long) As Long
Declare PtrSafe Function GetExitCodeProcess Lib"kernel32" _
    (ByVal hProcess As Long, _
    lpExitCode As Long) As Long
Sub StartCalc2()
    Dim TaskID As Long
    Dim hProc As Long
    Dim lExitCode As Long
    Dim ACCESS_TYPE As Integer, STILL_ACTIVE As Integer
    Dim Program As String
    ACCESS_TYPE = &H400
    STILL_ACTIVE = &H103
    Program ="Calc.exe"
    On Error Resume Next
'   Shell the task
    TaskID = Shell(Program, 1)
'   Get the process handle
    hProc = OpenProcess(ACCESS_TYPE, False, TaskID)
    If Err <> 0 Then
        MsgBox"Cannot start" & Program, vbCritical,"Error"
        Exit Sub
    End If
    Do  'Loop continuously
'       Check on the process
        GetExitCodeProcess hProc, lExitCode
'       Allow event processing
        DoEvents
    Loop While lExitCode = STILL_ACTIVE
'   Task is finished, so show message
    MsgBox Program &" was closed"
End Sub

While the launched program is running, this procedure continually calls the GetExitCodeProcess function from a Do-Loop structure, testing for its returned value (lExitCode). When the program is finished, lExitCode returns a different value, the loop ends, and the VBA code resumes executing.

Using the Windows ShellExecute API function

ShellExecute is a Windows Application Programming Interface (API) function that is useful for starting other applications. Importantly, this function can start an application only if an associated filename is known (assuming that the file type is registered with Windows). For example, you can use ShellExecute to display a web document by starting the default web browser. Or you can use an email address to start the default email client.

The API declaration follows (this code works only with Excel 2010 or later):

Private Declare PtrSafe Function ShellExecute Lib"shell32.dll" _
  Alias"ShellExecuteA" (ByVal hWnd As Long, _
  ByVal lpOperation As String, ByVal lpFile As String, _
  ByVal lpParameters As String, ByVal lpDirectory As String, _
  ByVal nShowCmd As Long) As Long

The following procedure demonstrates how to call the ShellExecute function. In this example, it opens a graphics file by using the graphics program that’s set up to handle JPG files. If the result returned by the function is less than 32, an error occurred.

Sub ShowGraphic()
    Dim FileName As String
    Dim Result As Long
    FileName = ThisWorkbook.Path &"flower.jpg"
    Result = ShellExecute(0&, vbNullString, FileName, _
        vbNullString, vbNullString, vbNormalFocus)
    If Result < 32 Then MsgBox"Error"
End Sub

The next procedure opens a text file, using the default text file program:

Sub OpenTextFile()
    Dim FileName As String
    Dim Result As Long
    FileName = ThisWorkbook.Path &"	extfile.txt"
    Result = ShellExecute(0&, vbNullString, FileName, _
        vbNullString, vbNullString, vbNormalFocus)
    If Result < 32 Then MsgBox"Error"
End Sub

The following example is similar, but it opens a web URL by using the default browser:

Sub OpenURL()
    Dim URL As String
    Dim Result As Long
    URL ="http://spreadsheetpage.com"
    Result = ShellExecute(0&, vbNullString, URL, _
        vbNullString, vbNullString, vbNormalFocus)
    If Result < 32 Then MsgBox"Error"
End Sub

You can use this technique also with an email address. The following example opens the default email client (if one exists) and then addresses an email to the recipient:

Sub StartEmail()
    Dim Addr As String
    Dim Result As Long
    Addr ="mailto:[email protected]"
    Result = ShellExecute(0&, vbNullString, Addr, _
        vbNullString, vbNullString, vbNormalFocus)
    If Result < 32 Then MsgBox"Error"
End Sub

Using AppActivate

You may find that if an application is already running, using the Shell function may start another instance of it. In most cases, however, you want to activate the instance that’s running — not start another instance of it.

The following StartCalculator procedure uses the AppActivate statement to activate an application (in this case, the Windows Calculator) if it’s already running. The argument for AppActivate is the caption of the application’s title bar. If the AppActivate statement generates an error, Calculator is not running and the routine starts the application.

Sub StartCalculator()
    Dim AppFile As String
    Dim CalcTaskID As Double
    AppFile ="Calc.exe"
    On Error Resume Next
    AppActivate"Calculator"
    If Err <> 0 Then
        Err = 0
        CalcTaskID = Shell(AppFile, 1)
        If Err <> 0 Then MsgBox"Can't start Calculator"
    End If
End Sub

Running Control Panel dialog boxes

Windows provides quite a few system dialog boxes and wizards, most of which are accessible from the Windows Control Panel. You might need to display one or more of these from your Excel application. For example, you might want to display the Windows Date and Time dialog box.

The key to running other system dialog boxes is to execute the rundll32.exe application by using the VBA Shell function.

The following procedure displays the Date and Time dialog box:

Sub ShowDateTimeDlg()
  Dim Arg As String
  Dim TaskID As Double
  Arg ="rundll32.exe shell32.dll,Control_RunDLL timedate.cpl"
  On Error Resume Next
  TaskID = Shell(Arg)
  If Err <> 0 Then
      MsgBox ("Cannot start the application.")
  End If
End Sub

Following is the general format for the rundll32.exe application:

rundll32.exe shell32.dll,Control_RunDLL filename.cpl, n,t

where:

• filename.cpl: The name of one of the Control Panel *.CPL files
• n: The zero-based number of the applet in the *.CPL file
• t: The number of the tab (for multitabbed applets)