Declare variables to use in several macros in the same module

[ChristineJ]

The variables in the code below count occurrences of different strings that appear within the formula in cell P10. (The list is much longer; this is just a sample.)

The variables countCell1, countCell2, countFunc6, countFunc12, countChar4, and countChar8 are Public Const in a different module, such as Public Const itemCell1 As String = "D9"

I need to use these same variables for six more macros in the same module - Sub Feedback_P11(), Sub Feedback_P12(), etc.

Sub Feedback_P10()
myTxt = Range("P10").Formula

countCell1 = (Len(myTxt) - Len(Replace(myTxt, itemCell1, ""))) / Len(itemCell1) 
countCell2 = (Len(myTxt) - Len(Replace(myTxt, itemCell2, ""))) / Len(itemCell2)
countFunc6 = (Len(myTxt) - Len(Replace(myTxt, itemFunc6, ""))) / Len(itemFunc6)
countFunc12 = (Len(myTxt) - Len(Replace(myTxt, itemFunc12, ""))) / Len(itemFunc12)
countChar4 = (Len(myTxt) - Len(Replace(myTxt, itemChar4, ""))) / Len(itemChar4)
countChar8 = (Len(myTxt) - Len(Replace(myTxt, itemChar8, ""))) / Len(itemChar8)

countEndD = (Len(myTxt) - Len(Replace(myTxt, "D(", ""))) / Len("D(")
countEndM = (Len(myTxt) - Len(Replace(myTxt, "M(", ""))) / Len("M(")
countEndN = (Len(myTxt) - Len(Replace(myTxt, "N(", ""))) / Len("N(")
countEndR = (Len(myTxt) - Len(Replace(myTxt, "R(", ""))) / Len("R(")
countEndS = (Len(myTxt) - Len(Replace(myTxt, "S(", ""))) / Len("S(")

TotalP10 = countEndD + countEndM + countEndN + countEndR + countEndS
ResultP10 = countCell1 + count Func12 + TotalP10

Right now I am recopying all of these same variables into each of the six additional macros, so there is a lot of redundancy.

I tried declaring and listing the variables at the top of the module above the macros, but it is not working. Sample:

Dim myTxt As String

Dim countCell1 As Integer
Dim countCell2 As Integer

countCell1 = (Len(myTxt) - Len(Replace(myTxt, itemCell1, ""))) / Len(itemCell1)
countCell2 = (Len(myTxt) - Len(Replace(myTxt, itemCell2, ""))) / Len(itemCell2)

Could this be adapted so I can declare all of these variables just once and them use them, as needed, in different macros in the same module?

Thanks for the help! C

 

[NdNoviceHlp]

Hi C. Instead of Dim, use Public before the variables at the top of the module. They are then available to the whole project. Note: do not Dim them again in separate Subs. HTH. Dave

 

[ChristineJ]

I changed the variables at the top of the module to

Public myTxt As String
Public countCell1 As Integer
Public countCell2 As Integer

countCell1 = (Len(myTxt) - Len(Replace(myTxt, itemCell1, ""))) / Len(itemCell1)
countCell2 = (Len(myTxt) - Len(Replace(myTxt, itemCell2, ""))) / Len(itemCell2)

What I was hoping to accomplish was to have something like the following work, without entering in countCell1 = (Len(myTxt) - Len(Replace(myTxt, itemCell1, ""))) / Len(itemCell1) in the macro.

Sub Feedback_P10()
myTxt = Range("P10").Formula
Range("A5").Value = countCell1

It would be great if there is a way to put all those "count" statements shown in my first code in one place and then use them in multiple macros in the module.

Thanks.

 

[NdNoviceHlp]

My misunderstanding. You can trial...

Public Const countCell1 = (Len(myTxt) - Len(Replace(myTxt, itemCell1, ""))) / Len(itemCell1)

You may need to also declare the itemcell1 as public? HTH. Dave

 

[ChristineJ]

Thanks for the try. However, it hangs up on the first "Len" and wants a constant for that. I'll keep trying! Appreciate the suggestions.

 

[NdNoviceHlp]

Yes I suppose it would hang up if the myTxt and itemcell1 variables have no values. Maybe you just want to create a function...

Function CntCell(MTxt As String, ICell As String)
CntCell = (Len(MTxt) - Len(Replace(MTxt, ICell, ""))) / Len(ICell)
End Function

To use..

CountCell1 = CntCell(MyTxt, itemcell1)
CountCell2 = CntCell(MyTxt, itemcell2)
'etc.

