none
Access 2010+: How to convert a table-based named macro to VBA? Is it possible? RRS feed

  • Question

  • OK, I've searched various forums on this and still something doesn't seem to make sense regarding converting table-based named macros to VBA.

    Let's say I've got an Item table (not a form) selected, and I click on 'Created Named Macro' from the Table command (in the ribbon on 2016).  I use the nifty interface to write some code.  I then save the macro and name it EditData.  That macro is now bound to the Item table.

    Now let's say that I really want to maintain this macro in VBA (so that I can more easily edit the code and perhaps add more functionality than I can with just the table-based macros.  Also, seeing the skeleton code in VBA will help me add more functionality in VBA without having to look up syntax so much.

    My issue is I don't see how to convert this table-based macro to VBA.  If I do create a macro to run the named macro, then I do see how I can convert the little bit of macro code to VBA.  But, what I really want to do is convert the code INSIDE the named macro, not the little macro stub that runs the macro.  Is that possible?  I would have thought so, but haven't found a way to do this.  I've also seen some posts that show how to convert macros associated with forms, but my initial macro is part of a table, not a form. 

    I feel like this is a stupid question for those experienced Access users, but I am just looking for the art of the possible.

    Thanks in advance.

    Scotty81

    Monday, November 7, 2016 9:00 PM

Answers

  • You are referring to a data macro, not a standard macro.  The actions of the latter are loosely equivalent to methods which can be called in VBA for a wide variety of programming operations.  Hence the functionality can be expressed in VBA code.  Data macros on the other hand cannot be converted to VBA code.

    Depending upon what you want to do, you might be able to write a VBA procedure or function which performs the tasks of a named data macro by using calling DAO or ADO methods, or by executing an SQL statement.

    Ken Sheridan, Stafford, England

    Tuesday, November 8, 2016 5:51 PM

All replies

  • Hi Scotty,

    when you try to create a macro then in the "Design" Tab. in the Tools block. you will find a button named "Convert macro to Visual Basic."

    after you create a macro you can use this way to convert the macro to VBA.

    I have the documentation about this but it is for office 2007 and they use Forms and Report.

    I try to find the documentation regarding office 2016 version. but its not available.

    so here I post an old link maybe help you to understand the functionality of this feature.

    Convert macros to VBA code

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 8, 2016 5:27 AM
    Moderator
  • Hi Deepak,

    I see what you are saying, but the reason that I hadn't noticed it earlier was that the 'Convert to Macro' button is greyed out.  Is there a way to make this button active in the Design View?  I've enclosed a screen shot so you can see what I see.

    Thanks,

    Scotty81

    Tuesday, November 8, 2016 1:53 PM
  • Hi Scotty,

    You may be out of luck. I couldn't find how to make the menu not greyed out. I thought using the Save As menu might do the trick but sadly it didn't work either. 

    I am experimenting with converting the macro to XML to see if I can convert the XML back to a regular macro.

    Tuesday, November 8, 2016 5:09 PM
  • You are referring to a data macro, not a standard macro.  The actions of the latter are loosely equivalent to methods which can be called in VBA for a wide variety of programming operations.  Hence the functionality can be expressed in VBA code.  Data macros on the other hand cannot be converted to VBA code.

    Depending upon what you want to do, you might be able to write a VBA procedure or function which performs the tasks of a named data macro by using calling DAO or ADO methods, or by executing an SQL statement.

    Ken Sheridan, Stafford, England

    Tuesday, November 8, 2016 5:51 PM
  • Hi Scotty81,

    I can see the issue with that disabled button.

    so here I think that the suggestion given by the Ken Sheridan is appropriate at this stage.

    please check it and if you think that it is the best suitable answer for your question then mark as an Answer.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 9, 2016 1:07 AM
    Moderator