Excel Runtime Error Excel 2016 For Mac

Posted on by admin

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.

I am trying to make a macro that prints the active sheet in Excel to a PDF, then attaches that PDF to an email. It works perfectly on PC, but errors out on my coworkers Mac. From my googling, it seems like file dialogue commands aren't supported on Mac but I can't figure out how to adjust the macro to use a different, mac supported command. Open the dropdown menu next to the Save as type: field and click on Excel Template (.xlt) if you are using Excel 2003 or earlier, or Excel Template (.xltx) if you are using Excel 2007 or later to select it. How to fix run time error in excel VBA Code Sample here common excel interv.

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 ?

For

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.

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.

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

Download

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.

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.

Well, we all know how important Excel file is in our personal as well professional life. This is used for performing various tasks like managing, organizing the crucial data and much more. And Microsoft Excel different versions provide advanced features as well greater ease of use.

So, today in this article we are going to discuss the latest Excel 2016 file and know how to fix Excel 2016 errors faced by the users.

Excel 2016 is a Microsoft’s venerable Excel series of spreadsheet software. This latest Excel version is smooth and modernized, with all its previously existing features and few more advanced features that provide greater ease of use. You can learn the very new Excel 2016 features: Here

However, despite its advancement and flexibility, this is not free from errors. Like other Excel 2010, 2013 and 2007 versions many users are found encountering the errors in Excel 2016 files.

Here, we tried our best to put together the common Excel 2016 errors that irritate the users and hinder them from using the latest Excel version.

How to Fix Excel 2016 Errors:

Excel is a crucial file and this is the reason it easily gets corrupted, damaged and start showing errors. We have listed the 9 common Excel 2016 errors with the possible workarounds to get rid of them.

Here comes the number 1…

1. Excel 2016 Is Not Responding Error

Excel is a crucial program and this needs to work smoothly, to keep the data safe. But many times, users face the situation when Excel starts crashing, freezing and displays – “Excel is not Responding” or “Excel has stopped working” error message.

Here know how to fix it:

To fix Excel 2016 Is Not Responding error you need to perform various workarounds, such as:

  1. Run Excel in Safe Mode: To do so, click Windows + R > open Run Box > type excel.exe/ safe and click OK.
  2. Perform Latest Updates: In “Control Panel” under “System and Security” and select the “Start Maintenance” option as auto mode.
  3. Disable COM Add-ins: Select File>Options>Add-ins and choose COM Add-ins > press Go button > delete the unwanted Add-ins and reopen the MS Excel.

Helpful Article: FIX: Crashing, Freezing, Not Responding or Stop Working Issues in MS Excel 2013 and 2016

Excel Runtime 13

2. The File is Corrupt and Cannot be Opened error in Excel 2016

This is random error encountered by many users when they to open the saved Excel 2016 file.

Well, this error is not limited to Excel 2016 this is also frequently seen in Excel 2010.

This is the irritating error appears every time when you try to access your saved excel file. Moreover, there is no any specific reason behind getting the error but there are possible fixes that help you to fix Excel 2016 error.

To Fix the Error Visit: Fix Excel Error “The file is corrupt and cannot be opened”

3. Formula Errors in Excel 2016

Formulas errors are the common problem in Excel files. Many users are found reporting in the forum thread that they are encountering Excel 2016 formula not working, error message:

Let describe it best with the user experience:

Hi everyone,

When entering a formula, this error keeps coming up. I have checked that I am entering the code correctly and when I send this document to colleagues and they enter the same formula it works without this message popping up. What am I doing wrong?? HELP!

Original post from Microsoft Community:

Excel 2016 provides several ways to fix errors in formulas. This can be corrected at one time, run error checker, trace cell reference, and here know how to do so:

