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]

Seems like you could just do this using the function I suggested...

CountCell1 = CntCell(Range("P11").Formula, itemcell1)

Dave

 

[pjmorris]

I get what you need to do - it was a bit late last night. Put the following code in a normal module, not the 'ThisWorkBook' and add additional code as necessary. You'll need to add the remainder of your variables, check that the worksheets statement in SetVariables is correct and then add whatever additional code is needed to the feedback procedures. For info, I think that the what you want to do is a very sensible strategy since if you ever need to change how a variable is calculated you only need to do so once.

Option Explicit

Public countCell1 As Integer
Public countCell2 As Integer
Public myTxt As String
'add further variables here

Sub SetVariables(cCell As String)
    myTxt = Worksheets("Sheet1").Range(cCell).Formula
    countCell1 = (Len(myTxt) - Len(Replace(myTxt, itemCell1, ""))) / Len(itemCell1)
    countCell2 = (Len(myTxt) - Len(Replace(myTxt, itemCell2, ""))) / Len(itemCell2)
    
    'add remainder of variable setting code here.
End Sub

Sub Feedback_P10()
    SetVariables ("P10")
    'your code goes here
End Sub

Sub Feedback_P11()
    SetVariables ("P11")
    'your code goes here
End Sub

Hope this helps

 

[ChristineJ]

Seems like you could just do this using the function I suggested...

CountCell1 = CntCell(Range("P11").Formula, itemcell1)

Dave

Thanks for this follow up. I am working to integrate it into my code.

 

[ChristineJ]

I get what you need to do - it was a bit late last night. Put the following code in a normal module, not the 'ThisWorkBook' and add additional code as necessary. You'll need to add the remainder of your variables, check that the worksheets statement in SetVariables is correct and then add whatever additional code is needed to the feedback procedures. For info, I think that the what you want to do is a very sensible strategy since if you ever need to change how a variable is calculated you only need to do so once.

Option Explicit

Public countCell1 As Integer
Public countCell2 As Integer
Public myTxt As String
'add further variables here

Sub SetVariables(cCell As String)
    myTxt = Worksheets("Sheet1").Range(cCell).Formula
    countCell1 = (Len(myTxt) - Len(Replace(myTxt, itemCell1, ""))) / Len(itemCell1)
    countCell2 = (Len(myTxt) - Len(Replace(myTxt, itemCell2, ""))) / Len(itemCell2)
   
    'add remainder of variable setting code here.
End Sub

Sub Feedback_P10()
    SetVariables ("P10")
    'your code goes here
End Sub

Sub Feedback_P11()
    SetVariables ("P11")
    'your code goes here
End Sub

Hope this helps

Brilliant - this is it. It eliminates all the redundancy of repeating the large block of identical code I have. What an elegant solution! Thanks so much for sticking with me on this.

 

[pjmorris]

Just a thought, but for further refinement if, as I suspect, much of the code is the same in the Feedback Procedures then put all the declarations and code in one procedure, but for specific data simply call the code with the relevant cell string, "P10" for example, as the supplied variable. You might be able to make it much easier to read and debug.

PS. If you happy with the solution above can you mark it as an answer.

HTH

 

[ChristineJ]

Just a thought, but for further refinement if, as I suspect, much of the code is the same in the Feedback Procedures then put all the declarations and code in one procedure, but for specific data simply call the code with the relevant cell string, "P10" for example, as the supplied variable. You might be able to make it much easier to read and debug.

PS. If you happy with the solution above can you mark it as an answer.

HTH

Yes, this I had done. I do call a macro in each one for the common elements. Thanks for the help.