Efficiency, Workbook objects in User Defined Record

[Neal Zimm]

Hi All,

Am developing an App where user's Wbk references an AddIn.

Data in AddIn sheets control options available to users.

Some of this data becomes arrays in a User record and I use the
AddIn object a lot for lookups.

Record is updated in a bunch of different Sub's and Functions.

Public Type uOptRec 'there are more fields, about 20. a few to illustrate

iMthDaysAy(12) as integer 'Qty of days in month

iMidMthDay(12) as integer 'day of midpoint in month

bExpenseAuth as boolean 'expense authorization yes/no

AAscAddIn as workbook 'where the data is stored
End Type

I'm not a super techie and don't know the "cost" of putting a
workbook object in a user record.

It seems easy to put the AddIn in the record versus:

Sub UpdateA(AAscAddIn as workbook, uOptRec as uOptRec, ..........

to minimize the arguments in the sub/function statement.
Bullen's book says "keep them to no more than 5, usually ... "

But, is it efficient ???
Does a workbook object in a record take up a lot of computer resources?
Is it anything like a "copy" of the .xla file IN the record ?

What are the Pro's and Con's ?.

Thanks, Neal Z

 

[Peter T]

An Object variable is very cheap in terms of resources. It's just a pointer
to the address in memory where the actual object exists. The main thing to
be careful of is to ensure any object variable is released once the object
no longer exists (no need to do anything if the variable is about to loose
its scope).

In passing, though it's a matter of style "Types" are generally appropriate
for a number of closely associated variables, eg name, age, address. I get
the impression you are using a Type merely as a means of consolidating a
number of variable to avoid passing them all in one go.
Since 16-bit has all but disappeared best to change those As Integer to As
Long

Regards,
Peter T

 

[Neal Zimm]

Peter -
Thanks much. Your answer's a relief.

It may seem like putting fields in a uRec is to avoid passing arguments,
but its not. The sample fields all contain option like data within the App
that will vary by customer. Putting the AAscAddIn field in, however, was
just that.

Re: As Integer, I'm a dinosaur and my first programming language was
IBM's assembler language for the System 360 hardware (Circa 1968) when just
about the largest IBM computers made had, WAIT FOR IT, 512K of memory, and
it took up a room about 30 feet by 40 feet. They used to heat buildings with
the hot air generated by that memory. (Three tiny wires running through a
small metal doughnut was one byte, Oh Well......)

Bytes were precious and old habits die hard.

As a follow up, what are some ways I could 'keep up' with things like
Integer vs. Long ? I really do kinda like efficiency, and when I first read
about Types, 8 bytes being half of 16 seemed cool.

Again thanks,
N

 

[Peter T]

512k of memory, what luxury, that's 4x more than 128k !

As to "keep up with things" probably the best way, perhaps the only way, is
to follow the groups, such as this one. Post trimmed relevant code and maybe
someone will point out other things "in passing" the actual subject of the
question.

"As Integer" is not wrong, and intuitively might seem economical. However it
gets converted to a Long internally so an extra step is involved (not that
you would likely notice). Also it can bite you with any accidental attempt
to assign a +32k value, such as a row number in the bottom half of the
sheet.

More here, note what follows after "except" in the 2nd paragraph.
http://msdn.microsoft.com/en-us/library/aa164207(office.10).aspx

Regards,
Peter T