Recently I bought a Mac and installed 2016 Microsoft excel for Mac. I am used to create a lot of applications for my job using user forms, but when I looked for it in my Mac I realized that there is no such tool box and a button where I can generate a new User form. There is the instruction code, and my previous files with user forms worked. So, to fix the Excel file error, simply uninstall the problematic apps and programs. First, go to the Task Manager and stop the running programs. Then in the start menu select Control Panel. In the Control Panel choose Add or Remove Program.

1. Excel Runtime Error Excel 2016 For Mac Data Entry Form
2. Microsoft Excel Runtime Error 9
3. Excel Runtime Error Excel 2016 For Mac 16 38
4. Mac Excel Runtime Error 1004

Important

Note that Excel custom functions are available on the following platforms.

• Office on Windows
• Office on Mac
• Office on the web

Excel custom functions are currently not supported on iPad or in one-time purchase versions of Office 2019 or earlier.

When running Excel on Windows or Mac, your add-in will run code for ribbon buttons, custom functions, and the task pane in separate JavaScript runtime environments. This creates limitations such as not being able to easily share global data, and not having access to all CORS functionality from a custom function.

However, you can configure your Excel add-in to share code in a shared JavaScript runtime. This enables better coordination across your add-in and access to the DOM and CORS from all parts of your add-in. It also enables you to run code when the document opens, or to run code while the task pane is closed. To configure your add-in to use a shared runtime, follow the instructions in this article.

Create the add-in project

If you are starting a new project, follow these steps to use the Yeoman generator to create an Excel add-in project. Run the following command and then answer the prompts with the following answers:

• Choose a project type: Excel Custom Functions Add-in project
• Choose a script type: JavaScript
• What do you want to name your add-in? My Office Add-in

After you complete the wizard, the generator creates the project and installs supporting Node components.

Configure the manifest

Follow these steps for a new or existing project to configure it to use a shared runtime.

1. Start Visual Studio Code and open the My Office Add-in project.

2. Open the manifest.xml file.

3. Find the <VersionOverrides> section, and add the following <Runtimes> section. The lifetime needs to be long so that the custom functions can still work even when the task pane is closed. The resid is ContosoAddin.Url which references a string in the resources section later. You can use any resid value you want, but it should match the resid of the other elements in your add-in elements.

4. In the <Page> element, change the source location from Functions.Page.Url to ContosoAddin.Url. This resid matches the <Runtime> resid element. Note that if you don't have custom functions, you will not have a Page entry and can skip this step.

5. In the <DesktopFormFactor> section, change the FunctionFile from Commands.Url to use ContosoAddin.Url. Note that if you don't have action commands, you won't have a FunctionFile entry, and can skip this step.

6. In the <Action> section, change the source location from Taskpane.Url to ContosoAddin.Url. Note that if you don't have a task pane, you won't have a ShowTaskpane action, and can skip this step.

7. Add a new Url id for ContosoAddin.Url that points to taskpane.html.

8. Make sure the taskpane.html has a <script> tag that references the dist/functions.js file. The following is an example.

   Note

   If the add-in uses Webpack and the HtmlWebpackPlugin to insert script tags, as add-ins created by the Yeoman generator do (see Create the add-in project above), then you must ensure that the functions.js module is included in the chunks array as in the following example.

9. Save your changes and rebuild the project.

Runtime lifetime

Excel Runtime Error Excel 2016 For Mac Data Entry Form

When you add the Runtime element, you also specify a lifetime with a value of long or short. Set this value to long to take advantage of features such as starting your add-in when the document opens, continuing to run code after the task pane is closed, or using CORS and DOM from custom functions.

Note

The default lifetime value is short, but we recommend using long in Excel add-ins. If you set your runtime to short in this example, your Excel add-in will start when one of your ribbon buttons is pressed, but it may shut down after your ribbon handler is done running. Similarly your add-in will start when the task pane is opened, but it may shut down when the task pane is closed.

Note

If your add-in includes the Runtimes element in the manifest (required for a shared runtime), it uses Internet Explorer 11 regardless of the Windows or Microsoft 365 version. For more information, see Runtimes.

Multiple task panes

