Loading of database in Userform_initialize subroutine

[cyberdude]

Hi,

I load a range of data into a listbox from a named ranged in an excel
spreadsheet by modifying the code in this link:

http://word.mvps.org/FAQs/InterDev/XLToWordWithDAO.htm

So, my modified code is:

Private Sub UserForm_Initialize()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim i As Integer
Dim object_name(50), object_chinese_name(50) As String

Set db = OpenDatabase("C:\Test\Book1.xls", False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `myDatabase`")

i = 0
While Not rs.EOF
object_name(i) = rs.Fields(0).Value
object_chinese_name(i) = rs.Fields(1).Value
ListBox1.AddItem (object_name(i))
rs.MoveNext
Wend

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Sub

I invoke the userform by the following simple code:

Sub show_form()

UserForm1.Show False

End Sub

Sometimes, I hide the userform by the code

Sub hide_form()

UserForm1.hide

End Sub

I would like to know if I repeatedly hide the userform by Sub
hide_form() and then invoke the userform by sub show_form(), will it
run the UserForm_initialize() and load the data from Excel again and
again? If it does, it will use more and more computer memory to hold
the data. Can the re-loading of data be avoided?

Mike

 

[Jay Freedman]

The UserForm_Initialize() runs only the first time the userform is instantiated
in memory. That happens the first time you call the .Show method, but never
again as long as the userform isn't unloaded. So the answer to your question is
"there is no problem here".

If the userform is unloaded (maybe by an Unload Me statement in a button handler
within the userform, or a UserForm1.Unload statement in some other macro
routine), then UserForm_Initialize() runs again on the next .Show call; but
since the userform was unloaded, the memory it occupied before (including the
contents of the list box) has been returned to the system for reallocation.

If you do have some code that you want to run every time the userform's .Show
method is called, put that into a UserForm_Activate() procedure. There you will
need to be careful about sucking up more memory.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all
may benefit.

 

[alborg]

Hi Jay, Mike:

I actually ran into this problem recently. When I opened up a template, it
would invoke an instance of MS Access, so on certain programming days I'd end
up with 6-7 instances of MSACCESS.EXE loaded.

Pretty easy fix, really. What you may wish to do is to kill off all
instances of MSEXCEL.EXE when you close the document. You first need go to
the Visual Basic programming pain of template. Check out this PNG picture of
how your platform should look like, with the "Private Sub document_Close()"
put in, under the "Private Sub Document_New()" (i.e. the document opening
code). Check this URL- Here is a PNG copy of the code from one of my
projects-
http://i38.photobucket.com/albums/e103/alborgmd/KillAccessInstances3.png .

Mike, in your case, since you are working with Excel, just put in something
like:

dim MyXL as object
On Error Resume Next
Repeat:
set MyXL = getobject("","Excel.application")
If Err = 0 Then
' Excel was running
MyXL.Quit
Set MyXL = Nothing
End If

That's it...

Cheers,
Al