Declaring Offset as a variable

[Dennis Benjamin]

Hi All

I have a VBA project which works fine but I'd like to improve my coding
style. In this project the user selects a cell in one sheet then a different
cell in another. Several checks are made on various values then the contents
of the corresponding row in the 1st sheet are copied to the second. For
historical reasons, the layout of the columns differs between the two sheets
(of course!) so the code boiled down to a bunch of statements like:

'NoteBook
mySht.Cells(myRow, dstNotebk) = ActiveCell.Offset(0, srcNotebk)
'Concentration
mySht.Cells(myRow, dstConc) = ActiveCell.Offset(0, srcConc)

where the variables dstNotebk etc. are declared as constants (e.g. const
dstConc = 5) and the source sheet is Active.

I'd like to neaten it up, for example allowing a syntax like

mySht.dstNotebk = ActiveCell.srcNotebk

i.e. have dstNotebk be a variable storing Cells(myRow,dstNotebk) and
srcNotebk storing Offset(0,srcNotebk). Is this possible? Is there a better
way to tackle this problem?

Thanks for any help!

 

[JLGWhiz]

I'll throw a little philosophy at you. Variables are useful when the data
the variable stores will be used multiple times within a procedure, to
conserve code space, to improve efficiency, and to provide a meaningful
reference when read by a user or all of the above. Some code writers like to
use a lot of variables as code names to make it difficult for novices to
interpret the code or because they think it somehow makes the code look more
professional. Personally, I like to keep it as simple as possible because I
get confused pretty easily at my age. Now, in answer to your question:
Unless something you do to the code improves the performance of it, making it
look pretty won't necessarily make it run better.

 

[Dennis Benjamin]

Thanks for the reply, JLG. You raise a good point that I had lost sight of -
by inventing my own syntax I will make it harder for someone else to
understand the code. Ironically, I was looking to make the code easier to
read/understand; there's a section where I do some calculations which is
impenetrable, the operators take up 1/50th of the space that the variable
names do! I just wanted to make the names of my variables smaller. If you
don't mind my asking, how would you handle a calculation where 3 variables
named

ActiveCell.Offset(0, srcConc)

are summed, and then the ratio of this total to another calculation is
compared to a value stored as a constant. Would you break it up into steps?
store intermediate values in intuitively named variables?

Any help appreciated!

D

 

[JLGWhiz]

I would try to reduce it to something manageable, if possible
and use simple formulas to work it out, something like the
following:


x = ActiveCell.Offset(0, 1).Value
y = ActiveCell.Offset(0, 2).Value
y = ActiveCell.Offset(0, 3).Value

a = x + y + z

b = Application.WorksheetFunction.Sum(Range("A1:C1")

If a/b = someVar Then
'Do something
Else
'Forget it
End If

Sometimes it is more expedient to stick with the offset references
but each case has to be evaluated by the developer. Hey, if you
write code that works, who cares what it looks like. I don't look
at the code of a program. I simply use the program. If the program
don't work, I might take a look at the code.