| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
remembering the nth item in one sub routine in the next sub routin
I have a macro which has one sub routine running another sub routine, however
I want the n number I defined in the first sub routine to be rembered in the next sub routine. ie Dim Asset(1 To 19) As String 'Camilla EDITED coding Asset(1) = "Eurostoxx 50" Asset(2) = "Nasdaq 100" Dim n As Integer For n = 1 To 19 Worksheets(Asset(n)).Activate CloseDay MyDay, MyMonth, MyYear Next n then in the next sub routine I am trying to switch between workbooks and worksheets ie Workbooks("Trade Files").Worksheets(Asset(n)).Activate but I need it to remember the the (Asset(n)) from the previous sub routine. Can anyone help? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
remembering the nth item in one sub routine in the next sub routin
If you want a variable to be available (global) to several Subs, then declare
it above all the subs in the module. -- Gary's Student "Cammy" wrote: I have a macro which has one sub routine running another sub routine, however I want the n number I defined in the first sub routine to be rembered in the next sub routine. ie Dim Asset(1 To 19) As String 'Camilla EDITED coding Asset(1) = "Eurostoxx 50" Asset(2) = "Nasdaq 100" Dim n As Integer For n = 1 To 19 Worksheets(Asset(n)).Activate CloseDay MyDay, MyMonth, MyYear Next n then in the next sub routine I am trying to switch between workbooks and worksheets ie Workbooks("Trade Files").Worksheets(Asset(n)).Activate but I need it to remember the the (Asset(n)) from the previous sub routine. Can anyone help? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
remembering the nth item in one sub routine in the next sub ro
Even when I define
Dim n As Integer Dim Asset(1 To 19) As String Asset(1) = "Eurostoxx 50" Asset(2) = "Nasdaq 100" At the beginning of the sub routine, the macro still get stuck on the coding: Workbooks("Trade Files").Worksheets(Asset(n)).Activate do you know why? "Gary''s Student" wrote: If you want a variable to be available (global) to several Subs, then declare it above all the subs in the module. -- Gary's Student "Cammy" wrote: I have a macro which has one sub routine running another sub routine, however I want the n number I defined in the first sub routine to be rembered in the next sub routine. ie Dim Asset(1 To 19) As String 'Camilla EDITED coding Asset(1) = "Eurostoxx 50" Asset(2) = "Nasdaq 100" Dim n As Integer For n = 1 To 19 Worksheets(Asset(n)).Activate CloseDay MyDay, MyMonth, MyYear Next n then in the next sub routine I am trying to switch between workbooks and worksheets ie Workbooks("Trade Files").Worksheets(Asset(n)).Activate but I need it to remember the the (Asset(n)) from the previous sub routine. Can anyone help? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
remembering the nth item in one sub routine in the next sub ro
sorry I forgot to say I don't want the variables defined for all sub routines
just the two that are relevant. "Cammy" wrote: Even when I define Dim n As Integer Dim Asset(1 To 19) As String Asset(1) = "Eurostoxx 50" Asset(2) = "Nasdaq 100" At the beginning of the sub routine, the macro still get stuck on the coding: Workbooks("Trade Files").Worksheets(Asset(n)).Activate do you know why? "Gary''s Student" wrote: If you want a variable to be available (global) to several Subs, then declare it above all the subs in the module. -- Gary's Student "Cammy" wrote: I have a macro which has one sub routine running another sub routine, however I want the n number I defined in the first sub routine to be rembered in the next sub routine. ie Dim Asset(1 To 19) As String 'Camilla EDITED coding Asset(1) = "Eurostoxx 50" Asset(2) = "Nasdaq 100" Dim n As Integer For n = 1 To 19 Worksheets(Asset(n)).Activate CloseDay MyDay, MyMonth, MyYear Next n then in the next sub routine I am trying to switch between workbooks and worksheets ie Workbooks("Trade Files").Worksheets(Asset(n)).Activate but I need it to remember the the (Asset(n)) from the previous sub routine. Can anyone help? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
remembering the nth item in one sub routine in the next sub ro
Also if I do define Asset(1) , Asset(2) at the top of the all subs in the
procedure it says it is an invalid procedure. "Cammy" wrote: Even when I define Dim n As Integer Dim Asset(1 To 19) As String Asset(1) = "Eurostoxx 50" Asset(2) = "Nasdaq 100" At the beginning of the sub routine, the macro still get stuck on the coding: Workbooks("Trade Files").Worksheets(Asset(n)).Activate do you know why? "Gary''s Student" wrote: If you want a variable to be available (global) to several Subs, then declare it above all the subs in the module. -- Gary's Student "Cammy" wrote: I have a macro which has one sub routine running another sub routine, however I want the n number I defined in the first sub routine to be rembered in the next sub routine. ie Dim Asset(1 To 19) As String 'Camilla EDITED coding Asset(1) = "Eurostoxx 50" Asset(2) = "Nasdaq 100" Dim n As Integer For n = 1 To 19 Worksheets(Asset(n)).Activate CloseDay MyDay, MyMonth, MyYear Next n then in the next sub routine I am trying to switch between workbooks and worksheets ie Workbooks("Trade Files").Worksheets(Asset(n)).Activate but I need it to remember the the (Asset(n)) from the previous sub routine. Can anyone help? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
remembering the nth item in one sub routine in the next sub ro
I have decided that it is best to define it globally but I have defined it
using another letter k so that all other n's in other subroutines are not affected. Thanks gary! "Cammy" wrote: Also if I do define Asset(1) , Asset(2) at the top of the all subs in the procedure it says it is an invalid procedure. "Cammy" wrote: Even when I define Dim n As Integer Dim Asset(1 To 19) As String Asset(1) = "Eurostoxx 50" Asset(2) = "Nasdaq 100" At the beginning of the sub routine, the macro still get stuck on the coding: Workbooks("Trade Files").Worksheets(Asset(n)).Activate do you know why? "Gary''s Student" wrote: If you want a variable to be available (global) to several Subs, then declare it above all the subs in the module. -- Gary's Student "Cammy" wrote: I have a macro which has one sub routine running another sub routine, however I want the n number I defined in the first sub routine to be rembered in the next sub routine. ie Dim Asset(1 To 19) As String 'Camilla EDITED coding Asset(1) = "Eurostoxx 50" Asset(2) = "Nasdaq 100" Dim n As Integer For n = 1 To 19 Worksheets(Asset(n)).Activate CloseDay MyDay, MyMonth, MyYear Next n then in the next sub routine I am trying to switch between workbooks and worksheets ie Workbooks("Trade Files").Worksheets(Asset(n)).Activate but I need it to remember the the (Asset(n)) from the previous sub routine. Can anyone help? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
remembering the nth item in one sub routine in the next sub ro
Why not just pass the variables to the second routine?
Sub Macro1() Dim n As Integer Dim Asset(1 To 19) As String Asset(1) = "Eurostoxx 50" Asset(2) = "Nasdaq 100" 'other stuff here Macro2 Asset(1), Asset(2), n End Sub Sub Macro2(myAsset1 as string, myAsset2 as string, myN as integer) 'your stuff End Sub James Sub Macro1 Dim Cammy wrote: I have decided that it is best to define it globally but I have defined it using another letter k so that all other n's in other subroutines are not affected. Thanks gary! "Cammy" wrote: Also if I do define Asset(1) , Asset(2) at the top of the all subs in the procedure it says it is an invalid procedure. "Cammy" wrote: Even when I define Dim n As Integer Dim Asset(1 To 19) As String Asset(1) = "Eurostoxx 50" Asset(2) = "Nasdaq 100" At the beginning of the sub routine, the macro still get stuck on the coding: Workbooks("Trade Files").Worksheets(Asset(n)).Activate do you know why? "Gary''s Student" wrote: If you want a variable to be available (global) to several Subs, then declare it above all the subs in the module. -- Gary's Student "Cammy" wrote: I have a macro which has one sub routine running another sub routine, however I want the n number I defined in the first sub routine to be rembered in the next sub routine. ie Dim Asset(1 To 19) As String 'Camilla EDITED coding Asset(1) = "Eurostoxx 50" Asset(2) = "Nasdaq 100" Dim n As Integer For n = 1 To 19 Worksheets(Asset(n)).Activate CloseDay MyDay, MyMonth, MyYear Next n then in the next sub routine I am trying to switch between workbooks and worksheets ie Workbooks("Trade Files").Worksheets(Asset(n)).Activate but I need it to remember the the (Asset(n)) from the previous sub routine. Can anyone help? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Remembering lookups | Excel Worksheet Functions | |||
| Remembering a value moving from one sheet to another | Excel Discussion (Misc queries) | |||
| Remembering Data Values. | New Users to Excel | |||
| For and If Then loops, and remembering a value | Excel Programming | |||
| UserForm remembering Settings | Excel Programming | |||
Linear Mode
