[AccessD] DoCmd.OpenModule for Access - Something similar for Excel ?

Bill Benson bensonforums at gmail.com
Fri Sep 19 19:45:36 CDT 2014


Brad it is even easier with Excel (unless you can do this in Access)... add
a reference to Microsoft VBA Extensibility 5.1 (?). That reference gives
you an object model to play with that is everything you need. If you put it
in an Addin, it will always be there for you to pick away at any
workbook's VBAPROJECT (unless locked for viewing).

You can loop the vbcomponents within a vbproject, and read the lines, parse
as display code on sheets.

It doesn't pop open the vba code modules (like View Code) but can show you
anything in any of them, and you can build userforms and populate drop
downs or sheets that look almost like the vba ide if you wanted to. Not
that I would ever take the time to reinvent the wheel, but the learning
curve in this might show you how to read/write dynamic vba in the future
and that know-how might benefit during another show.
On Sep 19, 2014 10:57 AM, "Brad Marks" <bradm at blackforestltd.com> wrote:

> Charlotte,
>
> I have an Access application that has 80+ VBA modules.  When I was
> developing this application, I found it very handy to have an Access form
> that had buttons that would make the VBA code visible via the
> DoCmd.OpenModule command.  I used this approach a lot because it made my
> work easier.
>
> Now, I would like to do the same thing with a large Excel application, but
> the DoCmd.OpenModule command is not available in Excel.
>
> Thanks,
> Brad
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:
> accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
> Sent: Friday, September 19, 2014 9:30 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] DoCmd.OpenModule for Access - Something similar for
> Excel ?
>
> Brad,
>
> Are you wanting to do this from form view or design view?  In design view
> you already have a button on the ribbon to view code, which takes you
> directly to the code module for the form or report.  And the property sheet
> events will allow you to go directly to the control code.  What exactly are
> you trying to accomplish with these "buttons"?
>
> Charlotte
>
> On Fri, Sep 19, 2014 at 6:09 AM, Brad Marks <bradm at blackforestltd.com>
> wrote:
>
> > Darryl,
> >
> > Over the past couple years, I have worked on a several Access
> > applications that had many many modules.  I found it to be very handy
> > to have buttons on a form to more quickly view the VBA code (via
> > DoCmd.OpenModule).  I know that there are other ways to get at the code,
> but this method seems faster.
> >
> > I am now starting a project in Excel which is going to also have many
> > modules.  I was hoping to use the DoCmd.OpenModule command to do the
> > same thing in Excel, but this command is not available in Excel.
> >
> > Thanks,
> > Brad
> >
> >
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com [mailto:
> > accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
> > Sent: Thursday, September 18, 2014 11:05 PM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] DoCmd.OpenModule for Access - Something similar
> > for Excel ?
> >
> > Pressing "Alt-F11" doesn't do the job?  That will open the VBE and
> > show you all the related code.
> >
> > If you are working with an object (say a button) you can right mouse
> > click
> > > 'Assign Macro' > 'Edit...'.  That will take you directly to the
> > underlying code for that object.
> >
> > Not sure why you need a button on the worksheet to get to the VBE (?)
> > I probably don't completely understand what your needs are with this.
> >
> > Regards
> > Darryl.
> >
> >
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com [mailto:
> > accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
> > Sent: Friday, 19 September 2014 12:12 PM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] DoCmd.OpenModule for Access - Something similar
> > for Excel ?
> >
> > Darryl,
> >
> > I would like to push a button to open up the VBE and see the code in a
> > module.
> >
> > Thanks,
> > Brad
> > ________________________________________
> > From: accessd-bounces at databaseadvisors.com <
> > accessd-bounces at databaseadvisors.com> on behalf of Darryl Collins <
> > darryl at whittleconsulting.com.au>
> > Sent: Thursday, September 18, 2014 7:58 PM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] DoCmd.OpenModule for Access - Something similar
> for
> > Excel        ?
> >
> > Hi Brad,
> >
> > I work mostly with Excel -  When you say "set up buttons in Excel in
> > order to open modules" - you mean to press a button in the workbook to
> > open up the VBE so you can see the code in that module, or do you mean
> > to assign code to run when the button is pressed?
> >
> > As you have noticed.  There are gobs of little syntax issues between
> > Access VBA and Excel VBA.
> >
> > For all things Excel - you can also try the Excel-L (or Excel G) lists.
> > They are very responsive and help.  Been subbed for years on both
> > (decades actually).
> >
> > G is usually for basic questions (How do I apply a Conditional Format
> > - that sort of thing) L is for more prickly advanced issues and code
> > stuff
> >
> > Naturally there is a wide grey area in that sort of classification.
> > Many folks are subbed to both so only post to one or the other.
> > X-posting is not necessary or appreciated.
> >
> > The EXCEL-L list is hosted on L-Soft international's LISTSERV(R)
> > software running on Microsoft Windows Server 2008 R2.
> > For subscription/signoff info and archives, see
> > http://peach.ease.lsoft.com/archives/excel-l.html .
> >
> > Cheers
> > Darryl
> >
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com [mailto:
> > accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
> > Sent: Friday, 19 September 2014 6:46 AM
> > To: Access Developers discussion and problem solving
> > Subject: [AccessD] DoCmd.OpenModule for Access - Something similar for
> > Excel ?
> >
> > All,
> >
> > I really like the DoCmd.OpenModule when developing in Access.
> >
> > Recently I have started a large project that is going to be using Excel.
> > I have very little experience with Excel VBA.
> >
> > It appears that DoCmd.OpenModule will not work with Excel VBA.  Is
> > there another way to set up buttons in Excel in order to open modules?
> >
> > I know that this is really an Excel question and not an Access
> > question, but I thought that someone in this group may have run into
> this before.
> >
> > Thanks,
> > Brad
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


More information about the AccessD mailing list