Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass variable to macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass variable to macro
Public sCol As String
must be declared in the general/standard module at the very top outside any procedures. -- Regards, Tom Ogilvy "Jason Morin" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass variable to macro
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 "Jason Morin" skrev i melding ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pass variable from one workbook to another | Excel Discussion (Misc queries) | |||
How to pass a variable into an SQL statement | Excel Discussion (Misc queries) | |||
Pass a variable into a range? | Excel Programming | |||
Pass variable from user form to macro | Excel Programming | |||
Using Public to Pass Variable | Excel Programming |