CODEWITHSUNDEEP
excelvba
Ken Ingram
Ken Ingram

Reputation: 1598

How to pass a variable to a function?

I have a Sub called sortRows(location As String, wksht As String)

The parameter wksheet is being passed to sortRows from another subroutine that is looping through spreadsheets.

I keep getting the error "Compile Error. "Expected: = "

After some research I discovered ByRef directive(?) and re-wrote the Sub as sortRows(location As String, ByRef wksht As String)

But I still get the same error when calling it in the worksheet loop.

The Sub works fine, but I can't seem to get the call to it working properly.

I have tried two ways to call the Sub: sortRows ("Florida",sheetname) sortRows ("Florida", wksht:=sheetname)

Both ways result in a "Compile Error"

Upvotes: 0

Views: 124

Answers (1)

brettdj
brettdj

Reputation: 55672

It is a little hard without seeing your full code.

But I suggest you try passing your worksheet variable as a worksheet, ie

There is a good reference here from Pearson Passing Simple Variables ByRef And ByVal 

Sub test()
Dim ws As Worksheet
Set ws = Sheets(2)
Call sortRows("MyName", ws)
End Sub

Sub sortRows(location As String, ByRef ws1 As Worksheet)
MsgBox ws1.Name
End Sub

Upvotes: 1

Related Questions