Is there a preference to call/address variables vs cells?

[EssKayKay]

Hello,
No biggie here and again, I must note I am not a programmer so please bear with me if you please.

I was wondering if there is a preference on which coding is more common or acceptable. My worksheet has a few routines that address the status of conditions that change. I have worked on this in two fashions.

1. I place the values that change in cells (P29,Q29,S29), then call the cells/values accordingly as they are needed. This causes the cells to routinely be changing.

2. I create variables; then call the variables in each routine. This does not require any cells to be populated. However, because I address this in numerous subs, I have to add the variable conditions in each sub.

Is one of the above more applicable etc.?

Here is a snip-it of my code:

Public Sub EscrowSetup()
Application.ScreenUpdating = False
Range("P29:V29").ClearContents

Dim YearDUE1 As Long
Dim YearDUE2 As Long
Dim YearPITI_2 As Long
Dim yearDIST As Long

If Range("M33") > 0 Then
  YearDUE1 = Year(Range("M32").End(xlDown).Offset(0, -10))
  YearDUE2 = Year(Range("M32").End(xlDown).Offset(1, -10))

  If Range("T33") > 0 Then
    yearDIST = Range("$T$2222").End(xlUp).Offset(0, -2).Copy
      Range("V29").PasteSpecial Paste:=xlPasteValues '-- for YearDIST --
      Range("M31").End(xlDown).Offset(1, 0).Select
  Else
    Range("V29") = Year(Range("R33").Value)
  End If
End If

If Range("T33") > 0 Then
  YearPITI_2 = Range("T32").End(xlDown).Offset(1, -2)
Else
  YearPITI_2 = Range("R33")
End If

If Range("M33") > 0 Then
  Range("P29") = YearDUE1
  Range("Q29") = YearDUE2
  Range("T29") = YearPITI_2
End If

Thanks for viewing,
Steve

 

[Micron]

Not sure I understand the question so I'll try to answer in general terms. For me, whether or not to use a variable over any other type of reference (e.g.cell/range/sheet) depends on certain factors:
- how often I will end up using it. Variable can be much easier to type repeatedly.
- assigning a long piece of code to a variable can make code easier to read and understand
- variable allows for modifying its value without affecting a source cell

Probably others that aren't coming to mind after just one coffee. I don't see anything real wrong with what you posted but I do wonder about some things:

    yearDIST = Range("$T$2222").End(xlUp).Offset(0, -2).Copy

Why assign a value to a varible if all you're doing is copying from a range then pasting?

      Range("M31").End(xlDown).Offset(1, 0).Select

I'm not seeing a reason for .Select there but maybe you know of one. It may be worth noting that you seldom need to select to do anything with the selection after that. Not saying never. Last but not least, you almost never need .Value as it is the default, but not wrong to use it.
HTH

 

[EssKayKay]

T

Not sure I understand the question so I'll try to answer in general terms. For me, whether or not to use a variable over any other type of reference (e.g.cell/range/sheet) depends on certain factors:
- how often I will end up using it. Variable can be much easier to type repeatedly.
- assigning a long piece of code to a variable can make code easier to read and understand
- variable allows for modifying its value without affecting a source cell

Probably others that aren't coming to mind after just one coffee. I don't see anything real wrong with what you posted but I do wonder about some things:

    yearDIST = Range("$T$2222").End(xlUp).Offset(0, -2).Copy

Why assign a value to a varible if all you're doing is copying from a range then pasting?

      Range("M31").End(xlDown).Offset(1, 0).Select

I'm not seeing a reason for .Select there but maybe you know of one. It may be worth noting that you seldom need to select to do anything with the selection after that. Not saying never. Last but not least, you almost never need .Value as it is the default, but not wrong to use it.
HTH

