PDA

View Full Version : Run another Sub from within active Sub



YellowLabPro
06-16-2007, 07:20 AM
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
06-16-2007, 07:38 AM
Are you needing to pass a paramater?
eg
Option Explicit

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

YellowLabPro
06-16-2007, 08:32 AM
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
06-16-2007, 08:56 AM
The basic syntax is just



Sub Parent()
Child
End Sub

Sub Child()
'do something
End Sub


although I prefre an explicit call




Sub Parent()
Call Child
End Sub

Sub Child()
'do something
End Sub


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




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

Sub Child(ByVal Msg As String)
MsgBox Msg
End Sub


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




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

YellowLabPro
06-16-2007, 12:01 PM
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

YellowLabPro
06-16-2007, 12:12 PM
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
06-16-2007, 04:59 PM
Problem resolved.
Module and Sub were both named TrimRecords