Pass variable to macro

[Guest]

Hello and TIA. I'm trying to pass along a variable from a workbook_open macro
to another macro, but the variable is not getting passed along. The macro
"GenerateWord" in a standard module isn't recognizing "sCol".

Public sCol As String
Sub Workbook_Open()
sCol = InputBox("Enter column.")
GenerateWord
End Sub

Sub GenerateWord()
Dim iLastRow As Long
'Set range
With Workbooks("words.xls").Sheets("test")
iLastRow = .Cells(Rows.Count, sCol).End(xlUp).Row 'error occurs here
......
End Sub

Regards,
Jason

 

[Tom Ogilvy]

Public sCol As String


must be declared in the general/standard module at the very top outside any
procedures.

 

[Harald Staff]

Hii Jason

You could try
iLastRow = .Cells(Rows.Count, ThisWorkbook.sCol).End(xlUp).Row

but that's not really passing a variable, it's making the variable
available. To really pass it, do

Sub Workbook_Open()
Dim sCol As String
sCol = InputBox("Enter column.")
Call GenerateWord(sCol)
End Sub

Sub GenerateWord(sCol As String)
MsgBox "You passed " & sCol
End Sub

Note that iLastRow will still err as is, because Cells need a numeric column
reference, not a column letter / string variable.

HTH. Best wishes Harald