Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Offset as a variable
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Offset as a variable
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Offset as a variable
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" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Offset as a variable
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" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a variable be used when declaring an API call ? | Excel Programming | |||
Declaring a tab name as a variable | Excel Programming | |||
Declaring a tab name as a variable | Excel Programming | |||
Declaring Variable as VBConstant | Excel Programming | |||
Declaring a variable? | Excel Programming |