Programming the VBA Editor Question

[Barb Reinhardt]

I've been asked if it's possible to copy a module from one workbook to
another programmatically. I've used some of the information here

http://www.cpearson.com/excel/vbe.aspx

to do this within one workbook. Right now, the user selects the workbook to
open and copies the code from the workbook that is executing. I'm now
wondering how something like this can be sent out to the users. I
downloaded the Microsoft Visual Basic For Applications Extensibility 5.3
reference to my computer some time ago and I'm sure the users don't have
this.

Without having them save all their files in some central location and me
running a macro to update them all, is there some other way to do this?

Thanks,
Barb Reinhardt

 

[Chip Pearson]

I'm sure the users don't have
this.

I think that the users would have that installed on their machine. I
don't believe that it is an optional component.

You can use all of the objects in the extensibility library without
using the reference (the library must be installed on the local
machine but need not be referenced in the project's References list)
by using late binding. That is, instead of declaring a variable with a
specific extensibility type (e.g., As VBComponent), you declare all
the objects As Object. Then, change all the constants (e.g., vb_ext_*)
from the constant name to the numeric equivalent, which you can find
via the object browser in the VBA editor.

Note that user's must have enabled the "Allow Access To The
VBAProject" setting.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Rick Rothstein]

I'm not sure if this will be useful to you or not, but you can Export your
module to a file and give your users access to it (by either putting it in a
common directory or emailing it to them) via the Import option. Try this...
select your Module and then click File/Export on the VB editor's menu bar,
then pick a location to save it to. Now, open up a new/different workbook,
select File/Import on the VB editor menu bar, find the file by its module
name (with a .bas extension) and open it. It should now be in the workbook
and, if you save the workbook, the module will save with it just like you
created it inside that workbook.

 

[Barb Reinhardt]

Two questions:

1) Must the users trust the VBA project in all cases (early binding and
late binding)?
2) What's the benefit of late binding (to the users)? I'm just not getting
this part. I gather they still need the reference on their computer, but it
doesn't need to be selected.

Thanks,
Barb Reinhardt

 

[Barb Reinhardt]

I've done it that way a lot myself, but I don't think the users would be
happy if they had to import the modules this way.

Thanks,
Barb Reinhardt

 

[Chip Pearson]

1) Must the users trust the VBA project in all cases (early binding and
late binding)?

Yes, the user needs to enable access to the VBA project regardless of
whether early or late binding in used.

2) What's the benefit of late binding (to the users)? I'm just not getting
this part. I gather they still need the reference on their computer, but it
doesn't need to be selected.

With early binding, you set a reference to the extensibility library
and then use the data types defined in that library. This occurs at
compile time. With late binding, everything is declared As Object so
type resolution is deferred until run time. There is no benefit to the
end user. Late binding allows you to avoid errors that would occur if
the extensibility library is not selected at compile time. The actual
extensibility library file must reside on the user's machine at run
time. Otherwise, you'll get object creation errors when the code is
executed.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)