Link to home
Start Free TrialLog in
Avatar of Bright01
Bright01Flag for United States of America

asked on

Calling a Sub Routine

EE Pros.,

I have what I hope is a rather easy question.  I have a macro that clears a number of cells and calls other subroutines that also clear cells on different Worksheets.  When I went to add a "call", it gave me an error I couldn't explain but clearly related to the ability to call a subroutine that is part of a particular WS.

Sub Clear_All()
Dim I As Integer
With Worksheets("Sheet1")
    .Range("N13:N19").ClearContents
    End With
    Call clearQuestionaireFields
    Call deleteButOneTemplate
    Call Hidealltabs
End Sub

All of these Subs are in a single Module.

In a particular Worksheet, this Macro allows me to clear the contents IN THAT WORKSHEET.

Sub ResetPrioritiesAll()
   Range(mc_sSELECTED_OUTCOMES).ClearContents
   Range(mc_sOUTPUT_CELLS).ClearContents
    Range("B2").Activate
    Range("B4").Activate
    Range("B2").Activate
End Sub

Here is my question.  What line of code do I use in the "Clear_All" Sub that executes the "ResetPrioritiesAll" Sub?

Thank you,

B.
Avatar of Steve
Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

you will have to have the ResetPrioritiesAll as a sub in a module (not worksheet) then reference the worksheet to work on eg:

Sub Clear_All()
Dim I As Integer
Set ws = thisworkbook.sheets("Sheet1")
With ws
    .Range("N13:N19").ClearContents
End With
    
    Call clearQuestionaireFields
    Call deleteButOneTemplate
    Call Hidealltabs
    
    Call  ResetPrioritiesAll(ws)

End Sub

Sub ResetPrioritiesAll(ws as worksheet)
    ws.Range(mc_sSELECTED_OUTCOMES).ClearContents
    ws.Range(mc_sOUTPUT_CELLS).ClearContents
    ws.Range("B2").Activate
    ws.Range("B4").Activate
    ws.Range("B2").Activate
End Sub

Open in new window

You can call a subroutine in a worksheet as long as it is not marked 'Private'. Each sheet has a code name, by default Sheet1, Sheet2, etc, and a display name which is the name you have give the worksheet.  If you have a setup like this screenshot, with a sheet with code name Sheet1, called 'My sheet name', you can call a routine called 'LocalSubroutine' in one of 2 ways, like this:
Sub P()

    ' using code name
    Call Sheet1.LocalSubroutine
    
    ' using display name
    Call ActiveWorkbook.Worksheets("My sheet name").LocalSubroutine

End Sub

Open in new window

User generated imageHaving said that you don't need the routine to be in a worksheet - your code will work with whatever sheet is active (unless your named ranges mc_sSELECTED_OUTCOMES are scoped local to the worksheet.
Avatar of Bright01

ASKER

The Barman,

I tried your solution by putting the original code into a Module and referencing it the way you said in order to activate the Sub.  I got a "Type Mismatch".  

Here is the code in the Module:

Sub Clear_CA_All()
Dim I As Integer
Set ws = ThisWorkbook.Sheets("Splash")
With ws
    .Range("N13:N19").ClearContents
End With
   
    Call clearCAQuestionaireFields
    Call deleteButOneTemplate
    Call Hidealltabs
    Call ResetPrioritiesAll("ws")

End Sub

Here is the code in the same Module:

Sub ResetPrioritiesAll(ws As Worksheet)
    ws.Range(mc_sSELECTED_OUTCOMES).ClearContents
    ws.Range(mc_sOUTPUT_CELLS).ClearContents
    ws.Range("B2").Activate
    ws.Range("B4").Activate
    ws.Range("B2").Activate
End Sub

Am I missing something?

Thank you,  B.
Andrewssd3,

I tried both approaches you recommended and got a debug error on both;

Sub Clear_CA()
Dim I As Integer
With Worksheets("Splash")
    .Range("N13:N19").ClearContents
    End With
    Call clearCAQuestionaireFields
    Call deleteButOneTemplate
    Call Hidealltabs
'    Call Sheet14.ResetPrioritiesAll
'Call ActiveWorkbook.Worksheets("Priorities").ResetPrioritiesAll
End Sub

Any ideas?

B.
do not use quote marks:
you may also need to dim it

dim ws as worksheet

Call ResetPrioritiesAll(ws)

Open in new window

What error did you get? It should work.  It might help us both if you could post the whole workbook.
Bar_Man,

I get a "duplication error" now.  Where do I put the Dim WS statement?  I think it's in the wrong place.  I also commented it out and still got the error.


Sub Clear_CA_All()
Dim I As Integer
Set ws = ThisWorkbook.Sheets("Splash")
With ws
    .Range("N13:N19").ClearContents
End With
   
    Call clearCAQuestionaireFields
    Call deleteButOneTemplate
    Call Hidealltabs
Dim ws As Worksheet
    Call ResetPrioritiesAll(ws)

End Sub
Andrew,

