Using Names for Cells rather than "A1" and benifits for math calculations in a macro

[DM2]

I'm working on a spreadsheet to calculate pressure buildup in a room as a result of introducing a chemical agent in the room (no...it's not a lethal chemical).

At the moment I'm setting up a userform for the data entry and haven't gotten to coding the calculations yet. In this process I'm seeing where I might need to move some information around in my spreadsheet so that the calculation process is more ordered (i.e. data for step 1 in row 1, step 2 in row 2,.....step 10 in row 10, etc.). Currently the data for step 7 may be in row 3 and so on.

If I start moving the data around now I'm going to have to tweak a number of macro's which currently are in the form of "a_Un = ['Calculations'!V5].Value: ['Calculations'!BY5].Value = a_Un". While there there is quite a lot of tweaking that would be needed even at this stage, it's going to be a nightmare later.

Question:

If I assign names to various cells and referenced those names in the macro's, I'm guessing I could move the cell around all day long and not have to tweak the macro?

While I don't like using "Names" in formula's because I've always felt it difficult to go back later and modify or fix something. I've read a few posts here that disagree with this, but then again I'm going to be the one that has to maintain it so...

I've assigned standard engineering nomenclature, using Excel equation editor (Agent Quantity = A_q). While none of the notations at this moment use characters that couldn't be noted with standard ^SUPERSCRIPT, or _SUBSCRIPT formatting, but I'm concerned I have have to (mass for example m"), so I'm sticking with the equation editor for now.

Since I'll be assigning variables to the values, I'll be using the cell notations (without the script).

Question:

Am I correct in assuming there's going to be a clash with naming a cell "Aq" and then assigning a variable with the same name to reference that same cell?
If so, would a cell name of "Aq", with a variable name of "a_Aq" be an issue?
Will using cell names in the VBA Code slow things down?
Will doing the math in the VB code be slower then doing it with a formula?

[protonLeah]

I don't know was you mean by "...move a cell around..."
Say you name cell N6 as NetWeight. In the Name Manager you will see in refers to: =Sheet1!$N$6
If you drag the cell to another location, the refers to value will change to its new location.

To reference the cell N6 in VB code you would use such as
MyVar = Range("NetWeight").Value

If you declare a variable:
Dim NetWeight As Double, that variable name is independent of the named range:

Given: cell N6 holds 22606

Please Login or Register  to view this content.

This code will report:
22606
11066
So, Named Ranges and Variable names are independent but could be confusing to read.

[TMS]

Just to add to what protonLeah has said, I normally prefix named ranges with "nr" and variables with their type. So, in the example, I would refer to dNetWeight and Range("nrNetWeight").

I also tend to hide my named ranges so they are not obvious and less likely to get deleted.

Regards, TMS

[DM2]

Bob,

I don't know was you mean by "...move a cell around..."

If I use my mouse on the spreadsheet to move cell $N$7 which has a name of "NetWeight" to cell $N$24, the name manager automatically compensates for the change (Netweight=$N$7, =$N$24)

