TheSwamp
Code Red => VB(A) => Topic started by: David Hall on October 08, 2014, 12:40:39 PM
-
I have searched and cant find a way to do this, and maybe it cant be done. Is there a way to have an excel spreadsheet import code from another spreadsheet into the VBA IDE programatically? I have 500 or so spreadsheets that need code updated, and I dont want to cut/paste to all 500. I was hoping for a piece of code that would update code from a master file everytime its opened.
-
I seem to remember seeing something that would allow you access to the VBE code segment, but I also remember something about MS locking it down because it was one of the ways virii would infect VBA modules.
-
I was afraid of that, or something similar. I too had thought RR had done something way back in the day, but had no way to search it out. thanks Keith
-
I knew it could be done. I hereby quote from something I bumped into years ago and put aside for a possible future
Excel VBA has VBE object whose object hierarchy simplified version is:
VBE
VBProject
VBComponent
CodeModule
Designer
Property
Reference
the VBProject object should be what you're looking for as a developer since by its means you can also write code for adding and removing VBA modules or Inserting VBA code
but there goes along the security issue you were told by Keith
error messages thrown while a VBA macro is trying to modify components in a VBA project depends on a setting in Excel’s Trust Center dialog box. You can view and change this setting a follows:
- Choose File ->Options
- In the Excel options dialog box, click the Trust Center tab
- In the Trust Center Tab, click the Trust Center Settings button
- In the Trust Center dialog box, click the Macro Settings tab
Or, you can use the Developer➜Code➜Macro Security command to go directly to a dialog box with a check box labeled "Trust Access to the VBA Project Object Model" which should be turned off by default. Even if you choose to trust the macros contained in the workbook, the macros can’t modify the VBA project if this setting is turned off. And this setting applies to all workbooks: you can't changed it for only a particular workbook.
To detect this setting via VBA is to attempt to access the VBProject object and then check for an error, as via the following code:
On Error Resume Next
Set x = ActiveWorkbook.VBProject
If Err <> 0 Then
MsgBox “Your security settings do not allow this macro to run.”
Exit Sub
End If[
On Error GoTo 0
As a developer creating projects you’ll need to enable the Trust Access to Visual Basic Project setting
I think you could find VBComponent object's "Export", "Remove", and "Import" methods very useful for your specific needs.
by their means you could distribute your users (or use it you yourself) a workbook that contains a macro that replaces the VBA module to update with an updated version stored in a file.
hope this can help you
bye
-
Well, that helps, now to figure out how to import to the ThisWorkbook. thanks
-
as a startup
Every workbook is represented by a VBProject object.
To access it using early binding, establish a reference to the Microsoft Visual Basic for Applications Extensibility Library
The Workbook object has a VBProject property that returns a VBProject object.
for example with
Dim VBProj As VBProject
Set VBProj = ActiveWorkbook.VBProject
you create an object variable that represents the the active workbook's VBProject object
then you can access a VBProject's components (UserForms, modules, class modules, and document modules) via the VBComponents collection, like
Dim VBC As VBComponent
followed by
Set VBC = ThisWorkbook.VBProject.VBComponents(1)
or
Set VBC = ThisWorkbook.VBProject.VBComponents(“Module1”)
for example you can list all modules in your workbook with something like
Sub ListModules()
Dim VBProj As VBProject
Dim VBComp As VBComponent
Dim CodeMod As CodeModule
Dim FirstLine As Long
Dim mssg As String
' set active workbook's VB Project
Set VBProj = ActiveWorkbook.VBProject
' Loop through its components
For Each VBComp In VBProj.VBComponents
Set CodeMod = VBComp.CodeModule
FirstLine = CodeMod.CountOfDeclarationLines + 1
Do Until FirstLine >= CodeMod.CountOfLines
mssg = mssg & VBComp.Name & ": " & CodeMod.ProcOfLine(FirstLine, vbext_pk_Proc) & vbNewLine
FirstLine = FirstLine + CodeMod.ProcCountLines(CodeMod.ProcOfLine(FirstLine, vbext_pk_Proc), vbext_pk_Proc)
Loop
Next VBComp
MsgBox mssg
End Sub
where you can also see the use of the VBComponent property "CodeModule" and some of this latter properties and methods. of which you can find online help pressing F1 key
-
Thanks, that should get me started in the right direction
-
In case anyone is searching this form looking for ideas on how to accomplish this, here is what I do to ensure that everyone is running the latest code (this code is being used in Excel).
Create a simple Excel Addin that has code to load a module into vba from a standard location (I have it at a shared network folder). The Addin should never change, and therefore is suitable for everyone to load on their copy of Excel. Here is the code I have for the AddIn that I wrote. It resides in the "ThisWorkbook" section:
Private Sub app_WorkbookActivate(ByVal Wb As Workbook)
'Register the ###### Macros
Debug.Print "Registering ###### Macros"
Call RegisterMacros
End Sub
Private Sub Workbook_Open()
On Error Resume Next
Application.EnableEvents = True
Set app = Application
'Remove ######Functions
Set mf = Application.VBE.VBProjects("*your addin name*").VBComponents.Item("*your module file name*")
Application.VBE.VBProjects("*your addin name*").VBComponents.Item("*your module file name*").Name = "*your module file name*remove"
Application.VBE.VBProjects("*your addin name*").VBComponents.Remove mf
'Add ######Functions from network
Application.VBE.VBProjects("*your addin name*").VBComponents.Import "*your file location here\*your module file name*.bas"
End Sub
With this code, whenever a Excel is opened, it renames and removes the module (if it exists). It then imports the module into VBA. The app_WorkbookActivate event calls a function that I have in my module that registers all the macros I wrote with Excel (so that all of the User Defined Functions aren't stored under "UserFunctions", and instead stored in custom folders).
It's all really simple, but it has worked for me for the last 10 years. The only thing to remember with this is that if you make any changes to your module, and you save it, you need to "export" the module. If you don't, your saved changes will be overwritten with the saved module the next time Excel is started.