Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Variables Between Procedures
I have a work book with six sheets
Sheet1, Sheet2. Sheet3 Sheet1UL, Sheet2UL. Sheet3UL The flowing code works as it should for the first sheet in the array passed to the second procedure €śClearDestination€ť. When it jumps back up to the first procedure €śArrayLoop€ť, sheetVar(i) holds the next sheet in the array correctly but when it moves back to €śClearDestination€ť, (sheetVar(i) & "UL") still holds Sheet1UL. For the life of me I cannot figure out why it dose not change to Sheet2UL. ARRRRRR! Appreciatively, Arturo Public NumCopies As Long Public sheetVar As Variant Sub ArrayLoop() sheetVar = Array("Sheet1", "Sheet2", "Sheet3") For i = LBound(sheetVar) To UBound(sheetVar) Set sh = Worksheets(sheetVar(i)) Sheets(sheetVar(i)).Select ClearDestination Next End Sub Sub ClearDestination() Dim myRange2 As Range Set myRange2 = Sheets((sheetVar(i) & "UL")).Range("A2:N65000") myRange2.ClearContents End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Variables Between Procedures
"Arturo" wrote:
The flowing code works as it should for the first sheet in the array passed to the second procedure €śClearDestination€ť. When it jumps back up to the first procedure €śArrayLoop€ť, sheetVar(i) holds the next sheet in the array correctly but when it moves back to €śClearDestination€ť, (sheetVar(i) & "UL") still holds Sheet1UL. For the life of me I cannot figure out why it dose not change to Sheet2UL. It's occasions like these that convince you to go into the preferences and require "Option Explicit" in all your modules. In the Visual Basic Editor, Tools - Options... - Editor tab - Require Variable Declaration That will also protect you from typos in variable names. The problem you're having has to do with what's in the variable i when you get to the ClearDestination sub. You might want to re-do the ClearDestination sub so that it requires an argument or two. That would be better than throwing everything into the global namespace. --Shawn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing Variables | Excel Discussion (Misc queries) | |||
Calling Procedures from another excel sheet by passing objects | Excel Programming | |||
More questions about variables and procedures... | Excel Programming | |||
Passing Variables | Excel Programming | |||
Passing ARGUMENTS between event procedures of a USERFORM | Excel Programming |