Thank you Micron for your suggestions. I will clean up the "select" code. I am going to continue with my cell reference as it is working (I'm a big fan of "If it works don't fix it").

Again, much apprciated. . .
SKK

 

[EssKayKay]

Not sure I understand the question so I'll try to answer in general terms. For me, whether or not to use a variable over any other type of reference (e.g.cell/range/sheet) depends on certain factors:
- how often I will end up using it. Variable can be much easier to type repeatedly.
- assigning a long piece of code to a variable can make code easier to read and understand
- variable allows for modifying its value without affecting a source cell

Probably others that aren't coming to mind after just one coffee. I don't see anything real wrong with what you posted but I do wonder about some things:

    yearDIST = Range("$T$2222").End(xlUp).Offset(0, -2).Copy

Why assign a value to a varible if all you're doing is copying from a range then pasting?

      Range("M31").End(xlDown).Offset(1, 0).Select

I'm not seeing a reason for .Select there but maybe you know of one. It may be worth noting that you seldom need to select to do anything with the selection after that. Not saying never. Last but not least, you almost never need .Value as it is the default, but not wrong to use it.
HTH

Hello Micron,

I’m working on cleaning up my “Select/Selection” coding as you noted. I found numerous locations where this was applicable. However, I found a few where I was not sure how to handle it. Here’s a snipit of one example:

If DoIt = vbYes Then
   UnProtect_It

   Range("I33").Select
   Selection.Copy
   Range(Selection, Selection.End(xlDown)).Select
   ActiveSheet.Paste
   Application.CutCopyMode = False

   Range("B" & Range("FormulasRow").Value & ":" & "K" & Range("FormulasRow").Value).Select
   Selection.Copy
   Range("B33:B" & Range("LastDataRow").Value).Select
   Range("B" & Range("LastDataRow").Value).Activate
   ActiveSheet.Paste
   Application.CutCopyMode = False
   Range("B33").Select


‘---- In this situation I was confused as how to handle a WITH statement.

   Range("P32,K6:K13").Select
   Selection.Locked = True
   Selection.FormulaHidden = False
      With Selection.Interior
         .Color = 15984868
         .TintAndShade = 0
   End With

Thanks for viewing,
Steve K.

 

[Micron]

Not sure what you mean. Perhaps this?

Range("P32,K6:K13").Select
With Selection
    .Locked = True
    .FormulaHidden = False
    .Interior.Color = 15984868
    .Interior.TintAndShade = 0
End With

That Looks like another example of where Select is not required. Not a big issue here, but if there is a lot of selecting going on (especially in a loop) then thing will slow down. Without selecting:

With Range("P32,K6:K13")
    .Locked = True
    .FormulaHidden = False
    .Interior.Color = 15984868
    .Interior.TintAndShade = 0
End With

Don't forget - locking cells has no effect if the sheet is not protected (at least AFAIK). That code doesn't unprotect/protect so maybe your ranges are not locked but you think they are.

 

[EssKayKay]

Not sure what you mean. Perhaps this?

Range("P32,K6:K13").Select
With Selection
    .Locked = True
    .FormulaHidden = False
    .Interior.Color = 15984868
    .Interior.TintAndShade = 0
End With

That Looks like another example of where Select is not required. Not a big issue here, but if there is a lot of selecting going on (especially in a loop) then thing will slow down. Without seleHRegarding locking, yes the sheet is locked. I temporarily unlock it to run code, then relock.

 

[EssKayKay]

Not sure what you mean. Perhaps this?

Range("P32,K6:K13").Select
With Selection
    .Locked = True
    .FormulaHidden = False
    .Interior.Color = 15984868
    .Interior.TintAndShade = 0
End With

That Looks like another example of where Select is not required. Not a big issue here, but if there is a lot of selecting going on (especially in a loop) then thing will slow down. Without selecting:

With Range("P32,K6:K13")
    .Locked = True
    .FormulaHidden = False
    .Interior.Color = 15984868
    .Interior.TintAndShade = 0
End With

Don't forget - locking cells has no effect if the sheet is not protected (at least AFAIK). That code doesn't unprotect/protect so maybe your ranges are not locked but you think they are.

Got it - thank you Micron for explaining the WITH code process.
Regarding "locking", yes the sheet is protected.