Dave

 

[dreid1011]

Thanks for the try. However, it hangs up on the first "Len" and wants a constant for that. I'll keep trying! Appreciate the suggestions.

If I recall, you cannot set a variable outside of a sub. You can declare them outside, but not assign a value to them.

 

[pjmorris]

Hi, I think you have a couple of different issues. In your original example you first mention myTxt in

myTxt = Range("P10").Formula

There is no explicit declaration of myTxt and hence Excel will create a 'variant' variable capable of holding anything rather than a string. You declare it outside of the procedure with 'Dim myTxt as String' to create a string variable visible to all other procedures in the module. If you use 'Public' it will be available to any procedure throughout the project. You could define the variables outside a procedure as stated, then create a macro that runs once. For example, in the 'ThisWorkbook' module put:

Option Explicit

Public countCell1 As Integer
Public countCell2 As Integer
Public myTxt As String

Private Sub Workbook_Open()
    myTxt = Range("P10").Formula
    
    countCell1 = (Len(myTxt) - Len(Replace(myTxt, itemCell1, ""))) / Len(itemCell1)
    countCell2 = (Len(myTxt) - Len(Replace(myTxt, itemCell2, ""))) / Len(itemCell2)
End Sub

What else would I do: Ans:

1. add the phrase 'option explicit' at the head of the module. That will force all variables to be declared so they cannot be created 'on the fly' as you appear to be doing. This ensures all variables are type checked and makes your code much more robust.
2. be aware that by declaring the variables at the head of the module they are 'in-scope' for all procedures in that module. The flip side of that is that they take their value with them. So 'myTxt' in one procedure will have the same value in another. Perhaps more importantly if the second procedure changes its value, when the first procedure accesses the data again it may not have what you expect.

PS. You can force 'Option Explicit' for all new modules by ticking the 'Require Variable Declaration' in the Editor tab of the options dialogue of the VBA editor. I would strongly recommend doing so.

HTH

 

[ChristineJ]

If I recall, you cannot set a variable outside of a sub. You can declare them outside, but not assign a value to them.

Thanks for that. I have now learned that along the way. Appreciate it.

 

[ChristineJ]

Hi, I think you have a couple of different issues. In your original example you first mention myTxt in

myTxt = Range("P10").Formula

There is no explicit declaration of myTxt and hence Excel will create a 'variant' variable capable of holding anything rather than a string. You declare it outside of the procedure with 'Dim myTxt as String' to create a string variable visible to all other procedures in the module. If you use 'Public' it will be available to any procedure throughout the project. You could define the variables outside a procedure as stated, then create a macro that runs once. For example, in the 'ThisWorkbook' module put:

Option Explicit

Public countCell1 As Integer
Public countCell2 As Integer
Public myTxt As String

Private Sub Workbook_Open()
    myTxt = Range("P10").Formula
   
    countCell1 = (Len(myTxt) - Len(Replace(myTxt, itemCell1, ""))) / Len(itemCell1)
    countCell2 = (Len(myTxt) - Len(Replace(myTxt, itemCell2, ""))) / Len(itemCell2)
End Sub

What else would I do: Ans:

1. add the phrase 'option explicit' at the head of the module. That will force all variables to be declared so they cannot be created 'on the fly' as you appear to be doing. This ensures all variables are type checked and makes your code much more robust.
2. be aware that by declaring the variables at the head of the module they are 'in-scope' for all procedures in that module. The flip side of that is that they take their value with them. So 'myTxt' in one procedure will have the same value in another. Perhaps more importantly if the second procedure changes its value, when the first procedure accesses the data again it may not have what you expect.

PS. You can force 'Option Explicit' for all new modules by ticking the 'Require Variable Declaration' in the Editor tab of the options dialogue of the VBA editor. I would strongly recommend doing so.

HTH

I really appreciate the detailed explanation, especially the recommendation to use Option Explicity. I've cleaned up my code based on your suggestions.

I just want to confirm that if I want a second Sub for where myTxt = Range("P11").Formula, I would have to repeated the following:

countCell1 = (Len(myTxt) - Len(Replace(myTxt, itemCell1, ""))) / Len(itemCell1)
countCell2 = (Len(myTxt) - Len(Replace(myTxt, itemCell2, ""))) / Len(itemCell2)

I cannot share set a variable that I can share among subs, correct? (I have a long list of these and was trying to avoid the repetition.)

Thanks so much. Learned a lot from your response.