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.
"Dennis Benjamin" wrote:
> 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" <(E-Mail Removed)> wrote in message
> news:045F468B-CB7C-492D-AF73-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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!
> >>
> >>
> >>
>
>
>
|
Similar Threads
