MXL5: Running VBA Procedures from AppleScript

Last reviewed: February 4, 1998
Article ID: Q175425
The information in this article applies to:
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SUMMARY

AppleScript is a scripting language you can use to automate processes on a Macintosh computer. The commands and syntax used in AppleScript are much like a programming language and have the ability to control applications outside the Finder.

This article provides sample AppleScript scripts that demonstrate how to call Microsoft Visual Basic for Applications Sub procedures (including macros) and Function procedures.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

AppleScript is manufactured by Apple Computer Inc., a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this product's performance or reliability.

To call a Visual Basic procedure from an AppleScript script, you can use Evaluate or Do Script, followed by the appropriate syntax for specifying the name of the procedure (and possible arguments). Because there is not a discernible difference between Evaluate and Do Script, Evaluate is used in all sample scripts in this article.

NOTE: All examples provided in this article assume Microsoft Excel is running and the sample procedure being called is contained in an open workbook called "Workbook1."

Recording a Script That Launches a Microsoft Excel Macro

Creating the Macro:

  1. Start Microsoft Excel.

  2. On the Insert menu, point to Macro, and then click Module.

  3. Type the following in the module:

          Sub Test_Recording()
             MsgBox "This is a Excel macro."
          End Sub
    
    

  4. Activate Sheet1.

  5. On the File menu, click Save.

  6. Save the workbook as "Workbook1" (without the quotation marks).

  7. Leave Excel open.

Recording the Script:

  1. Start AppleScript Editor.

  2. If you do not have an untitled script window open, click New Script on the File menu.

  3. Click Record.

  4. On the Application menu, click Microsoft Excel.

  5. On the Tools menu, click Macro.

  6. In the list of macros, click Test_Recording, and then click Run.

    A message box appears displaying the message "This is a Microsoft Excel macro."

  7. Click OK.

  8. On the Application menu, click Script Editor.

  9. Click Stop.

    The recorded script looks like the following:

    tell application "Microsoft Excel"

              Activate
              Evaluate "Workbook1!Test_Recording()"
           end tell
    
        NOTE: To start Visual Basic procedures (including macros) from
        an AppleScript script, you must follow the name of the procedure with
        parentheses.
    
    

  10. On the File menu, click Save.

  11. Select a destination folder, type a name for your script in the Save Script As box, and then click Save.

Playing Back the Recorded Script:

  1. With the recorded script displayed in the Script Editor window, click Run.

    The Test_Recording macro runs, and the message box appears displaying the message "This is a Microsoft Excel macro."

  2. Click OK.

  3. On the Application menu, click Script Editor.

Passing Arguments to a Visual Basic Procedure

In the preceding example, you created a script that calls a Visual Basic macro. A Visual Basic macro is a public Sub procedure that does not accept any arguments. However, you can pass arguments to Sub and Function procedures.

As mentioned earlier in this article, you must follow the name of the procedure with parentheses if you want to start it from an AppleScript script. If you are passing arguments to a procedure, you place them between the parentheses.

Passing a Single Value to a Sub Procedure:

  1. Open the workbook (Workbook1) that you created earlier in this article, and then activate Module1.

  2. Type the following code in the module:

          Sub Pass_1_Argument(x)
    
             Worksheets("Sheet1").Cells(1,1).Value = x
    
          End Sub
    
    

  3. Activate Sheet1.

  4. On the File menu, click Save.

  5. Leave Microsoft Excel open. Start Script Editor and type the following in a new script window:

          tell application "Microsoft Excel"
    
             set myvalue to 10
             Activate
             Evaluate "Workbook1!Pass_1_Argument(" & myvalue & ")"
          end tell
    
       NOTE: In order to pass a variable to a Visual Basic procedure, you must
       concatenate the variable into the Evaluate or Do Script statement. If
       you want to pass a constant value to the procedure, you can place the
       value between the parentheses without using concatenation. If you
       want to pass a constant value, the Evaluate line becomes the following:
    
          Evaluate "Workbook1!Pass_1_Argument(10)
    
    

  6. On the File menu, click Save.

  7. Select a destination folder, type a name for your script in the Save Script As box, and then click Save.

  8. Run the script.

    The script activates Microsoft Excel and places the value 10 in cell A1 on Sheet1.

Returning a Value from a Procedure to a Script

If you want to return a value from a Visual Basic Sub procedure, you will be limited to a True Boolean value when the subroutine completes successfully. However, if you are calling a function, you can return any numerical, Boolean, or string value back to the AppleScript script. Whether you are calling a Microsoft Excel Sub or Function procedure, the value is returned to a predefined AppleScript variable named "result."

Returning a Value from a Function:

  1. Activate Microsoft Excel, and open the workbook (Workbook1) that you created earlier in this article.

  2. Activate Module1 and type the following code:

          Function Return_Function_Value(x) As Integer
    

             Return_Function_Value = x * 2
    
          End Function
    
    

  3. Leave Microsoft Excel open. Start Script Editor and type the following in a new script window:

          tell application "Microsoft Excel"
    
             Evaluate "Workbook1!Return_Function_Value(10)
             set myvalue to result + 1
             display dialog myvalue
          end tell
    
    

  4. On the File menu, click Save.

  5. Select a destination folder, type a name for your script in the Save Script As box, and then click Save.

  6. Run the script.

    An AppleScript dialog box appears displaying the value 21.

  7. Click OK.

