Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

call module from a module

0

 Using the call to call a module from a module I get compile error expected variable or procedure not module. trying  to call a module to clear a form.

'Clear Deposit Form

   Call Clr_DepForm

Sub Clr_DepForm()

    Dim Form As Worksheet

    Dim Data23 As Worksheet

   Set SourceSheet = Sheets("Form")

   Set DataSheet = Sheets("Data23")

'Clear Deposit Form

    Range("B4").ClearContents

    Range("F6").Value = 0

    Range("F8:H16").Value = 0

    Range("F18:H18").Value = 0

    Range("F20:H24").Value = 0

    Range("F27:H29").Value = 0

    Range("F32:H34").Value = 0

    Range("F36:H36").Value = 0

    Range("M6:O25").Value = 0

    Range("I27:N29").ClearContents

    Range("I35:L36").ClearContents

        Cells(4, 2).Select

End Sub

excel
MS Office Version: Office 365
posted by: JFKennedy (rep: 2) on Sat Jul 15, 2023 at 10:35 am
Answer
Discuss

Discussion

Hi JF and welcome to the Forum.

I'm not clear where your call is (in which procedure or module). Please edit your original question to attach a representative Excel file, using the Add Files... button and I'll try to respond.

Also I notice that you set two sheets but then fail to reference them in yor Range().Clear lines (so they would act on ranges/ cells on the currently active sheet only). Which sheet do you want to clear? I'm guessing Form.
John_Ru (rep: 6142) Jul 15, '23 at 11:02 am
@JFKennedy
I agree with John. A representitive file will go a long way to help us help you.
What you are wanting to do is a common coding method, but we need a sample file so we can see where the error is.
WillieD24 (rep: 557) Jul 15, '23 at 10:40 pm
@JFKennedy- if you don't help us (see above), we can't help you! 
John_Ru (rep: 6142) Jul 19, '23 at 5:52 am
Yes I'trying to clear the Form without writing the clear code over again. Here is  the module that is calling the clear module.

Sub Find_DepForm()       Dim SourceSheet As Worksheet     Dim DataSheet As Worksheet     Dim Rng As Range     Dim SearchValue As Variant     Dim DataCol As Range     Dim RecordRow As Integer       Set SourceSheet = Sheets("Form")     Set DataSheet = Sheets("Data23")     Set DataCol = DataSheet.Range("A:A")           'Clear Deposit Form      Call Clr_DepForm
JFKennedy (rep: 2) Jul 21, '23 at 9:45 pm
@JFKennedy
The line “Call Clr_DepForm” can reside inside your “Sub Find_DepForm()” macro as you have it but the entire code of “Sub Clr_DepForm()” cannot. It must be outside of the macro calling it. It can be in the same module but as a separate macro. You can use my suggestion below or shorten it to:
Sub Clr_DepForm_NEW2()
 
'Clear Deposit Form
 
Sheets("Form").Activate
 
    Range("B4,I27:N29,I35:L36").ClearContents
    
    Range("F6,,F8:F16,F17:H18,F20:H24,F27:H29,F32:H34,F36:H36,M6:O25").Value = 0
    
 
        Cells(4, 2).Select   ' or use Range("B4").Select to keep code consistant
 
End Sub
WillieD24 (rep: 557) Jul 21, '23 at 10:24 pm
Add to Discussion

Answers

0
Selected Answer

@JFKennedy,

Even though you have not provided a sample file here are the problems I see with the code you have presented.

1) Your  " Call Clr_DepForm " statement needs to be inside a sub proceedure and not freestanding.

2) Your 2 "Dim" statements seem unnecessary as "Form" and "Data23" seem to be sheet names.

3) Your 2 "Set" statements throw compile errors because "SourceSheet" and "DataSheet" are undefined variables. (eg: Dim SourceSheet as Worksheet,  Dim DataSheet as Worksheet)

4) After declaring variables you should then have the line: " Sheets("Form").Activate " so the macro knows which sheet to act on.

5) Using " .ClearContents " and " .Value = 0 " have simlar results with one small difference. They both remove or overwrite whatever is in the specified cell/range.        " .ClearContents " will remove whatever is in the cell, formulas included, and the cell will be blank; " .Value=0 " overwrites whatever is in the cell, formulas included, and the cell now displays " 0 ". Is this the result you intended?

With all that being said, I would suggest you change your macro to:

Sub Clr_DepForm_NEW()

'Clear Deposit Form

Sheets("Form").Activate

    Range("B4").ClearContents
    Range("F6").Value = 0
    Range("F8:H16").Value = 0
    Range("F18:H18").Value = 0
    Range("F20:H24").Value = 0
    Range("F27:H29").Value = 0
    Range("F32:H34").Value = 0
    Range("F36:H36").Value = 0
    Range("M6:O25").Value = 0
    Range("I27:N29").ClearContents
    Range("I35:L36").ClearContents

        Cells(4, 2).Select   ' or use Range("B4").Select to keep code consistant

End Sub

If this solves your issue please mark my answer as selected.

Cheers   :-)

posted by: WillieD24 (rep: 557) on Wed Jul 19, 2023 at 3:47 pm
Discuss

Discussion

Did that work for you, JFK? If so, please mark Willie's Answer as Selected. 
John_Ru (rep: 6142) Jul 25, '23 at 1:04 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login