How to change a global variable of a workbook by activating a macro from another workbook

Question

I have two excel files, a patcher.xlsm and a file.xlsm. There is a button on file.xlsm, when it clicks it executes a procedure on Sheet 1 with a message box: The magic number is: 5.

Now, I have a patcher.xlsm, that would re-import a patched Sheet 1 and a Standard module with a global variable and run a macro on file.xlsm to set this number to 7. The expected result is such that, users clicking on file.xlsm would now see The magic number is: 7.

For some reason, whenever I executed patcher.xlsm, the magic number is always reset to 0, as though the memory has been cleared or project reset.

Is there a way to assign a global variable from another workbook?

In the past, I have looked at several stackoverflow threads, example, changing the scope of the variable, private, public, global, etc, activating the workbook, looking at Excel's hidden name space at the moment.

Sub Proc()
  MsgBox "The magic number is: 5"
End Sub

Sheet1 in file.xlsm

Call CopyModule("patcher_b_progress_bar.xlsm", "file_b.xlsm", "Sheet1", True)
Call CopyModule("patcher_b_progress_bar.xlsm", "file_b.xlsm", "GlobalModule", True)
Application.Run ("'file_b.xlsm'!GlobalModule.setDefaultMagicNumber")

patcher.xlsm "patching" the sheet 1 and setting a magic number via a module

CopyModule 
' export "patched GlobalModule" from patcher.xlsm as temp.bas
' remove GlobalModule from file.xlsm
' re-import "patched GlobalModule" to file.xlsm
(too lengthy to post here, referenced from http://www.cpearson.com/excel/vbe.aspx)

CopyModule of patcher.xlsm

Option Explicit

Private MAGIC_NUMBER As Integer

Public Function getMagicNumber() As Integer
    getMagicNumber = MAGIC_NUMBER 
End Function

Public Function setDefaultMagicNumber() As Integer
    MAGIC_NUMBER = 7
    setDefaultMagicNumber= getMagicNumber()
End Function

GlobalModule of patcher.xlsm

Sub Proc()
  If GlobalModule.getMagicNumber= 7 Then
    MsgBox "Magic number is correct: " & GlobalModule.getMagicNumber
  Else
    MsgBox "Magic number is wrong: " & GlobalModule.getMagicNumber
  End If
End Sub

Sheet1 of patcher.xlsm (to be overwritten to file.xlsm)

EDIT: I have modified the issue. It seems like the variable MAGIC_NUMBER is re-initialise to "0" even though the line "GlobalModule.setDefaultMagicNumber" is executed successfully.

Answer 1

Welcome to SO and Congratulation for asking a real good question

This is only a part solution and not at all any where near to satisfaction. However i am posting it, since it would somehow serve your purpose of code to change Magic_Number to some desired integer.

I tried it some different setup with slightly different objective (which could not be achieved).

In File.Xlsm Workbook Open event

Private Sub Workbook_Open()
SetMagic
End Sub

In Module1 of File.Xlsm

Global Magic_Number As Integer
Sub Test_Magic()
MsgBox Magic_Number
End Sub

Sub SetMagic()
Magic_Number = 5
ThisWorkbook.Sheets(1).Range("F1").Value = Magic_Number
End Sub

In Sheet1 Change Event of File.Xlsm

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1:E20"), Target) Is Nothing Then
    Magic_Number = Magic_Number + 1
    Range("F1").Value = Magic_Number
    End If
End Sub

Finally in Patch.xlsm Module1

Sub ChangeMagic()
Dim wb As Workbook, Opn As Boolean , Ln as Long
    Onp = False
    For Each wb In Application.Workbooks
    If wb.Name = "File.xlsm" Then
    Opn = True
    Exit For
    End If
    Next

    If Opn = False Then
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\File.xlsm")
    End If


    With wb.VBProject.VBComponents("Module1").CodeModule
    Ln = .ProcBodyLine("SetMagic", vbext_pk_Proc)
    .ReplaceLine Ln + 1, "Magic_number =  100"
    End With

    Application.OnTime Now + TimeValue("00:00:03"), "'File.xlsm'!Module1.SetMagic"
    wb.Activate
End Sub

The line

Application.OnTime Now + TimeValue("00:00:03"), "'File.xlsm'!Module1.SetMagic"

do the trick. after replacing the required code line in VBE, macro in Patch.xlsm ends and SetMagic get executed after a delay when File.xlsm activated. Magic_Number will be 100.

But it still not serve my intended purpose as during trials it become evident that Magic_Number is resetting to 0 as soon the VBE code module line is replaced (i.e. before executing Sub SetMagic). But your purpose will be solved and Magic_Number is getting any value we set from Patch.xlsm.

For my objective of dynamically setting Magic_Number = Magic_Number + 100 could not be achieved. For achieving that purpose, i have to workaround and keep the latest value of Magic_Number stored in some cell etc and retrieve from there (that will loose real spirit of the code intended)