Question about variables & public variables

[Silverback1992]

Hello All,

So I wanted to change the way I create macros usually (I'm no expert but want to be one), and decided that I want to declare my variables once as public if I use them in multiple modules and subs.

And I had an example like this:

Option Explicit

'Variables only here

Public wsP, wsA, wsR, wsF, wsB, wsUn As Worksheet

Public LRP, LRA, LRR, FDC, x As Long

Sub Variables()

Set wsP = ThisWorkbook.Sheets("Payment Proposal")
    
Set wsA = ThisWorkbook.Sheets("AR")
    
Set wsR = ThisWorkbook.Sheets("Result")
    
Set wsF = ThisWorkbook.Sheets("Formatting Example")

Set wsB = ThisWorkbook.Sheets("Buttons")

Set wsUn = ThisWorkbook.Sheets("Units")
    
LRP = wsP.Cells(Rows.Count, 3).End(xlUp).Row - 1
    
LRA = wsA.Cells(Rows.Count, 1).End(xlUp).Row - 1

LRR = wsR.Cells(Rows.Count, 1).End(xlUp).Row
    
End Sub

And I started to call in this very sub to my other subs and it worked perfectly and I liked it. What came up though is that a variable like LRP or LRA or LRR can change while the macro is running - for example at the beggining it is 1 and later on it is 10. In order for my macro to know that I want to use the 10 not the 1 I had to call in the sub again.

What I would loved is a macro where I declare my variables, and the macro automatically "knows" if the value of that variable changed.

So I decided to think about this for a sec and wrote the following one:

Option Explicit

Sub newdawn()

Dim LR As Long

    LR = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

ThisWorkbook.Sheets("Sheet1").Cells(LR + 1, 1) = "Test"

ThisWorkbook.Sheets("sheet1").Range(Cells(6, 1), Cells(10, 1)) = "###"

ThisWorkbook.Sheets("Sheet1").Cells(LR + 1, 1) = "Test02"

End Sub

Let's say the sheet's A1 cell is equal to "Field Name". The "Test" string goes into A2. Then A6:A10 is filled with "###", then A2 cells overwrites to "Test02". This is logical, however my question is: is there any way to make VBA "refresh" the value of the variable? So in this case "Test02" would go into A11?

In the first code for example as mentioned I had to call the sub 2 times during another sub. Is there a way to declare a variable in a way that I don't have to?

Thank you very much in advance,

Have a wonderful day,

Silverback

 

[Jeffrey Mahoney]

Let me answer this one

 

[Joe4]

I can't say I really understand what you are trying to ask. But if your variables are changing, is there a benefit to declaring them as public variables?
I guess it really matters at what point they change. If they can change in between sub procedures, there really is no reason to declare them publicly.

Also, most of your variable declarations are problematic. This does not do what you think.

Public wsP, wsA, wsR, wsF, wsB, wsUn As Worksheet
Public LRP, LRA, LRR, FDC, x As Long

You are declaring wsUn as a Worksheet, x as Long, and everything else as Variant. Each variable needs its own explicit declaration, or else it defaults to Variant.
So, it should look like:

Public wsP As Worksheet, wsA As Worksheet, wsR As Worksheet, wsF As Worksheet, wsB As Worksheet, wsUn As Worksheet
Public LRP As Long, LRA As Long, LRR As Long, FDC As Long, x As Long

 

[Silverback1992]

Also, most of your variable declarations are problematic. This does not do what you think.

Public wsP, wsA, wsR, wsF, wsB, wsUn As Worksheet
Public LRP, LRA, LRR, FDC, x As Long

You are declaring wsUn as a Worksheet, x as Long, and everything else as Variant. Each variable needs its own explicit declaration, or else it defaults to Variant.
So, it should look like:

Public wsP As Worksheet, wsA As Worksheet, wsR As Worksheet, wsF As Worksheet, wsB As Worksheet, wsUn As Worksheet
Public LRP As Long, LRA As Long, LRR As Long, FDC As Long, x As Long

My thanks, didn't know that.

 

[Jeffrey Mahoney]

Be cautious with Public variables. If you have two SUBS trying to change the values and use them, they may cross over on you. For instance, You set i as a range in one sub, then call another sub to perform some other things and it ALSO uses i for its own purpose, i will change for the calling sub.

I think you are asking if a variable could get auto-populated with a worksheet value if it changes. You could create a sheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A2")) Is Nothing Then
    AVariable = Range("A2").Value
  End If
End Sub

 

[Silverback1992]

Be cautious with Public variables. If you have two SUBS trying to change the values and use them, they may cross over on you. For instance, You set i as a range in one sub, then call another sub to perform some other things and it ALSO uses i for its own purpose, i will change for the calling sub.

I think you are asking if a variable could get auto-populated with a worksheet value if it changes. You could create a sheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A2")) Is Nothing Then
    AVariable = Range("A2").Value
  End If
End Sub

What happened is that in another sub I called in the Variables sub right away. At that point LRR's value was 1. While running, the code itself in that sub changed LRR's value to 14. I needed to use that 14 for my next couple of lines there, but in order to do so I had to call Variables sub in again.