Totals on Forms

[jkitzy]

I have a form used to enter cash-drawer information. The information is
stored as data in a spreadsheet. There are about 50 discreet fields of data
in strings, dates, and currency.

But in trying to build this, there are some questions I have. I'm using
Excel 2003:

1. There doesn't seem to be a way put the *fields* into an array for easy
population. I put the data from the found range into an array to make it
easier to deal with, but I still have to use a For...Each loop to populate
the fields. Some of the fields are formatted as dates, most are currency, so
I have several If statements to format those as dates. Is there a better way?

2. If I can't make the array of controls, then is there any advantage for me
in using an array at all? I mean, what's the difference in performance of
putting that data into an array first, then cycling through the array versus
just cycling through the cells themselves.

3. I'm torn between assigning the field a value from cell or setting it up
as a control source. The advantage of the control source method, as I see it,
is that I can automagically update the cells and I don't have to cycle
through every cell to put the information back into the spreadsheet. But I'd
rather assign values than control source because... well... if someone makes
a mistake, they can more easily recover the *before* data.

4. *Is* there any way to assign a bunch of controls to an array and just
dump the data back into the spreadsheet? Again, I'm having to cycle through
each field and put it back into my array to dump it into the spreadsheet.
This seems wasteful.

5. Is there any way to automatically calculate fields on the form? I'd like
to have users enter in denominations and have a running total update. I don't
want to put something in *every* field's change script if I don't have to.
It's very redundant.

5. Because I'm dealing with currencies, I'd like each field to be formatted
as currency. Is there any way to do that, again, without having to put the
format script in the change event for every field? Again, this seems tres
repetitive.

Sorry for the long-winded stuff here, but some overall guidance would help.
And you all are the experts! Thanks all. I search this forum on a daily basis
when I'm working on an app because I'm *not* a programmer. I rarely post
because I usually find what I need, so I don't often get to thank you!

 

[Ronald R. Dodge, Jr.]

As a general basis, I have also ran into some of the same issues, though
with different things. To overcome this sort of thing, in the past, people
would use Structures to address some of these concerns, but was still rather
limited. As a result of such limitations on Structures, Class Modules came
around and have replaced Structures. Class modules can resolve most of
these issues.

In this case, you would have one class modules that you only need to have
created one time within code to be the collection class, which then that
class will have basic properties and methods about the class and how to get
to the individual items of that collection class (this includes adding,
removing, counting, getting an item either by it's key or it's index number,
and what ever else). In addition to the standard stuff of a collection
class, you can also add your custom methods/properties to the collection
level to help it out.

There is one major draw back to collection class modules, and that is it
still doesn't work nearly as efficient as being able to use SQL against a DB
to summarize the data. However, with the use of Events, may be able to help
minimize some of that issue as well. Not entirely sure as I'm still going
through a learning process with events myself. The only problem I see is
that I'm not sure if there is a good way to get something to listen to
events of each of those individual class items. Even if there is, I'm not
sure how much of an overhead there may be as that would be dependent on how
many events per item multiplied by the number of items within the collection
class.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000