Returning a Value from a Sub Procedure:

  1. Activate Microsoft Excel, and open the workbook (Workbook1) that you created earlier in this article.

  2. Activate Module1, and type the following code:

          Sub Return_Sub_Value()
    
             Sheets("Sheet1").Cells(1, 1).Value = 1
    
          End Sub
    
    

  3. Leave Microsoft Excel open. Start Script Editor, and type the following in a new script window:

          tell application "Microsoft Excel"
    
             Evaluate "Workbook1!Return_Sub_Value()"
             if result then
                display dialog "Success"
             end if
          end tell
    
    

  4. On the File menu, click Save.

  5. Select a destination folder, type a name for your script in the Save Script As box, and then click Save.

  6. Run the script.

    An AppleScript dialog box appears displaying "success".

  7. Click OK.

Error Trapping Values Returned from a Procedure

There are situations in which your Sub or Function procedure fails due to user-intervention or a run-time error. This section discusses ways to communicate back to your AppleScript script that an error occurred in the Visual Basic procedure.

Error Trapping for a Sub Procedure:

Because a Sub procedure cannot explicitly pass a value back to its AppleScript caller, you can work around it by having the procedure store a value in a worksheet cell or a defined name in the workbook. Then, your script can check the current value of the cell or defined name to see if an error occurred. The following example uses a defined name to hold the status of any error in the Sub procedure.

  1. Activate Microsoft Excel, and open the workbook (Workbook1) that you created earlier in this article.

  2. On the Insert menu, point to Name, and then click Define.

  3. Type "myerr" (without the quotation marks) in the Names in Workbook box.

  4. Delete any text in the Refers To box, and then type ="ok" (including the quotation marks) in the Refers To box. Click OK.

  5. Activate Module1, and type the following code:

           Sub Return_Sub_Error()
    
              ThisWorkbook.Names("myerr").RefersTo = "ok"
    
              On Error GoTo handle:
    
              x = MsgBox(Prompt:="Click Yes for an error, otherwise click No" _
                 , Buttons:=vbYesNo)
              If x = vbYes Then
                 Error 1004      ' Generates a run-time error.
              End If
    
              Exit Sub
    
              handle:
              ThisWorkbook.Names("myerr").RefersTo = Err
    
           End Sub
    
    

  6. Leave Microsoft Excel open. Start Script Editor, and type the following in a new script window:

    tell application "Microsoft Excel"

              Activate
              Evaluate "Workbook1!Return_Sub_Error()"
              if not(Evaluate "Workbook1!myerr") = "ok" then
                 set myerr to Evaluate ("Workbook1!myerr")
                 set myerr to myerr as integer
                 display dialog "An error occurred in the macro: " & myerr
              else
                 display dialog "No error."
              end if
           end tell
    
    

  7. On the File menu, click Save.

  8. Select a destination folder, type a name for your script in the Save Script As box, and then click Save.

  9. Run the script.

    The script activates Microsoft Excel and displays a message box.

  10. Click Yes.

    This creates an error condition in the Sub procedure and changes the value of the defined name "myerr." Then, an AppleScript dialog box appears displaying the message "An error occurred in the macro: 1004."

  11. Click OK.

NOTE: If you click No in Step 10, the script displays a dialog box with the message "No error."

Error Trapping for a Function:

Since a function can return a value to its caller, passing this information back to a script is much easier than it is for a Sub procedure.

  1. Activate Microsoft Excel, and open the workbook (Workbook1) that you created earlier in this article.

  2. Activate Module1, and type the following code:

          Function Return_Function_Error () As Integer
    

             On Error GoTo handle:
    
             x = MsgBox(Prompt:="Click Yes for an error, otherwise click No" _
                 , Buttons:=vbYesNo)
             If x = vbYes Then
                Error 1004      ' Generates a run-time error.
             Else
                Return_Function_Error = 0
             End If
    
             Exit Function
    
             handle:
                Return_Function_Error = Err
    
          End Function
    
    

  3. Leave Microsoft Excel open. Start Script Editor, and type the following in a new script window:

          tell application "Microsoft Excel"
    
             Activate
             Evaluate "Workbook1!Return_Function_Error()"
             if not (result = 0) then
                set myerr to result as integer
                display dialog "An error occurred in the macro: " & myerr
             else
                display dialog "No error"
             end if
    
          end tell
    
    

  4. On the File menu, click Save.

  5. Select a destination folder, type a name for your script in the Save Script As box, and then click Save.

  6. Run the script.

    The script activates Microsoft Excel and displays a message box.

  7. Click Yes.

    This creates an error condition in the function, and the function value will be set to the value of the error. Then, an AppleScript dialog box displays the message "An error occurred in the macro: 1004."

  8. Click OK.

NOTE: If you click No in step 7, the script displays a dialog box with the message "No error."


Additional query words: mac vbe vba
Keywords : kbcode kbprg xlvbahowto
Version : MACINTOSH:5.0,5.0a
Platform : MACINTOSH
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 4, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.