Problem with OnAction in macro running on a Mac

[BillHamilton]

I raised this question on the MrExcel forum almost two years ago with no resolution forthcoming, so I think it's reasonable to post it again here without being accused of cross-posting. The MrExcel url is https://www.mrexcel.com/board/threads/problem-with-onaction-in-macro-running-on-a-mac.1101630/?highlight=onaction. There were a few replies but nothing came of them.

So, here goes again. I hope someone here can help.

I have a macro which runs under Windows perfectly well. I'm having to convert the system it runs in to run on a Mac, and it fails at a very frustrating point. It's to do with checking/reassigning the macros that are invoked by clicking on shapes that might be on any sheet in the workbook. These shapes include Ovals and Buttons (neither are ActiveX).

The failure on the Mac happens when assigning the existing OnAction macro name to a variable in order to check what it is. The failure occurs with the first Button on the first sheet, after having checked and ignored a couple of TextBoxes that have no macro assigned and no error is given with the OnAction assignment. The error is

Run-time error '1004': Method 'OnAction' of object 'Shape' failed.

Here's the code. (The basic functionality was obtained from https://www.thespreadsheetguru.com/blog/correcting-shape-assigned-macro-links-after-copying-worksheet-vba, althogh I tweaked it a little).



Sub ResetMacrosTest()
'PURPOSE: Remove any external workbook references from all shapes triggering macros in given workbook
'Source: www.TheSpreadsheetGuru.com
Dim stwsSht As String, stCtrl As String, stMacroLink As String, stNewLink As String
Dim vSplitLink As Variant
Dim wsSht As Worksheet, sShp As Shape
Application.ScreenUpdating = False
wbWork.Activate ' this w/b has already been opened
' Loop through each shape in each worksheet
For Each wsSht In wbWork.Worksheets
stwsSht = wsSht.Name
For Each sShp In wsSht.Shapes
stCtrl = sShp.Name ' (this is only here so I can see in the debugger which shape is being processed)

stMacroLink = sShp.OnAction '<<<<<<<<<<<< On a Mac, this always fails on first Button on first sheet.

' Determine if shape was linking to a macro and if so ensure the pathname is removed from its location .
If stMacroLink <> "" And InStr(stMacroLink, "!") <> 0 Then
vSplitLink = Split(stMacroLink, "!") ' vSplitLink(0) is the w/b (path&)name, vSplitLink(1) is the macro name.
If vSplitLink(0) <> wbWork.Name Then ' this basically removes the full pathname from the link, if present.
stNewLink = wbWork.Name & "!" & vSplitLink(1)
sShp.OnAction = stNewLink ' Apply New Link
End If
End If
Next sShp
Next wsSht
End Sub

The error occurs at the point indicated with the <<<<<<<<<'s. I have also discovered that the error occurs with any reference to 'sShp.OnAction', even a Debug.Print.

I haven't a clue where to go with this one, so any advice is welcome. I have encountered a few differences between the Windows and Mac VBAs during this conversion exercise, mainly in the Shapes department. Maybe this is another, but I can't find the workaround.

(Using a Macbook Pro, OS10.15.7 Catalina with Office for Mac 2019. This runs perfectly under Windows 10, Office 365.)

[SamT]

This is just basic T/S. I don't know MACs
Try
stMacroLink = Shapes(sShp).OnAction

stMacroLink = Shapes(sShp.Name).OnAction

stMacroLink = Shapes(1).OnAction

Also try
Dim soShp As ShapeOval
'dim srShp as ShapeRectangle 'There is no ShapeButton in MAC


Sub A_Button_Is_Which_Shape()
'Won't work for FreeForm Drawings
Dim X, Y

Set X = Shapes(n) 'Edit to suit where n is a button shape index number on the sheet
Y = X.AutoShapeType
End sub

I still don't know MACs, but you might have to redraw the Buttons as Rectangles, maybe with Shadows.

[BillHamilton]

Thanks for the ideas. I'll give thm a try today.

[BillHamilton]

