Bring VBA on Excel for Mac to parity with Excel for Windows
Bring VBA on Excel for Mac to parity with Excel for Windows.
Currently, there are too many missing VBA elements missing. It is a major problem for those of us with mixed environments. Many things that worked under Excel 2011 no longer work in 2016.
Why is the not being addressed?
Hi everyone. I’m marking this one as completed, even though we realize there are still some issues with VBA on Mac, and we will continue to work on those as they’re identified.
We’ve been working to improve VBA support on Mac over time, and hopefully you’ve noticed some of the improvements. For example, there was a previous comment about Application.MacroOptions, which is now working so that you can see user defined functions in the formula builder.
Since this suggestion is very broad, it’s not possible to fix everything at once and completely. If you run into specific issues or errors with VBA, it’s best that you report it to us by clicking the feedback button in Excel and sending a frown with the details of the problem
Steve K – Microsoft Excel
I tried to attack the problem in a logical way, by looking for a book or language reference document on VBA for Excel running on MacOS. But among the numerous publications on VBA for Windows, I couldn't find any. If somebody of you would be lucky enough in that finding, all of you could be sure, about any VBA feature, whether it's existing or not for MacOS.
agree. And why Screen Updating is no longer possible in the early 2020 Excel update?? It worked fine before and now it is gone again. Also, stopping a running macro with "esc" should be there and for some reasons not enabled. This is really lacking compared to the Windows Excel version
Mike Beal commented
VBA application macros do not work. I want to be able to use my macros to interact with browsers, email, and other reports. This hamstrung BS is so frustrating. Please create a Mac library or at least have some sort of compiler that can translate Windows macros to function on Mac OS.
Mark Eisler commented
Is it the case that Application.MacroOptions is no longer supported in Excel for Mac (version 16.26, 2019)? As described by Microsoft (1) it should provide descriptions to to display a user-defined function (UDF) in a built-in or new category within the Insert Function dialog box, a description and status bar text.
This seems to be be an old chestnut, as discussed in various online postings, e.g. most usefully in (2) and (3) below.
I have tried doing this until blue in the face, using the simplest imaginable UDF that adds two numbers together. Application.MacroOptions doesn’t seem to work at all under any circumstances in Excel for Mac. I have tried running the code when Excel starts by calling a sub from the Auto_Open() in my PersonalMacroWorkbook. The code fails, error 2004. I’ve tried running it by calling the same code after excel opens, either in the PersonalMacroWorkbook, or in a .xlsm workbook other than the one containing the VBA code for the UDF to be described; in both cases, again the code fails with the same error.
Finally, I’ve tried running the Application.MacroOptions code in the workbook containing the UDF to be described, being sure to do so before first use of my UDF. Here, the code runs with no error - I can step though it to confirm that, BUT, it has absolutely no effect. If the Formula Builder is already open when I type the name of the UDF in a cell, I do see the name of my UDF and its result in the Formula Builder. But that happens anyway, regardless of whether or not code containing Application.MacroOptions for the UDF has been run. And absolutely nothing specified in Application.MacroOptions appears in the Formula Builder or elsewhere; my UDF doesn’t appear under any category regardless what numerical or string value is given for the Category argument, and nothing appears in the status bar or in the Formula Builder under Fx or Syntax where info is shown for the built-in Excel functions.
So should I conclude that Application.MacroOptions is no longer supported in Excel for Mac? Or have I missed something and more undocumented steps are required to make it work? It would be mighty helpful for Microsoft to clarify this so that Excel for Mac users need not to waste hours trying to get this to work!
Dim ArgDesc(1 To 2) As String
ArgDesc(1) = "Val1: first number to be multiplied"
ArgDesc(2) = "Val2: second number to be multiplied [optional]; if missing, Val1 is also used for Val2"
' This to confirm the code has run
MsgBox "Running DescribeUDF() in " & ActiveWorkbook.Name
Description:="Add two numbers", _
StatusBar:="TestAdd(Val1 As Variant, Optional Val2 As Variant) As Variant"
Public Function TestAdd(Val1 As Variant, Optional Val2 As Variant) As Variant
If IsMissing(Val2) Then
Val2 = Val1
TestAdd = Val1 + Val2
‘Apparently’ relative reference has been fixed, although, I’ve barely opened XL on my Mac because it’s almost useless. It definitely reeks of some kind of hillbilly warfare between Microsoft & Apple.
I agree these are important features that are needed in excel. I was forced to update in order to use it. These are extremely important features!!!!
At least get the assignment of macros to keystroke combinations into working conditions for BOTH EXCEL AND WORD. A VBA macro doesn't help too much if you cannot get it to work faster than doing the same stuff by hand.
Es muy importante que de una vez por todas hagan 100% compatibles las versiones de Excel de Windows y Mac, porque casi todos tenemos Mac en casa y Windows en el trabajo o viceversa.
Por ejemplo lo comentado aquí: el no poder insertar un UserForm provoca que macros viejas elaboradas en Windows, que utilizaban UserForm´s fallen y no abran en Mac 2016. Así mismo de frustrante es intentar programar eventos de Libro u hoja de trabajo en VBA ya que al invocar los eventos dentro del menú de eventos del Libro u de las Hojas de Trabajo, VBA genera un error y no pone los encabezados del evento requerido, teniendo que investigar en un texto y tener que capturar todo a mano, perdiendo la esencia VISUAL de VBA...