Passing Objects As Parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a general programming method. I'm fairly adept at using VBA to open
and manipulate Excel files from within an Access code module. I try to break
out the different logical steps of my process into different subs. A lot of
times, the different steps involve the same Excel workbook and/or worksheet.

I know that you can pass objects (like workbooks and worksheets) as
parameters from one sub to another. However, knowing that Microsoft often
allows you to do things that you shouldn't do, is this really a smart thing
to do? As far as I am aware, there are a couple of different methods to
accomplish what I do (the list below encompasses only the ones that I've
done):

1) Each sub has its own Excel.Application object (as well as Workbook and
Worksheet objects), which are created and destroyed each time the sub runs.
The file/path of the Excel document is passed to the sub as a parameter.

2) There is a module-level Excel.Application object, but each sub has its
own Workbook and Worksheet objects that get created and destroyed as the sub
runs. The file/path is passed as a parameter.

3) The Workbook/Worksheet objects themselves are passed as parameters.

Are there any distinct advantages or warnings regarding any of these
methods? Is it only a matter of personal preference? I'm at the point in my
career where I'm developing my own personal code-writing style, and I don't
want to adopt any bad habits.

Thanks.
 
MDW said:
I have a general programming method. I'm fairly adept at using VBA to open
and manipulate Excel files from within an Access code module. I try to break
out the different logical steps of my process into different subs. A lot of
times, the different steps involve the same Excel workbook and/or worksheet.

I know that you can pass objects (like workbooks and worksheets) as
parameters from one sub to another. However, knowing that Microsoft often
allows you to do things that you shouldn't do, is this really a smart thing
to do? As far as I am aware, there are a couple of different methods to
accomplish what I do (the list below encompasses only the ones that I've
done):

1) Each sub has its own Excel.Application object (as well as Workbook and
Worksheet objects), which are created and destroyed each time the sub runs.
The file/path of the Excel document is passed to the sub as a parameter.

2) There is a module-level Excel.Application object, but each sub has its
own Workbook and Worksheet objects that get created and destroyed as the sub
runs. The file/path is passed as a parameter.

3) The Workbook/Worksheet objects themselves are passed as parameters.

Are there any distinct advantages or warnings regarding any of these
methods? Is it only a matter of personal preference? I'm at the point in my
career where I'm developing my own personal code-writing style, and I don't
want to adopt any bad habits.
Click to expand...


Some random thoughts on this issue:

Method 1 might run into the issue of clashes between
multiple instances operating on the same data.

Method 2 is subject to the global variables being reset on
any unhandled errors, or left hanging during testing when
you interupt processes frequently.

Method 3 has always been my method of choice and has not
caused any significant problems.

Another method is to create a class to manage the object
(create, access, terminate, etc), but in most cases, this is
overkill.
 
--
Hmm...they have the Internet on COMPUTERS now!


Marshall Barton said:
Some random thoughts on this issue:

Method 1 might run into the issue of clashes between
multiple instances operating on the same data.

Method 2 is subject to the global variables being reset on
any unhandled errors, or left hanging during testing when
you interupt processes frequently.

Method 3 has always been my method of choice and has not
caused any significant problems.

Another method is to create a class to manage the object
(create, access, terminate, etc), but in most cases, this is
overkill.
Click to expand...

Marshall,

Thanks for the input. As I've refined my own programming style, Method 3
seems to make the most sense to me. I was just worried that there was
something "wrong" with that approach (such as undue taxation of system
resources, etc.).

Thanks again.
 
This could be the explanation I am looking for in a problem I have been
having. That is that the Excel Application object is not destroyed until I
close the form or, in one case, I have to close the Access App.
I have been using a modifed #2, in that I Dim all my Excel Object variables
at the module level so all subs and functions have visibility.
Since Excel coding is almost as verbose as writing COBOL, I break it up into
logical process, 1. Create the objects. 2. Create and Format a Sheet, 3. Load
the Data, etc.
So, see if I got it here:
1. Scope and create the objects in the main processing loop
2. Pass the object as a parameter to the subs or functions that need to work
on them.

VVVVVVVeeeery IIIIInterrriisssting!
 
Yes, this method has been incredibly useful. With my process, I aminterested
in reading info from exsting forms instead of creaing new ones, but I'm sure
the principle is the same. (I would think that if you're creating new forms,
you'll want to use ByRef in all your subs). In a nutshell, here is what I do.


FOLDER_PATH = "C:\Some Folder\"

' Get a handle on the file system
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Get a handle on MS Excel
Set objXL = CreateObject("Excel.Application")

Set objFolder = objFSO.GetFolder(FOLDER_PATH)

For Each objFile In objFolder.Files

If UCase(Right(strFileName, 3)) = "XLS" Then

Set objWB = objXL.Workbooks.Open(FOLDER_PATH & strFileName)

Call Sub1(objWB)
Call Sub2(objWB)
Call Sub3(objWB)

objWB.Close
Set objWB = Nothing

End If

Next

objXL.Quit
Set objXL = Nothing

Set objFSO = Nothing

Private Sub Sub1(ByRef THE_WB As Excel.Workbook)

' Do stuff

End Sub
 
My world is mixed. I do some new spreadsheet creation and I pull data from
spreadsheets. We were having so many problems with data formats using
TransferSpreadsheet and, in some cases, copy from Excel, paste into Access,
that I have converted most of that to reading the data directly from the
spreadsheet, and writing into tables.

I have just about eliminated the case of leaving an EXCEL.EXE process
running, but I think this will take care of all of it except the one user I
had who decided it was running too slowly, so he Control Break ed out of it,
then wondered why Excel was hanging up when he tried to open a workbook.

I would not have a clue as to know how to determine if an instance of Excel
was left unattended so I could kill it.
 
This is just too darn cool!
I took one of my more complex Excel reports, took about an hour to make all
the mods, it ran first time through, and worked exactly as I had hoped. The
Excel process ended exactly when it should.
Aint it wonderful what you can learn out here!
 
There is nothing 'wrong' with any one approach - until you cause the
entire network for your organization to crash, the servers to crap out
requiring them to be reinstalled from scratch, the financials corrupted
beyond recovered and MOST of all causing a headache for one or more
administrative assistance. The first four are somewhat survivable, the
fifth however is a death wish.

MDW wrote:
 
Back
Top