0

Right now I am trying to automate the building of a form that needs data collection from multiple sources. This is a question that will influence my design so no code is available for reference yet.

General setup

In a module "Module1"

Public infoArray As Variant

Sub Main()

'info array initialized to some value here

'insert various lines of code here

formA.show (vbModeless)

End Sub

Sub afterComments()

'called by an action done in the modeless FormA

'insert code that accesses infoArray() here

End Sub

My question: If a variable is declared as public in a module, so that it exists outside the bounds of a subroutine, and a modeless form is called before the main subroutine ends. Will the value for that variable, set in the main subroutine, be available for the "afterComments" subroutine to access? Since the main subroutine will continue to its end because the form is modeless, I do not know if the values for those public variables will be cleared once that happens, or remain in memory since a form is still technically active.

(yes I realize I could probably find out quickly by running a test piece of code, but i don't have access to excel right now and I need to continue designing, also I apologize for the formating and any typos, this was written from my phone.)

So far I've only been able to find info on modeless form implementation, public variable access rules and that public variables are cleared when the code stops running, I just don't know if this counts as the code finishing or not because while the main subroutine finishes execution, the modeless form is still active. A simple yes or know would be sufficient, but any help would be greatly appreciated.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
Matthew Ternullo
  • 21
  • 3
  • 1
    Possible duplicate of [VBA Global variables no longer declared after deleting worksheet](https://stackoverflow.com/questions/39671309/vba-global-variables-no-longer-declared-after-deleting-worksheet) – Comintern Jul 25 '18 at 00:30
  • 1
    A global variable, once populated, retains its value until explicitly cleared or the VB project is reset (such as by calling `End` or by an unhandled error). It's not cleared as soon as your procedures are done running. Involvement of forms won't change that. – Tim Williams Jul 25 '18 at 00:34
  • @TimWilliams I'm still relatively new to VBA, and I am a little unclear as to what you are referring to when you say "calling End". When you say calling "End" are you referring to the "End Sub" from the "Main" Subroutine, or just "End" with no conditions attached? – Matthew Ternullo Jul 25 '18 at 00:49
  • @TimWilliams So in this case is the VB project reset? at the "End Sub" of the main subroutine? – Matthew Ternullo Jul 25 '18 at 00:59
  • 1
    @Comintern I already read that post when I was researching the question and it didn't clarify my answer, I looked more thoroughly this time and it still didn't help. I'm still relatively new to stack exchange and I am trying to post well formatted and thought out questions and I followed the tutorials on writing good questions while writing this. Can you give more of an explanation of what exactly qualifies as a duplicate question so I can avoid these mistakes in the future? – Matthew Ternullo Jul 25 '18 at 01:09
  • @MatthewTernullo - just `End` by itself. See - https://stackoverflow.com/questions/7041138/what-is-the-lifetime-of-a-global-variable-in-excel-vba/7043901#7043901 – Tim Williams Jul 25 '18 at 03:53
  • @TimWilliams Thank you very much! I'll make sure to read the answer comments from now on when I'm researching. – Matthew Ternullo Jul 25 '18 at 15:00

0 Answers0