Q2. Assuming this is not an addin, would you advise one
standard module in the workbook to hold all the controlling code, rather than sheet code?
It depends on what you are doing. But personally, I like to have a Module named "Main" that does the overall control &
aptly named modules or classes of routines that perform related functions (or just 1 module called Aux if working on
smallish project)
Sometimes on very small projects where just a quick routine is required, I'll leave it in the sheet (no modules).
Sometimes, you must have code in a sheet (event handlers for example), but if the functions performed can be
parameterised & will be used in other functions or sheets, put that in a module or class. That way, if a problem exists
in your code, you only make the correction in one place.
Don't repeat the same code, parameterise it in a function, or build a class (if appropriate)
e.g.
...::Sheet Code::..
Private Sub UpdateCol_A
Aux.UpdateCol("A")
End Sub
Private Sub UpdateCol_B
Aux.UpdateCol("B")
End Sub
...::Module Aux ::..
Public Sub UpdateCol(col as String)
'Lots of stuff...
'Lots of formatting...
'Lots of clever things... '***Some problem here fixed.***
'Lots of calculations...
End Sub
Q1. when do you? ... Do they need to be unloaded?
You can (if you wish) unload forms when you are finished e.g.
frm_Initial.Show
If frm_Initial.UserAcepted = True then
frm_SelectCert.Show
If frm_SelectCert.UserAcepted = True then
frm_whatever
End If
End If
Unload frm_Initial
Unload frm_SelectCert
Unload frm_whatever
But is it even necessary? VB will clean up when excel is closed.
Its not like the forms you are loading will hold MB's of data in variables - are they?
It is kind of a judgement call, perhaps your forms loads data from a file - or DB. Perhaps it takes 5 seconds to load
that. Would you want to have that happen everytime the form is to be shown? I wouldn't, I'd simply hide the form.
If the forms are for input & you don't clear them explicitly (ie Textbox1.Text = "": List1.Clear etc) then when they
are shown again, they will hold the old input data. Now this sometimes is a bonus & other times a pain!
If the forms are quick loading & must be cleared, Unload them after use!
From the info above, you should be able to make your own judgement
Regards - Steve.