Don't design your add-in to use multiple task panes if you are planning to use a shared runtime. A shared runtime only supports the use of one task pane. Note that any task pane without a <TaskpaneID> is considered a different task pane.

Next steps

• Read the Call Excel APIs from a custom function article for details on using the Excel JavaScript APIs and custom Excel functions in a shared runtime.
• Explore the patterns-and-practices sample Manage ribbon and task pane UI, and run code on doc open to see a larger example of the shared JavaScript runtime in action.
• Read the Custom keyboard shortcuts in Office Add-ins for information about adding custom keyboard shortcuts to your project.

See also

AppleScriptTask in Mac Office 2016 or higher

In Mac Office there are many problems with the built-in VBA commands. VBA developers responded by using AppleScript in many situations to work around the problems. They also used AppleScript to do things that are not possible with VBA code; for example to email using VBA code.

In Office 2016 and higher, we need to use a new method and a new approach explained below.

Example for Excel 2011

In Office 2011 we use the built-in MacScript function to run a script that we build up as a string in the VBA code. See the code example below.

Copy the test macro and the function below into a normal module of your workbook. Change the file path and name in the macro TestMacro to point to a file on your Mac to test.

When you run the macro named TestMacro it will test if the file :
Macintosh HD:Users:RDB:Desktop:MacTestFile.xlsm
exists on your Mac and display a msgbox showing True or False

Note: You can also use : FileName = '/Users/RDB/Desktop/MacTestFile.xlsm'

Excel 2016 or higher for the Mac

If you test the code that is working correctly in Excel 2011, in Excel 2016 and higher it gives a run-time error 5; but if you test the script string that the VBA code created in the Script Editor, the script works correctly.

What’s the problem here?

The legacy 'MacScript' VB Command is severely limited by Apple’s sandbox requirements: it will not work correctly in most situations in Office 2016 and higher.

Instead, Microsoft added a new VB command 'AppleScriptTask' that accesses and runs an AppleScript file located outside the sandboxed app. This new approach is not as convenient: with the MacScript function you could have the script in the file itself, while with the AppleScriptTask method you need to distribute an extra file containing the script, and it must be placed in the specified location on the user’s system to have permission to run. This requires some user interaction the first time.

1) Test the AppleScript in the Script Editor ?

First we open the Script Editor on your Mac.

1. Click on the Spotlight icon in the top right corner of your Mac.
2. Enter 'Script Editor' to find and open this program.
3. Click the New Document button, and we are ready to start.

Tip: Right click on the Script Editor icon in the dock and choose Options>Keep in Dock so it is easy the next time to open it when you need it.

The script you want the VBA code to run looks like this now in the script editor:

tell application 'System Events' to return (exists disk item 'Macintosh HD:Users:RDB:Desktop:MacTestFile.xlsm') and class of disk item 'Macintosh HD:Users:RDB:Desktop:MacTestFile.xlsm' = file

1. Paste that inside the editor
2. Press the Run button
3. Observe the Result area at the bottom
4. You see the result (True or False)

When your script works correctly in the script editor you have proved that nothing is wrong with the script. Now we go to step 2 to make the script ready for using it with AppleScriptTask.

2) Add the script inside a handler and test it

Replace the script in the Script Editor with the script below.

And for testing only we copy this line at the top

ExistsFile('/Users/RDB/Desktop/MacTestFile.xlsm')

You can use this line instead if you want to use the colon separator

ExistsFile('Macintosh HD:Users:RDB:Desktop:MacTestFile.xlsm')

So it looks like this :

1. Now save the file as MyFileTest.scpt on your Desktop.
2. Use (File>Save)in the Script Editor Menu bar in the top left corner of your Mac window

• The Script Editor must be active to see the Script Editor menu bar.

You see that I placed the code inside a handler named ExistsFile which takes a parameter string of filepath. The name of the handler and parameter string is your choice. You see also in the script line that I replaced both path/file name strings with the variable filepath. This works the same as in the VBA code example for Excel 2011 where we have a string named FileName, which we created in the macro.

Before we try to run the script with AppleScriptTask in VBA we first test the handler inside the Script Editor. The first line in the script is there only for testing.

Press the Run button, and the script test if the file exists on your Mac. The line above the ExistsFile handler provides the filename string to the ExistsFile handler, to enable you to test the script before we take the next step of calling it from VBA.

