Can variables hold methods?

excelnoob001

New Member
Joined
Jul 24, 2019
Messages
26
I am new to VBA.

I am trying to make a variable contain a method, so my code would become more abstract.

Rich (BB code):
Dim wbS As Workbook 

Dim Source_File As String
Source_File = ActiveWorkbook.Name
Set wbS = Workbooks(Source_File)

Set wsS1 = wbS.Sheets("A")
Set wsS2 = wbS.Sheets("B")
Set wsS3 = wbS.Sheets("C")
Set wsS4 = wbS.Sheets("D")
Set wsS5 = wbS.Sheets("E")
Set wsS6 = wbS.Sheets("F")
Set wsS7 = wbS.Sheets("G")
Set wsS8 = wbS.Sheets("H")

'the above is the context to my problem

Dim rep As String
    
                If passedA = True Then
                    Set rep = wsS1
                    
                ElseIf passedB = True Then
                    Set rep = wsS2
                
                ElseIf passedC = True Then
                    Set rep = wsS3
                    
                ElseIf passedD = True Then
                    Set rep = wsS4
                
                ElseIf passedE = True Then
                    Set rep = wsS5
                    
                ElseIf passedF = True Then
                    Set rep = wsS6
                    
                ElseIf passedG = True Then
                    Set rep = wsS7
                    
                ElseIf passedH = True Then
                    Set rep = wsS8
                    
                ElseIf passedI = True Then
                    Set rep = wsS9
                
                End If
            
                Set USS = rep.Range("A7", Range("B7").End(xlDown)) 
                            USS.Copy
                            ActiveSheet.Paste Destination:=wsMT.Range("A3") 'the destination of the paste
                            Application.CutCopyMode = False


                 Set X = rep.Range(...)
Set Y = rep.Range(…)
Set XY = rep.Range(...)
Set YY = rep.Range(…) Set... blah blah blah <strike></strike>​



I am trying to make it so that rep can change and make the formulas more dynamic, instead of having a specific formula for each condition.

Thank you for your help. :D
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top