Q1: If I use the cells "Name" in the VBA code (rather than $N$7, then I don't think I have to worry about updating the VBA if I move the cell from N7 to N24...Right?
Q2: Or...maybe because I didn't use the "$" sign in the VBA, I had to update the code when i move N7 to N24?

Let's assume for a minute that cell N6 (NetWeigh) has a value of 11066
This value was established by a "TextBox" on a userform with a name of "NetWeight" (value converted to an integer)

Cell N7 is named "CylQty", and has a value of "2" (total quantity of cylinders, each with the same netweight).
The value was established by a "TextBox" on the same userform with a name of "CylQty"

Cell N8 is named "TotNtWt" (Total Net Weight) and needs to hold the total net weight.
A variable would be associated with N8 of "TotNtWt" for consistence and possible use elsewhere in the code.

Q3: Is using the same name in the "Name Manager" as in a VBA Variable good, or poor code practice?
I like TMS's idea...

I normally prefix named ranges with "nr" and variables with their type

On paper the equation would look like NetWeight x CylQty = TotNtWt.
This math is about 1/10th as complex as most of the equations so most of the formulas would be much longer and harder to decipher.

Q4: Would using variables to perform calculations in the VBA yield shorter equations that might be simpler to follow?
Q5: Would using variables allow for faster, or slower execution, or would it matter?

[protonLeah]

A1: True
A2: If you use like Range("N6") or Range("$N$6") in code, then you must find and edit every occurance of it to the new cell address.
A3: TMS's practice is better.
A4: Yes. Also, if you use names for the ranges and accidentally mis-spell one, the debugger will let you know as soon as you press enter(if you have "OPTION EXPLICIT" enabled). If you are using Range(N6).Value instead of the name and accidentally type M6 or N7 you won't know until you get bad answers or a crash, then you will have to hunt for the mistake.
A5: I don't know if it will affect execution speed.

[TMS]

Q1: Right
Q2: Dollar signs in the VBA range reference are irrelevant; the range reference will not auto-adjust, they are fixed in your code whatever
Q3: it is personal choice. In the same way as it is good practice to use a type prefix, it helps to identify your named ranges and, indeed, dynamic named ranges
Q4: depends on how long you make the variable names. But it is likely to be more readable (and maintainable) if the variable names relate to their content.
Q5: not sure. Suspect faster if you set the variable once and use it many times; hence you don't have to evaluate range addresses.

Regards, TMS

[DM2]

B. Johnson / TMS,
Thanks for your replies.
A1: Thought so.
A2: I've discovered this, and updating is a pain.
A3: I'm going to follow TMS's lead, or some deviation thereof
A4: Ahhhh...I like the idea of the error checking as well!!!!!!
A5: Ease of understanding the code is more important, especially for someone like me...

[TMS]

You're welcome. Thanks for the rep.


Note: This code

Please Login or Register  to view this content.

is more understandable in, say, six moths, than:

Please Login or Register  to view this content.

But, clearly, the latter is much shorter

Regards, TMS

[shg]

I'm pretty fussy about names, and picked up my naming convention partly from Bullen's PED

Names with Worksheet scope:

o Fixed single cells start with ptr (e.g., ptrInput)

o Fixed named ranges start with rgn ("region"), tbl (table), or according to purpose: lut (lookup table), val (validation list)

o Me refers to the cell in which it appear (RefersToR1C1 "=rc")

o myRow and myCol ("=r" and "=c") (a common formula I put above or below tables is =sum(tblXxx myCol))

o Other relative named ranges start with rel (relN/E/S/W are one cell up, right, down, and left "=r[-1]c", "=rc[1]", etc.)

o Fixed rows start with row (rowHdr) (number rows with a formula like =rows(rowHdr:Me) - 1)

o Fixed columns start with col (colPrice, colQty)

o Named formulas start with frm (frmExt "=colQty * colPrice"); then just use the formula =frmExt

Names with Workbook scope

o Constants start with con (conBig "= 9E+307 + 8.97693134862315E+307 + 6E+292", conZzz "=rept(z, 255)")

[DM2]

Shg,
Ok...I have to ask, being an engineer and always interested in uniformity:
Q: How does one go about getting one's hands on the "Bullen's PED" document? Is this the book http://www.amazon.com/Professional-E.../dp/0321262506 ?
Q: Not sure I understand "...Fixed single cells..." If Cell N6 is "NetWeight" and is an integer, would it be "ptrNetWeight"?, What's the significance of "ptr..."?
Q: In the case of my example above "TotNtWt = NetWeight x CylQty", would it be "frmTotNtWt=ptrNetWeight * ptrCylQty"
Q: Do you provide any documentation at the beginning of your code with these details?, maybe in a separate code module?

Also...I changed this thread to "Un-Solved" as it would appear that there's more valuable contributions coming.

[TMS]

Q: In the case of my example above "TotNtWt = NetWeight x CylQty", would it be "frmTotNtWt=ptrNetWeight * ptrCylQty"

These are variables defined and used in the VBA. So, no.

shg is, I think, referring to naming conventions for named ranges, dynamic named ranges and named formulae.

[shg]

Is this the book ...

Yup.

Q: Not sure I understand "...Fixed single cells..." If Cell N6 is "NetWeight" and is an integer, would it be "ptrNetWeight"?, What's the significance of "ptr..."?
Q: In the case of my example above "TotNtWt = NetWeight x CylQty", would it be "frmTotNtWt=ptrNetWeight * ptrCylQty"
Q: Do you provide any documentation at the beginning of your code with these details?, maybe in a separate code module?

ptr is just the prefix for named single-cell ranges. As TMS says, this is my naming convention for ranges, constants, and formulas (what you see in Name Manager), not VBA variables. I also have a naming convention for those.