When Excel detects the formula that you entered is incorrect, a small green triangle appears in the upper left corner of the cell, where the formula is entered. But if the error uncommon, then an error message, a cryptic three-or-four letter show preceded by the sign (#) in the cell.

To fix it:

Select the green triangle cell and click the Error button. This small button appears beside a cell with a formula error, click the icon and the drop-down list offers the prospect for correcting formula errors and find out more about them.

  • Run the Error Checker:

Another way to tackle formula errors is run error checker. To run the error checker:

Run the error checker, go to Formulas tab > click Error checking button (you might need to click the Formula Auditing button first, depending on the size of your screen).

Helpful Article: 8 Common Excel Error Messages that Irritates You The Most

4. Excel 2016 “Could not load some objects because they are not available on this machine.” error

This is another irritating Excel 2016 error, which irritates the users.

To fix this perform the given methods:

  • To stop the code, click the square stop button.
  • Verify if there are any MISSING references (‘Tools>References’) and fix them.
  • Also, check a reference for “Microsoft Windows Common Controls 6.0 (SP6)”? (that’s where the progress bar lives). If not, add it (C:WindowsSystem32MSCOMCTL.OCX).
  • Try running the Office installation to detect and repair errors.

Try the given solutions to fix Excel 2016 error.

5. Excel 2016 Has Stopped Working on Windows 10

This is the irritating error that is commonly seen in the Windows 10 OS. The Windows 10 users when try to use Excel 2016 file it starts displaying – “Excel 2016 Has Stopped Working on Windows 10” error message.

However to know the complete information and working solution visit this:

Read: Repair MS Excel 2016 Has Stopped Working On Windows 10

6. Data Entry Errors in Excel 2016

This is a common mistake that later becomes a big hurdle for the user. While entering vast quantities of data in Excel 2016, the little typos can easily creep into your work that later considered as a data entry error in Excel 2016.

To fix this you can make use of the AutoCorrect feature in Excel 2016:

The AutoCorrect feature is a stroke of luck for those who often make the same stupid typing errors. With this feature, you can alert Excel 2016 to own particular typing mistakes and tell the program how this should automatically fix them for you.

For adding the replacements, follow the given steps:

  • Select File > Options > Proofing or hit Alt+FTP and click the AutoCorrect Options button or else press Alt+A.
  • Now the AutoCorrect dialog box appears.
  • On the AutoCorrect tab > enter the typo or abbreviation in the Replace text box.
  • And, enter the correction or full form in the With text box.
  • Then, click the Add button or hit Enter to add the new typo or abbreviation to the AutoCorrect list.
  • Lastly, click the OK button to close the AutoCorrect dialog box.

Hence, in this way you can fix the data entry error in Excel 2016.

7. “Problem Sending The Command To The Program” error On Windows 8/7

This error is not particularly seen in Excel 2016, other users of Excel 2013 and 2010 are also facing the particular error in Windows 8/7.

The error indicated that some process running inside Excel will not let Excel close. And commonly appears while trying to open web links from an email program, internet links, and it pop-up with this error message.

To fix the error, here follow the complete manual solution:

Visit: Fix Excel Error “Problem Sending The Command To The Program” On Windows 8/7

8. Excel 2016 VBA Run-time error ‘1004’

Runtime errors are the common problem and this crop up anytime while using Excel file. The Runtime error 1004 in Excel 2016 generally appears while generating Macro in Excel document and the users are totally helpless as they cannot perform a single action.

Let define it best with the user experience

Can anybody help me debug this line in the Macro I’m trying to do please,

Sub SaveWithNewName()

Dim NewFN AsVariant

ActiveSheet.Copy

NewFN = “Users/gozmani/Desktop/IG Networks LTD/Inv” & Range(“F4”).Value & “.xlsx”

Excel Runtime Error Excel 2016 For Mac Tutorial

ActiveWorkbook.SaveAs NewFN, FileFormat:=52

ActiveWorkbook.Close

NextInvoice

EndSub

I tried different file format codes but can’t make it work, I get the Error 1004 message “Your changes could not be saved to ‘Inv2091.xlsx’, but were saved to a temporary document….”

Post from Microsoft Community

To fix the runtime error 1004 in Excel 2016 here follow the complete information and fixes:

Visit: How to Fix Runtime Error 1004 in Excel

9. Errors Detected while Trying to Save Excel 2016 file

This is the last error in our list that irritated the Excel 2016 users.

Excel Runtime Error Excel 2016 For Mac

In Excel 2016 when the users try to save a Microsoft Excel workbook it starts displaying the error message – “Errors Detected while Trying to Save Excel 2016 file”.

Well, there is no any definite reason behind getting the error, however, to fix Excel 2016error here follow some possible workarounds:

  • Save the file as a different Excel file type.
  • Or else try to save the workbook to another location.

Hope this might help you to resolve Excel 2016 error.

Excel Runtime Error Excel 2016 For Mac Data Entry Form

These are the 9 common Excel 2016 errors with the possible working solutions. Hope the given solutions will help you to get rid of the error but if still, you are not able to fix the errors, then it is recommended to make use of the automatic MS Excel Repair Tool.

Automatic Solution: MS Excel Repair Tool:

Make use of the professional recommended MS Excel Repair Toolto repair corrupt, damaged as well as errors in Excel file.This tool allows to easily restore all corrupt excel file including the charts, worksheet properties cell comments, and other important data. With the help of this, you can fix all sort of issues, corruption, errors in Excel workbooks. This is a unique tool to repair multiple excel files at one repair cycle and recovers the entire data in a preferred location. It is easy to use and compatible with both Windows as well as Mac operating system. This supports the entire Excel versions and the demo version is free.

Steps to Utilize MS Excel Repair Tool:

Conclusion:

Well, I have tried my best to put together the common Excel 2016 errors and provide their fixes.

However, if I have missed out any error then please let me know.

Excel Runtime Error Excel 2016 For Mac Book

Also, make use of the given fixes if you are dealing with any given issues or errors in Excel 2016.

Additionally, always keep the backup of the important files to overcome the situation.

If, in case you have any additional questions concerning the ones presented, do tell us in the comments section below or you can also visit our Repair MS Excel Ask Question

Excel Runtime 2016

Good Luck….


Margret

Margret Arthur is an entrepreneur & content marketing expert. She writes tech blogs and expertise on MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in easy to understand language is very impressive. When not writing, she loves unplanned travels.