The first three (all with stMacroLink = Shapes(something).OnAction) failed with a Compile error on the 'Shapes' word - Sub or function not defined. Same in the Sub to find the shape type.

'Dim soShp as ShapeOval' also gave a compile error - User-defined type not defined.

Same results under Windows.

Sorry - thanks for the sugggestions though.

[BillHamilton]

Further information:

When the macro gives the error it stops in the debugger with the 'OnAction' line highlighted. If I then step into it with cmd+shft+I it works!!!! And all further executions also work. There are about 50 objects in the workbook that invoke a macro so it gets run a good few times.

I thought this might just be some sort of timing issue so I put in a Wait for 10 seconds and tried the instruction again.



On Error Resume Next
stMacroLink = sShp.OnAction
On Error GoTo 0
Application.Wait Now + TimeValue("0:00:10")
stMacroLink = sShp.OnAction ' <<< error produced here


It output the error on the second one as indicated. Then it continued when I stepped into it manually.

I have no clue.

[SamT]

I thought this might just be some sort of timing issue so I put in a Wait for 10 seconds and tried the instruction again.

... it continued when I stepped into it manually.
Yeppers, It sure looks like a timing issue and not an Unknown Function/Property issue.

Sometimes (In Windows) I use DoEvents when I suspect timing

stMacroLink = sShp.OnAction
DoEvents
stMacroLink = sShp.OnAction
DoEvents
...

:dunno Microsoft says: (https://docs.microsoft.com/en-us/office/vba/api/overview/office-mac#office-2016-for-mac-is-sandboxed)

Office 2016 for Mac is sandboxed

Unlike other versions of Office apps that support VBA, Office 2016 for Mac apps are sandboxed.
Sandboxing restricts the apps from accessing resources outside the app container. This affects any add-ins or macros that involve file access or communication across processes. You can minimize the effects of sandboxing by using the new commands described in the following section. (https://docs.microsoft.com/en-us/office/vba/api/overview/office-mac)That may be relevant since ShapeTypes are members of Office, but Shape.OnAction is a member of Excel, (Two different Apps?)

If you must use Wait, try waiting really short times like 100 milliseconds, or even less. Also try simple loop delays
For x = 1 to 1000
NextOn a Gigahertz processor, that's only a few microseconds

[BillHamilton]

Bad news. It still fails over a lot of combinations and repeats of DoEvents and delay loops as suggested (anything from 1,000 to 10.000,000). But it still works when stepped into with the debugger.

I know what you mean about sandboxing. I've come across that with accessing external files but I don't see how it might have its tentacles in here as the instruction does work, albeit manually the first time, and as designed for subsequent executions with no further intervention.

Thank you for taking the time to think about this for me.

[SamT]

Maybe move all Shape deeds to separate functions???


stMacroLink = GetAction(wsSht, stctrl)

Function GetAction(Sht as Worksheet, ShpName as String) As String
GetAction = Sht.Shapes(ShpName).OnAction
End Function

after that, it gets complicates. Class Modules run in their own Thread, if Multithreading is available. Create your own Shapes collection Class with Properties for each Shape property you need to use. Class Properties can have the same name as Application Properties, which would leave the rest of your code almost unchanged.

[BillHamilton]

SamT:
Sorry to have gone quiet - other things happening round here.

I tried your 'function' method and it gave the same failure. Something has changed though as when I step into the original failing instruction in the debugger, it does not process any Button objects. The only objects it processes correctly are Ovals, which are the only other objects with macros assigned. Same if I put an 'On Error Resume Next' before the OnAction assignment - no Buttons, only Ovals.

I've been coding in VBA (Windows) for over twenty years and Class Modules are a black hole as far as I'm concerned. I don't expect you to give me the 1.01 course on it here, so I think I'll just draw a line under this and put it down to something (else) that Mac VBA just cannot do.

Your previous suggestion of recreating the buttons as Rectangles is just too impractical for this application and would not justify the conversion time required. I did put in one Rectangle for a trial though, and it worked ok.

Thank you for your time.

Regards,

Bill

[SamT]

:thumb