Consulting

Results 1 to 10 of 10

Thread: Problem with OnAction in macro running on a Mac

  1. #1

    Problem with OnAction in macro running on a Mac

    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/thread...light=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/b...-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.)

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

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

  4. #4
    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.

  5. #5
    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.

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    ...
    Microsoft says:
    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.
    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
    Next
    On a Gigahertz processor, that's only a few microseconds
    Last edited by SamT; 05-31-2021 at 11:02 AM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

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

  8. #8
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  9. #9
    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

  10. #10
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •