Call procedures inside an imported bas file to be loaded during a macro run

[bpatt]

Hi,

I'm trying to save a common macro file (single file to edit) that will be used by multiple templated excel workbooks. It needs to be used by other computers and users so I don't think the personal workbook or creating an add-in would work. So, I'm saving the file with a revision number as a .bas file that will be imported by the template workbook when one of the procedures needs to be run.

At this time, I have form control buttons which, when clicked, will check if .bas file is imported/import .bas file if not or if an older version. This works fine. After that, I'm trying to call on the desired procedure (which is in the loaded .bas file) based on the click.

Problem occurs if there is no .bas file already imported. I get a compile error about the procedure not being defined. I assume this is due to the procedure not existing until I import it but it is trying to compile all the potentially used procedures at the start. Is there a way to tell it to ignore that the procedure doesn't exist yet as it will when it gets to the procedure call?

I guess I'm not understanding how VBA run sequence works. I was assuming that it would only "compile" the code when it was needed. I.e. it would compile the button click procedure and the procedure calls inside of this procedure are just links to a section of code that will be compiled when called. This way a procedure's code could be changed during a run if need be (replace with updated module). Does a run of any procedure compile all procedures in a module? So, even if I have an older version of the procedure loaded so the compiler doesn't complain, if I update the code of those procedures during a run it will still run the older "complied" code?

Does this need to be in two separate macro runs (one for updating procedures code and one to actually use that new code)?

Sub Button_Click(button_number)
    'if you click this button, do something
    CheckMacroVersion                            'this will check version and import .bas file procedures into a module if not there or older (will delete older version before import if need be)
    Select Case button_number
        Case 1:     'Load Data'
            Populate_Data_Sheet                 'this procedure code is in the .bas file
        Case 2:     'Publish PDF
            Publish_PDF                               'this procedure code is in the .bas file
        Case Else:
            MsgBox ("something went wrong")
    End Select
End Sub

[6StringJazzer]

I am not sure what scope is checked for compile errors when you start to run a Sub. Actually, "compile" is a misnomer. VBA code is compiled to an intermediate form called Microsoft P-Code before it is executed, but I think this happens every time execution starts, rather than being compiled only once. I do not know if P-Code is machine-specific. (This is similar to how Java is "compiled" down to bytecode then executed by a JVM.) In contrast, compiled code like C is compiled down to machine-specific object code and then bound into an executable file before it is run. It is only compiled once. I have been able to run Subs successfully even though syntax errors exist in other modules and even in Subs in the same module.

If you want to avoid a compile error when the .bas is not loaded, you can use Application.Run but you will still get a runtime error. You can trap the runtime error. You said "ignore" so this code will totally ignore it if the procedure does not exist. You can also put out an error message instead if you want to do that.

Sub Button_Click(button_number)
    'if you click this button, do something
    CheckMacroVersion                            'this will check version and import .bas file procedures into a module if not there or older (will delete older version before import if need be)
    Select Case button_number
        Case 1:     'Load Data'
            On Error Resume Next
            Application.Run("Populate_Data_Sheet")                 'this procedure code is in the .bas file
            On Error GoTo 0
        Case 2:     'Publish PDF
            On Error Resume Next
            Application.Run("Publish_PDF")                 'this procedure code is in the .bas file
            On Error GoTo 0
        Case Else:
            MsgBox ("something went wrong")
    End Select
End Sub

[Artik]

bpatt, If you are interfering with the code in the VBA project in one thread, you cannot call the procedure in the same thread. This is because the code has to be recompiled after a design change. And recompilation will take place only after the main routine is finished.
Try this way:

Sub Button_Click(button_number)
    'if you click this button, do something
    CheckMacroVersion  'this will check version and import .bas file procedures into a module if not there or older (will delete older version before import if need be)
    
    'Procedure call on a new thread
    Application.OnTime Now, "'BtnClick " & button_number & "'"
End Sub


'This code must be in standard module:
Sub BtnClick(button_number)

    Select Case button_number
        Case 1:     'Load Data'
            On Error Resume Next
            Application.Run ("Populate_Data_Sheet")  'this procedure code is in the .bas file
            On Error GoTo 0
        Case 2:     'Publish PDF
            On Error Resume Next
            Application.Run ("Publish_PDF") 'this procedure code is in the .bas file
            On Error GoTo 0
        Case Else:
            MsgBox ("something went wrong")
    End Select

End Sub

Another problem you may encounter.
Programming interference in a VBA project is possible only when the "Trust programmatic access to the project" option is selected, which is turned off by default. If you have no control over the other computers, it will have a problem. And I will mention another one - a secured VBA project.

Artik

[bpatt]

Thank you for the quick responses.

Sounds like I can't automate the bas import without trusted access. I suppose I could create a prompt telling users they need to enable access if they want to use the macros. Or do something like have the user manually add the bas file but that would require more operator knowledge of location/version.

I'll try the new thread method with a prompt for now.

[6StringJazzer]

I think Artik and I have two different interpretations about what you are trying to do.

I assumed that either the .bas file has been imported, or it hasn't. If it has, it will run; if it hasn't you can handle the error.

It appears that Artik has assumed that if it has not been imported, you want that to be fixed during execution of the macro so it can continue. I didn't get that from your description. Maybe you can clarify.

[bpatt]

Before the CheckMacroVersion procedure has been run, the status of the .bas file is unknown. It can be in 3 states. It can be not imported (no other procedures in any modules expect CheckMacroVersion and Button_Click), older version already imported (all procedures code in a module available to be used but an older version of it), or most recent version of the bas files imported. Not imported and older version needs to be updated by re-importing the most recent .bas file. If its the most recent version of the procedures code, nothing needs to be done but using the procedures as normal. I could just have an old version of the procedures (second state) in the template workbooks so the error doesn't occur but I think the first run would use the older version of the code (can't change during thread issue that Artik mentioned) while the next procedure call would use the newer code. I could also force the operator to manually import the newest version before running any macros but I wanted to try and have this auto-updated in the background without an operator having to identify the most recent version and location.

This project has at least 3 different template workbooks which define how data is analyzed and presented for reporting. Each of these different types will have 20+ different datasets that at times need to be updated or updated on how the data is presented. Currently I have ~60 workbooks with the procedures embedded in each workbook but I've improved/added the procedures over time. So, if I want the reporting consistent, I have the update the procedure code on all of them manually each time. Because of this, I'm trying out a single file in a shared dropbox location to be updated method that will be loaded/updated automatically when any of these workbooks are used. Just sounds like excel's default is to only allow this if you stay on a single pc or have somewhat finished code (custom add-in). I'm just updating this code too often to be sending these procedure files to other people.

Also, I did try Artik's method and it appears to work. Not sure if there is anything I could do about the trusted access if I'm using an auto-loaded bas file though.

Thanks

[Kyle123]

What is it that makes you think an addin wouldn’t work here?

[bpatt]

How are add-ins updated and shared? I'm under the impression that add-ins would require each user to install the add-in on their PC and then re-install or update ever time I changed the code. I don't know what the add-in update process is for custom add-ins (I change the code a lot)? Maybe if the add-in updated automatically it would be okay but I would still have to tell anyone that wanted to use this workbook full of data that much of the analysis requires installing said add-in.

I was trying to go for anyone connected to our dropbox could be sent a workbook full of data and run the most recent macros without manually updating the code.