The Workbook is rather large and has over 19 Modules and numerous sheets.  You said in your post;  

(unless your named ranges mc_sSELECTED_OUTCOMES are scoped local to the worksheet.

The ranges are created within the WS so perhaps that may also be a problem..........

Thoughts?

B.
My original post answered your question, which was how to call a routine in a worksheet module.  However, I don't think what you're trying to do needs a routine in a worksheet - you should probably do what the Barman suggested and bring the routine into your main module.

You need to put the Dim ws before the first time you refer to ws, then it should work.  I'm not sure what the duplication error is.  There is a message "duplicate declaration in current scope", which you would get if you already have a Dim ws either in the same routine or at module level (i.e. at the top of the module, before any Subs or Functions). You need to be more specific about the error messages you are getting, and look at the exact line that causes the error.
May I have the same Routine in both a module and a Worksheet or does it have to carry a different name?

Also, can I have a Dim WS statement and a Dim Integer statement in the same Sub?

Thank you,

B.
It shouldn't be this hard.  I have put the code for "reset priorities" (that was in the Priorities WS), in Module 1 with a different name (ResetPrioritiesAllModule) and added the referral in the Clear_CA Sub.

Sub Clear_CA()
Dim I As Integer
With Worksheets("Splash")
    .Range("N13:N19").ClearContents
    End With
    Call clearCAQuestionaireFields
    Call deleteButOneTemplate
    Call Hidealltabs
    Call ResetPrioritiesAllModule
End Sub

Sub ResetPrioritiesAllModule()
Dim ws As Worksheet
With Worksheets("Priorities")
   Range(mc_sSELECTED_OUTCOMES).ClearContents
   Range(mc_sOUTPUT_CELLS).ClearContents
    Range("B2").Activate
    Range("B4").Activate
    Range("B2").Activate
    Call ResetPrioritiesAllModule
    End With
'    Call Sheet14.ResetPrioritiesAll
'Call ActiveWorkbook.Worksheets("Priorities").ResetPrioritiesAll
End Sub

I get an error:  on the line Range(mc_sSELECTED_OUTCOMES).Clearcontents

Keep in mind, I do not get any errors when I reset the Priorities and I'm in the Priorities WS.

Thank you,

b.
Try this:
Private Const mc_sSELECTED_OUTCOMES As String = "SelOut"
Private Const mc_sOUTPUT_CELLS As String = "Output"
Sub Clear_CA()

    Dim I As Integer
    Dim ws As Worksheet
    
    With Worksheets("Splash")
        .Range("N13:N19").ClearContents
    End With
    
'    Call clearCAQuestionaireFields
'    Call deleteButOneTemplate
'    Call Hidealltabs
    
    Set ws = ActiveWorkbook.Worksheets("Priorities")
    Call ResetPrioritiesAllModule(ws)
    
End Sub

Sub ResetPrioritiesAllModule(ByRef wks As Worksheet)

    With wks
        .Activate
        wks.Range(mc_sSELECTED_OUTCOMES).ClearContents
        wks.Range(mc_sOUTPUT_CELLS).ClearContents
        
        wks.Range("B2").Activate
    
    End With
    

End Sub

Open in new window

If this works, then share the points with the Barman, as this is basically what he suggested.  If not, tell us exactly what error message you get, and on what line.

I'm assuming you already have something like the 2 Const declarations at the top, so you can remove them or replace them with yours.

Stuart
OK......I put the entire construct as you had it in a new module and tried to run it.

The error I got is now:  

Run Time Error "1004"

Method Range of Object_Worksheet Failed

Then if you go to Debug, the error is on line:

 wks.Range(mc_sSELECTED_OUTCOMES).ClearContents

Does that help?

B.

Here's the code I placed in the new module:

Private Const mc_sSELECTED_OUTCOMES As String = "SelOut"
Private Const mc_sOUTPUT_CELLS As String = "Output"
Sub Clear_CAnew()

    Dim I As Integer
    Dim ws As Worksheet
   
    With Worksheets("Splash")
        .Range("N13:N19").ClearContents
    End With
   
'    Call clearCAQuestionaireFields
'    Call deleteButOneTemplate
'    Call Hidealltabs
   
    Set ws = ActiveWorkbook.Worksheets("Priorities")
    Call ResetPrioritiesAllModule(ws)
   
End Sub

Sub ResetPrioritiesAllModule(ByRef wks As Worksheet)

    With wks
        .Activate
        wks.Range(mc_sSELECTED_OUTCOMES).ClearContents
        wks.Range(mc_sOUTPUT_CELLS).ClearContents
       
        wks.Range("B2").Activate
   
    End With
   

End Sub
Error 1004 is due (most likely) to having the range select hidden or in filter mode.

You may need to clear fileters before the clearcontents.
ASKER CERTIFIED SOLUTION
Avatar of andrewssd3
andrewssd3
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Andrew,

Thank you very much.  Finally got to work based on your input.  Appreciate the commentary.

B.