Before we go to the next step remove the script line above the handler or make it a comment, so the script does not use it. You do this by adding two hyphens before the line so it looks like this:

--ExistsFile('/Users/RDB/Desktop/MacTestFile.xlsm')

Click on the Run button and you see that nothing happens, because the handler has no filename string to test. But it test and indents the changes in the script. It is important before you close a file after you make changes to press the Run button.

Close the script file now and you will notice that it has automatically saved your changes.

3) Where to place the script file for using it with AppleScriptTask

Now the script file is ready and tested we must copy it into the correct location. Follow the steps below to copy and paste it into this exact location.

1. Open a Finder Window
2. Hold the Alt key and click Go in the Finder menu bar
3. Click Library
4. Click Application Scripts (if it exists; if not create this folder)
5. Click com.microsoft.Excel if it exists; if not create this folder (note: Capital letter E)
6. Copy MyFileTest.scpt to the com.microsoft.Excel folder.

Note: If you want to use the example in Word you must add/use the com.microsoft.Word folder, each Office app have its own folder. Unfortunately there is no folder for all Office programs.

This are three ways to easily open the com.microsoft.Excel folder manual :

• Add it to your Favorites in Finder by dragging it to it while holding the Alt key down.
• Add it to your Favorites in Finder with the shortcut : cmd Ctrl T
• Drag the folder to the Desktop with the CMD and Alt key down. You now have a link to the folder on your desktop so it is easy to find it and open it in the future.

Note : Adding the folder to your Favorites is my favorite because you see the folder in your open and save dialogs in Excel.

Or use this script one time to create a folder for your Office files and create shortcuts to a few important folders, check out this page : Setup your Mac for Mac Office 2016 or higher

4) Use the script we create in VBA with AppleScriptTask

When you use AppleScriptTask the third argument is a parameter string that you use to give information to the handler. In the example on this page this must be the file path and name of the file that we want to test for.

Microsoft Excel Runtime Error 9

This is the code line that you use in your VBA code: You see that there are three arguments:

1. The script file name
2. The name of the handler
3. The file name you want to check

RunMyScript = AppleScriptTask('MyFileTest.scpt', 'ExistsFile', '/Users/RDB/Desktop/MacTestFile.xlsm')

So your VBA macro now looks like this :

You can also add code to your workbook that check if the scpt file is in the correct location, copy the function below in the same module as your macro :

You can add this to your macro to stop it when the scpt file is not in the correct location

More information

More than one handler in your script file

You can have more than one handler in the scriptfile; in the screenshot below I have also added a handler to test whether a nominated folder exists on your Mac.

Calling the folder test in VBA looks like this:

RunMyScript = AppleScriptTask('MyFileTest.scpt', 'ExistsFolder', '/Users/RDB/Desktop/YourFolder/')

Use string wih more than one parameter in AppleScriptTask

AppleScriptTask can accept only one parameter string, but I found a workaround. See how I use SplitString in the applescript below to use more parameters.

More applescript examples that you can use with AppleScriptTask

Below you find some example scrips that you can copy in a scpt file and run it with VBA. I call the scpt file FileFolder.scpt in the macros below. So copy the scrips in a file FileFolder.scpt and copy it in the correct location on your Mac. You see that the check file and folder scripts from above are also in the scrips below.

Excel Runtime Error Excel 2016 For Mac 16 38

And below you find the VBA macros to call the scrips above.

Create SCPT files with VBA code and copy it in the com.microsoft.Excel folder

It is possible to create or update scpt files only with VBA code, but before you can do this you must do some things manual first because Apple not allow you to create the path with VBA code. See point 3 above how to create the path below.

Mac Excel Runtime Error 1004

Library/Application Scripts/com.microsoft.Excel/

Then copy the MakeSCPTFile.scpt file from the download above inside the com.microsoft.Excel folder.

You must do this only one time and after that you can do everything with VBA code.

Copy the excel workbook on your desktop and test the code and see if it create a new scpt file inside the com.microsoft.Excel folder for you, you see that you create the script also in the VBA macro. If you change the string in the macro and run the code again it will overwrite the existing scpt file, this way you can update the scpt file.