Run another Sub from within active Sub

[YellowLabPro]

I am trying to call/run a sub from another. The only way I can get it to work is if I place the second sub in the same procedure, and use this line:
TrimRecords.
What I am trying to do is leave the other sub outside of the activesub and just call it.
They are both in the same VBA Project.
I have tried the following lines of code:

1) TrimRecords
2) Call TrimRecords
3) Call "TrimRecords"
4) Application.Run TrimRecords
5) Application.Run "TrimRecords"


Thanks,

YLP

[mdmackillop]

Are you needing to pass a paramater?
eg
Option Explicit

[VBA]Sub Test()
Dim rng As Range
Set rng = Range("A1:A10")
TrimRecords rng
End Sub


Sub TrimRecords(rng As Range)
Dim cel As Range
For Each cel In rng
cel.calue = Trim(cel)
Next
End Sub
[/VBA]

[YellowLabPro]

Hi Malcolm,
I will have to load up your code and experiment.
But not sure what you mean by your question-

Are you needing to pass a paramater?
eg
Option Explicit

Rather than hardcode the Trim function in the module, I was going to just call it for other procedures.
Am I missing your point?

[Bob Phillips]

The basic syntax is just

[vba]

Sub Parent()
Child
End Sub

Sub Child()
'do something
End Sub
[/vba]

although I prefre an explicit call


[vba]

Sub Parent()
Call Child
End Sub

Sub Child()
'do something
End Sub
[/vba]

As for parameters if you want the child procedure to work on some information passed from parent that is a parameter


[vba]

Sub Parent()
Call Child("Are you in?")
End Sub

Sub Child(ByVal Msg As String)
MsgBox Msg
End Sub
[/vba]

If you pass it byref, any changes made in Child are reflected in Parent


[vba]

Sub Parent()
Dim myval as long
myVal = 17
MsgBox "myVal in Parent before call " & myVal
Call Child(myVal)
MsgBox "myVal in Parent after call " & myVal
End Sub

Sub Child(ByRef val As Long)
MsgBox "myVal in Child b change " & Val
val = val * 3
MsgBox "myVal in Child after change " & Val
End Sub
[/vba]

[YellowLabPro]

[VBA]Option Explicit
Sub TrimRecords()
Dim Wss As Worksheet
Dim i As Long
Dim LRow As Long
Set Wss = ActiveSheet
LRow = Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To LRow
Wss.Cells(i, 4) = WorksheetFunction.Trim(Wss.Cells(i, 4))
Next i
End Sub[/VBA]

[YellowLabPro]

Hello Xld,
I just got back to this. I sent Mdmackillop my procedure before I saw you had posted up. I am working on this w/ MD currently, and will go back to yours when we finish to see the differences.

thanks,

Doug

[mdmackillop]

Problem resolved.
Module and Sub were both named TrimRecords