CODEWITHSUNDEEP
vbaexcel
dorichidori
dorichidori

Reputation: 5

Excel VBA Calling modules

So I have put together this code, but I am having trouble running it. I tried calling on the sub routines, but nothing was happening. I am stuck on how to call the modules I have created. I have also created a MainMenu() sub and I am wondering if that was maybe a mistake and I should have run my MainMenu from sheet1. Maybe someone has a better idea.

My original plan was to create a commandbutton where if clicked module1, which contains the MainMenu() would run.

Sub CommandButton1()

End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Within module1 there is a loop that runs and calls the other three modules

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim row_sum As Integer

Sub Main()
'Finding the last row
row_sum = 0

'row check upto 5,000
For k = 1 To 5000
    ' if cells in k are blank then the row_check = 0, else row_check = 1
    If CStr(Cells(k, 1)) = "" Then row_check = 0 Else row_check = 1
    'adding the total of row_check to row_sum
    row_sum = row_sum + row_check
Next k
For j = 2 To row_sum
    Call Module2
    Call Module3
    Call Module4

Next j

End Sub

I can not figure out where I am going wrong exactly. if anyone sees something I am missing please let me know. I appreciate any help

Upvotes: 0

Views: 10732

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

This is all the code we needed to see:

Call Module2
Call Module3
Call Module4

=)

You don't "call" modules, modules are containers for procedures and functions. What you "call", is procedures and functions. So if you have Sub DoSomething() in Module5, you can invoke it like this:

Module5.DoSomething

Or, if there's no DoSomething anywhere else:

DoSomething

Just like you would invoke VBA.Interaction.MsgBox like MsgBox "Hello!". Note that you don't need to have a Call keyword anywhere for it to work.

Your Module2.SourceApprove procedure has parameters. So you can't just do SourceApprove or Module2.SourceApprove, VBA will complain that a parameter is not optional.

So you supply the parameters in a comma-separated list of values:

Module2.SourceApprove "first", "second", "third", 42

These could be variables, too.

Dim foo As Long
foo = 42

Module2.SourceApprove "first", "second", "third", foo

Consider using meaningful names for your parameters - that way IntelliSense will help you know what values to provide. You can (should) also specify a type for the parameters, so that "first" can't be provided as a value for a parameter that requires a number.

Sub SourceApprove(ByVal i As Long, ByVal j As Long, ByVal k As Long, ByVal row_sum As Double)

Upvotes: 5

Related Questions