Microsoft KB Archive/184440

From BetaArchive Wiki

Article ID: 184440

Article Last Modified on 1/22/2007


APPLIES TO

  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 for Macintosh


This article was previously published under Q184440

SUMMARY

This article includes sample AppleScript scripts that demonstrate how to use AppleScript to call Microsoft Visual Basic for Applications Sub procedures and Function procedures in Microsoft Excel.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements.
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 the Evaluate command or the Do Script command, followed by the appropriate syntax for specifying the name of the procedure (and possible arguments). Because there is not a discernible difference between the Evaluate command and the Do Script command, 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 Starts a Microsoft Excel Macro

Creating the Macro

  1. Start Microsoft Excel.
  2. Start the Visual Basic Editor (press OPTION+F11), and click Module on the Insert menu.

  3. Type the following in the module:

    Sub Test_Recording()
   MsgBox "This is an Excel macro."
End Sub
  4. On the File menu, click Close and Return to Microsoft Excel.
  5. On the File menu, click Save.
  6. Save the workbook and name it Workbook1.
  7. Click Hide Microsoft Excel on the Application menu.

Recording the Script

To record the script, follow these steps:

  1. Start Script Editor.
  2. On the Controls menu, click Record.
  3. On the Application menu, click Microsoft Excel.
  4. Point to Macro on the Tools menu, and then click Macros.
  5. In the list of macros, click Test_Recording, and then click Run.

    A message box displays the message "This is an Excel macro."
  6. Click OK.
  7. On the Application menu, click Script Editor.

  8. On the Controls menu, click Stop.

    The recorded script looks similar to 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, the name of the procedure must be followed by parentheses.

  9. On the File menu, click Save.
  10. 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

To play the script, follow these steps:

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


The Test_Recording macro is run, and a message box displays the "This is an Excel macro" message.

  1. Click OK.
  2. On the Application menu, click Script Editor.

Passing Arguments To and From a Visual Basic Procedure

The following examples passes arguments from an AppleScript script to a Visual Basic for Applications Sub or Function procedure.

As noted earlier in this article, the name of the procedure must be followed by 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

To pass a value, follow these steps:

  1. Open the workbook (Workbook1) that you created earlier and then start the Visual Basic Editor (press OPTION+F11).

  2. Type the following code in the module:

    Sub Pass_1_Argument(x As Variant)

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

End Sub
  3. On the File menu, click Close and Return to Microsoft Excel.
  4. On the File menu, click Save.
  5. On the Application menu, click Hide Microsoft Excel.

  6. 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)
  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. On the Controls menu, click Run.

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

Returning a Value from a Procedure to a Script

A Visual Basic Sub procedure returns a value of 0 when it runs successfully from an AppleScript script.

If you are calling a Function procedure, you can return any numerical, Boolean, or string value back to the AppleScript script. Whether you are calling a Sub or Function procedure, the value is returned to a predefined AppleScript variable named "result."

Returning a Value from a Function

To return a value, follow these steps:

  1. Switch to Microsoft Excel and open the workbook (Workbook1) that you created earlier.

  2. Start Visual Basic Editor (press OPTION+F11) and type the following code in the module:

    Function Return_Function_Value(x As Integer) As Integer

    Return_Function_Value = x * 2

End Function
  3. On the Application menu, click Hide Microsoft Excel.

  4. 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
  5. On the File menu, click Save.
  6. Select a destination folder, type a name for your script in the Save Script As box, and then click Save.
  7. Click Run on the Controls menu.

    An AppleScript dialog box displays the value 21.
  8. Click OK.

Returning a Value from a Sub Procedure

To return a value from a Sub procedure, follow these steps:

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

  2. Start the Visual Basic Editor and type the following code in the module:

          Sub Return_Sub_Value()

         Sheets("Sheet1").Cells(1, 1).Value = 1

      End Sub
  3. On the Application menu, click Hide Microsoft Excel.

  4. Start Script Editor, and type the following in a new script window:

    tell application "Microsoft Excel"
   Evaluate "Workbook1!Return_Sub_Value()"
   display dialog result as integer
end tell
  5. On the File menu, click Save.
  6. Select a destination folder, type a name for your script in the Save Script As box, and then click Save.
  7. On the Controls menu, click Run.

    An AppleScript dialog box displays the value 0.
  8. Click OK.

Error Trapping Values Returned from a Procedure

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

Error Trapping for a Sub Procedure

A Visual Basic Sub procedure cannot explicitly pass a value back to its AppleScript caller. However, a Sub procedure can store a value in a worksheet cell, or it can store a defined name in the workbook. Then, your AppleScript script can check the current value of the cell or the defined name to see if an error has occurred. The following example uses a defined name to hold the status of any error in the Sub procedure:

  1. Start Microsoft Excel and open the workbook (Workbook1) that you created earlier.
  2. On the Insert menu, point to Name, and then click Define.
  3. Type myerr in the Names in workbook box.
  4. Delete any text in the Refers To box, and then type = "ok" in the Refers To box. Click OK.

  5. Start Visual Basic Editor and type the following code in the module:

    Sub Return_Sub_Error()

   Dim x as Integer

   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. On the Application menu, click Hide Microsoft Excel.

  7. 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
  8. On the File menu, click Save.
  9. Select a destination folder, type a name for your script in the Save Script As box, and then click Save.
  10. On the Controls menu, click Run.

    The script activates Microsoft Excel and displays a message box.
  11. 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 displays the following message:

    "An error occurred in the macro: 1004."

  12. 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

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

To trap errors for a function, follow these steps:

  1. Switch to Microsoft Excel and open the workbook (Workbook1) that you created earlier.

  2. Start Visual Basic Editor and type the following code in the module:

    Function Return_Function_Error () As Integer

   Dim x 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. On the Application menu, click Hide Microsoft Excel.

  4. 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
  5. On the File menu, click Save.
  6. Select a destination folder, type a name for your script in the Save Script As box, and then click Save.
  7. On the Controls menu, click Run.

    The script activates Microsoft Excel and displays a message box.
  8. Click Yes.

    This creates an error condition in the function, and the function value is set to the value of the error. Then, an AppleScript dialog box displays the following message:

    "An error occurred in the macro: 1004."

  9. Click OK.

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

REFERENCES

For additional information about using AppleScript scripts with Excel, click the article numbers below to view the articles in the Microsoft Knowledge Base:

113891 MXL: Using AppleScript with Microsoft Excel


180613 XL98: AppleScript Error Selecting Range on Another Worksheet


177047 XL98: Recorded AppleScript Cannot Create Embedded Chart


176413 XL98: Error Message Running AppleScript That Clears Print Area


176412 XL98: Drawing Tools Not Recorded into AppleScript Script



Additional query words: XL98

Keywords: kb3rdparty kbdtacode kbfaq kbhowto kbprogramming KB184440



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/184440&oldid=379005’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki