Making code efficient

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

Guest

In a project I am working on I have to fill arrays (sometimes
two-dimensional) with field names. Now entering code such as :-
Set ObjectVar(1) = Staff1
Set ObjectVar(2) = Staff2
Set ObjectVar(3) = Staff3
Set ObjectVar(4) = Staff4
Set ObjectVar(5) = Staff5

is all very well but each time I try to do the same with a loop such as:-
For i = 1 to 5
Set ObjectVar(i) = "Staff" & i
Next i

I get an error 'object required'. Surely there must be a way of doing this
more efficiently? One portion of my code involves seperately using Set
ObjectVar(x, y) = fieldname about 456 times. I'd be grateful for any
suggestions.
 
Terboy said:
In a project I am working on I have to fill arrays (sometimes
two-dimensional) with field names. Now entering code such as :-
Set ObjectVar(1) = Staff1
Set ObjectVar(2) = Staff2
Set ObjectVar(3) = Staff3
Set ObjectVar(4) = Staff4
Set ObjectVar(5) = Staff5

is all very well but each time I try to do the same with a loop such
as:- For i = 1 to 5
Set ObjectVar(i) = "Staff" & i
Next i

I get an error 'object required'. Surely there must be a way of doing
this more efficiently?
Click to expand...

Are these objects controls on the form where the code is running? If
so, you can use code like this:

For i = 1 to 5
Set ObjectVar(i) = Me.Controls("Staff" & i)
Next i

If they are fields in a recordset (named "rs", for example) you could do
something similar with:

For i = 1 to 5
Set ObjectVar(i) = rs.Fields("Staff" & i)
Next i
One portion of my code involves seperately
using Set ObjectVar(x, y) = fieldname about 456 times.
Click to expand...

That's an awful lot of controls or fields! May I ask what you're doing
with this?
 
According to a book I have its much more efficient to use a collection and to
use the logic
For Each...
Next

-Dorian
 
Dirk, thanks for the reply, I'll give it a go. Yes, the controls are on the
form where the code is running.

You asked what I was doing? I am writing a staff scheduling system. The main
screen consists of a grid of unbound text boxes 5 across by 56 down (hence
the 2-dimensional array). Each one is named by column and row eg, GE101. From
this a routine can calculate which date in the week the leftmost 1 in GE101
refers to and the two rightmost figures eg. "01" referring to the member of
staff (MoS) held in an array . These unbound controls emulate a 'paper'
planning grid. By clicking on an unbound control in the grid I can do a
lookup based on the previous criteria relating to date and MoS and edit a
table to reflect new planning entries and/or changes.

Terboy
 
Terboy said:
Dirk, thanks for the reply, I'll give it a go. Yes, the controls are
on the form where the code is running.

You asked what I was doing? I am writing a staff scheduling system.
The main screen consists of a grid of unbound text boxes 5 across by
56 down (hence the 2-dimensional array). Each one is named by column
and row eg, GE101. From this a routine can calculate which date in
the week the leftmost 1 in GE101 refers to and the two rightmost
figures eg. "01" referring to the member of staff (MoS) held in an
array . These unbound controls emulate a 'paper' planning grid. By
clicking on an unbound control in the grid I can do a lookup based on
the previous criteria relating to date and MoS and edit a table to
reflect new planning entries and/or changes.
Click to expand...

Gotcha. Maybe you could do something a trifle more data-driven using a
continuous form, or maybe not. Should be an interesting project. Good
luck with it.
 
Dirk, thanks for the previous post! The code worked a treat and obviously
improved my take on object variables.

Thanks again!
 
Back
Top