CODEWITHSUNDEEP
vbaexcel
EXPGDave
EXPGDave

Reputation: 11

Problems passing parameters between subs

I have my main sub as per below:

Option Explicit
Public y As String
Public xCell As Range
Sub BenAppMr()

Call SearchFor("Award")
Call ConvertToLetter(xCell)
MsgBox "The column letter is " & y
End Sub

and then my two other subs which I call from above:

Sub SearchFor(z As String)
xCell = Cells.Find(What:=z, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
End Sub

and 

Sub ConvertToLetter(x As Range)
y = Split(x.Address(1, 0), "$")(0)
End Sub

Am I missing something here? I can't quite understand why this won't work.

I'm looking to search of "Award" in my excel sheet and convert the column number to a letter. I'm looking to pass these parameters as I'll be calling a few searches and a few conversions in my main sub (once it's working)

It's been a long time since I used this kind of set up, normally I just call procedures without passing a parameter but it would be a lot cleaner this way.

Any help would be much appreciated.

Upvotes: 1

Views: 61

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

Using Sub's to set Global variables is not a good coding pattern - you would be much better off using functions to return the values directly to the calling code:

Sub BenAppMr()

    Dim y As String, xCell As Range

    Set xCell = SearchFor("Award")

    If Not xCell Is Nothing Then
        y = ConvertToLetter(xCell)
        MsgBox "The column letter is " & y
    Else
        MsgBox "Search value not found!"
    End If

End Sub

Function SearchFor(z As String) As Range
    Dim xCell As Range

    Set xCell = ActiveSheet.Cells.Find(What:=z, After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False)

    Set SearchFor = xCell
End Function

Function ConvertToLetter(x As Range) As String
    ConvertToLetter = Split(x.Address(1, 0), "$")(0)
End Function

...and use Set for object-type variables as Rory pointed out.

Upvotes: 1

Related Questions