Author Topic: Excel VBA program to import program  (Read 7059 times)

0 Members and 1 Guest are viewing this topic.

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Excel VBA program to import program
« 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.
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

Keith™

  • Villiage Idiot
  • Seagull
  • Posts: 16899
  • Superior Stupidity at its best
Re: Excel VBA program to import program
« Reply #1 on: October 08, 2014, 01:49:12 PM »
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.
Proud provider of opinion and arrogance since November 22, 2003 at 09:35:31 am
CadJockey Militia Field Marshal

Find me on https://parler.com @kblackie

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: Excel VBA program to import program
« Reply #2 on: October 08, 2014, 01:51:08 PM »
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
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

RICVBA

  • Newt
  • Posts: 62
Re: Excel VBA program to import program
« Reply #3 on: October 09, 2014, 03:37:52 AM »
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:
Code: [Select]
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

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: Excel VBA program to import program
« Reply #4 on: October 09, 2014, 10:34:13 AM »
Well, that helps, now to figure out how to import to the ThisWorkbook.  thanks
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

RICVBA

  • Newt
  • Posts: 62
Re: Excel VBA program to import program
« Reply #5 on: October 09, 2014, 11:53:42 AM »
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
Code: [Select]
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
Code: [Select]
Dim VBC As VBComponent followed by
Code: [Select]
Set VBC = ThisWorkbook.VBProject.VBComponents(1)or
Code: [Select]
Set VBC = ThisWorkbook.VBProject.VBComponents(“Module1”)
for example you can list all modules in your workbook with something like
Code: [Select]
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

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: Excel VBA program to import program
« Reply #6 on: October 09, 2014, 02:49:23 PM »
Thanks, that should get me started in the right direction
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

mmelone

  • Mosquito
  • Posts: 12
Re: Excel VBA program to import program
« Reply #7 on: May 04, 2015, 02:41:53 PM »
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:

Code: [Select]
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.