Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming the VBA Editor Question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming the VBA Editor Question
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) On Mon, 20 Oct 2008 11:55:02 -0700, Barb Reinhardt wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming the VBA Editor Question
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. -- Rick (MVP - Excel) "Barb Reinhardt" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming the VBA Editor Question
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 "Chip Pearson" wrote: 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) On Mon, 20 Oct 2008 11:55:02 -0700, Barb Reinhardt wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming the VBA Editor Question
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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Barb Reinhardt" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming the VBA Editor Question
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) On Mon, 20 Oct 2008 12:57:01 -0700, Barb Reinhardt wrote: 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 "Chip Pearson" wrote: 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) On Mon, 20 Oct 2008 11:55:02 -0700, Barb Reinhardt wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query Editor question | Excel Discussion (Misc queries) | |||
VBA Editor How to Question | Excel Programming | |||
Excel crashes when programming to the VBA Editor | Excel Programming | |||
Editor question | Excel Programming | |||
A few problems Programming to the Visual Basic Editor | Excel Programming |