Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim statement
A final attempt to demo what I want to do (if possible).
When UserForm1 displays I want to Set rng = Cells(ActiveCell.Row, 1) then call Module2 .test to populate TextBox1. My problem is not knowing where to place my Dim Statement (Dim rng as Range). If I do it within the UserForm code then when Module2 code runs it stops on rng and I get Sub or Function not defined error. This is just a test piece of code. Ultimately after rng is set to Cells(ActiveCell.Row, 1) there will be code which will find the next row in the active worksheet which meets a set criteria and then it will set rng1 and so on until it has set rng9 Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4) End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim statement
this is known as SCOPE. The DIM can be inside a SUB or in the genaral area of
the module ie at the very top. Since you neeed to use the variable in a couple of subs, is scope is module, so OPTION EXPLICIT DIM rng as Range Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4).Value End Sub IF your call to Module2.test also uses this variable, then the scope is wider, so PUBLIC rng As Range would allow other code modules access to the variable. Hope this is clear. Read up on variable scope "Patrick C. Simonds" wrote: A final attempt to demo what I want to do (if possible). When UserForm1 displays I want to Set rng = Cells(ActiveCell.Row, 1) then call Module2 .test to populate TextBox1. My problem is not knowing where to place my Dim Statement (Dim rng as Range). If I do it within the UserForm code then when Module2 code runs it stops on rng and I get Sub or Function not defined error. This is just a test piece of code. Ultimately after rng is set to Cells(ActiveCell.Row, 1) there will be code which will find the next row in the active worksheet which meets a set criteria and then it will set rng1 and so on until it has set rng9 Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4) End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim statement
Yes my Modual2 will also use the variable. So I amended my UserForm1 code
as seen below. But I still get the Sub or Function not defined error when Module2 is called. Option Explicit Public rng As Range Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub "Patrick Molloy" wrote in message ... this is known as SCOPE. The DIM can be inside a SUB or in the genaral area of the module ie at the very top. Since you neeed to use the variable in a couple of subs, is scope is module, so OPTION EXPLICIT DIM rng as Range Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4).Value End Sub IF your call to Module2.test also uses this variable, then the scope is wider, so PUBLIC rng As Range would allow other code modules access to the variable. Hope this is clear. Read up on variable scope "Patrick C. Simonds" wrote: A final attempt to demo what I want to do (if possible). When UserForm1 displays I want to Set rng = Cells(ActiveCell.Row, 1) then call Module2 .test to populate TextBox1. My problem is not knowing where to place my Dim Statement (Dim rng as Range). If I do it within the UserForm code then when Module2 code runs it stops on rng and I get Sub or Function not defined error. This is just a test piece of code. Ultimately after rng is set to Cells(ActiveCell.Row, 1) there will be code which will find the next row in the active worksheet which meets a set criteria and then it will set rng1 and so on until it has set rng9 Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4) End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim statement
If you want that rng variable to be seen from anywhere, move the Public
statement to a General module. Or you can use something like this: Option Explicit Sub test() MsgBox UserForm1.rng(1, 4).Address(external:=True) End Sub "Patrick C. Simonds" wrote: Yes my Modual2 will also use the variable. So I amended my UserForm1 code as seen below. But I still get the Sub or Function not defined error when Module2 is called. Option Explicit Public rng As Range Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub "Patrick Molloy" wrote in message ... this is known as SCOPE. The DIM can be inside a SUB or in the genaral area of the module ie at the very top. Since you neeed to use the variable in a couple of subs, is scope is module, so OPTION EXPLICIT DIM rng as Range Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4).Value End Sub IF your call to Module2.test also uses this variable, then the scope is wider, so PUBLIC rng As Range would allow other code modules access to the variable. Hope this is clear. Read up on variable scope "Patrick C. Simonds" wrote: A final attempt to demo what I want to do (if possible). When UserForm1 displays I want to Set rng = Cells(ActiveCell.Row, 1) then call Module2 .test to populate TextBox1. My problem is not knowing where to place my Dim Statement (Dim rng as Range). If I do it within the UserForm code then when Module2 code runs it stops on rng and I get Sub or Function not defined error. This is just a test piece of code. Ultimately after rng is set to Cells(ActiveCell.Row, 1) there will be code which will find the next row in the active worksheet which meets a set criteria and then it will set rng1 and so on until it has set rng9 Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4) End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim statement
I really seem to be missing the boat here.
I want to be able to declare the following ranges: Dim rng As Range Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim rng4 As Range Dim rng5 As Range Dim rng6 As Range Dim rng7 As Range Dim rng8 As Range Dim rng9 As Range I want to be able to set the values during UserForm Initialization "Set rng = Cells(ActiveCell.Row, 1)" I want that rng variable to be seen from anywhere. I have not been able to craft a General Module that will allow me to do this. So please, what would the code look like? "Dave Peterson" wrote in message ... If you want that rng variable to be seen from anywhere, move the Public statement to a General module. Or you can use something like this: Option Explicit Sub test() MsgBox UserForm1.rng(1, 4).Address(external:=True) End Sub "Patrick C. Simonds" wrote: Yes my Modual2 will also use the variable. So I amended my UserForm1 code as seen below. But I still get the Sub or Function not defined error when Module2 is called. Option Explicit Public rng As Range Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub "Patrick Molloy" wrote in message ... this is known as SCOPE. The DIM can be inside a SUB or in the genaral area of the module ie at the very top. Since you neeed to use the variable in a couple of subs, is scope is module, so OPTION EXPLICIT DIM rng as Range Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4).Value End Sub IF your call to Module2.test also uses this variable, then the scope is wider, so PUBLIC rng As Range would allow other code modules access to the variable. Hope this is clear. Read up on variable scope "Patrick C. Simonds" wrote: A final attempt to demo what I want to do (if possible). When UserForm1 displays I want to Set rng = Cells(ActiveCell.Row, 1) then call Module2 .test to populate TextBox1. My problem is not knowing where to place my Dim Statement (Dim rng as Range). If I do it within the UserForm code then when Module2 code runs it stops on rng and I get Sub or Function not defined error. This is just a test piece of code. Ultimately after rng is set to Cells(ActiveCell.Row, 1) there will be code which will find the next row in the active worksheet which meets a set criteria and then it will set rng1 and so on until it has set rng9 Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4) End Sub -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim statement
Hi Patrick,
Try on top of a module, so not within sub/end sub Public rng as Range Public rng1 as Range Wkr, JP "Patrick C. Simonds" wrote in message ... I really seem to be missing the boat here. I want to be able to declare the following ranges: Dim rng As Range Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim rng4 As Range Dim rng5 As Range Dim rng6 As Range Dim rng7 As Range Dim rng8 As Range Dim rng9 As Range I want to be able to set the values during UserForm Initialization "Set rng = Cells(ActiveCell.Row, 1)" I want that rng variable to be seen from anywhere. I have not been able to craft a General Module that will allow me to do this. So please, what would the code look like? "Dave Peterson" wrote in message ... If you want that rng variable to be seen from anywhere, move the Public statement to a General module. Or you can use something like this: Option Explicit Sub test() MsgBox UserForm1.rng(1, 4).Address(external:=True) End Sub "Patrick C. Simonds" wrote: Yes my Modual2 will also use the variable. So I amended my UserForm1 code as seen below. But I still get the Sub or Function not defined error when Module2 is called. Option Explicit Public rng As Range Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub "Patrick Molloy" wrote in message ... this is known as SCOPE. The DIM can be inside a SUB or in the genaral area of the module ie at the very top. Since you neeed to use the variable in a couple of subs, is scope is module, so OPTION EXPLICIT DIM rng as Range Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4).Value End Sub IF your call to Module2.test also uses this variable, then the scope is wider, so PUBLIC rng As Range would allow other code modules access to the variable. Hope this is clear. Read up on variable scope "Patrick C. Simonds" wrote: A final attempt to demo what I want to do (if possible). When UserForm1 displays I want to Set rng = Cells(ActiveCell.Row, 1) then call Module2 .test to populate TextBox1. My problem is not knowing where to place my Dim Statement (Dim rng as Range). If I do it within the UserForm code then when Module2 code runs it stops on rng and I get Sub or Function not defined error. This is just a test piece of code. Ultimately after rng is set to Cells(ActiveCell.Row, 1) there will be code which will find the next row in the active worksheet which meets a set criteria and then it will set rng1 and so on until it has set rng9 Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4) End Sub -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim statement
I can't imagine whay you'd want so many declared ranges. Are yuo sure?
A range can be set to numerous cells, not necessarily contiguous set aRange = Range("A1,G2:J2,L5:L25") for example "Patrick C. Simonds" wrote: I really seem to be missing the boat here. I want to be able to declare the following ranges: Dim rng As Range Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim rng4 As Range Dim rng5 As Range Dim rng6 As Range Dim rng7 As Range Dim rng8 As Range Dim rng9 As Range I want to be able to set the values during UserForm Initialization "Set rng = Cells(ActiveCell.Row, 1)" I want that rng variable to be seen from anywhere. I have not been able to craft a General Module that will allow me to do this. So please, what would the code look like? "Dave Peterson" wrote in message ... If you want that rng variable to be seen from anywhere, move the Public statement to a General module. Or you can use something like this: Option Explicit Sub test() MsgBox UserForm1.rng(1, 4).Address(external:=True) End Sub "Patrick C. Simonds" wrote: Yes my Modual2 will also use the variable. So I amended my UserForm1 code as seen below. But I still get the Sub or Function not defined error when Module2 is called. Option Explicit Public rng As Range Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub "Patrick Molloy" wrote in message ... this is known as SCOPE. The DIM can be inside a SUB or in the genaral area of the module ie at the very top. Since you neeed to use the variable in a couple of subs, is scope is module, so OPTION EXPLICIT DIM rng as Range Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4).Value End Sub IF your call to Module2.test also uses this variable, then the scope is wider, so PUBLIC rng As Range would allow other code modules access to the variable. Hope this is clear. Read up on variable scope "Patrick C. Simonds" wrote: A final attempt to demo what I want to do (if possible). When UserForm1 displays I want to Set rng = Cells(ActiveCell.Row, 1) then call Module2 .test to populate TextBox1. My problem is not knowing where to place my Dim Statement (Dim rng as Range). If I do it within the UserForm code then when Module2 code runs it stops on rng and I get Sub or Function not defined error. This is just a test piece of code. Ultimately after rng is set to Cells(ActiveCell.Row, 1) there will be code which will find the next row in the active worksheet which meets a set criteria and then it will set rng1 and so on until it has set rng9 Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4) End Sub -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim statement
or even use an array of the ranges???
Public rng() as range And set its bounds later or if you know: public rng(0 to 9) as range "Patrick C. Simonds" wrote: I really seem to be missing the boat here. I want to be able to declare the following ranges: Dim rng As Range Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim rng4 As Range Dim rng5 As Range Dim rng6 As Range Dim rng7 As Range Dim rng8 As Range Dim rng9 As Range I want to be able to set the values during UserForm Initialization "Set rng = Cells(ActiveCell.Row, 1)" I want that rng variable to be seen from anywhere. I have not been able to craft a General Module that will allow me to do this. So please, what would the code look like? "Dave Peterson" wrote in message ... If you want that rng variable to be seen from anywhere, move the Public statement to a General module. Or you can use something like this: Option Explicit Sub test() MsgBox UserForm1.rng(1, 4).Address(external:=True) End Sub "Patrick C. Simonds" wrote: Yes my Modual2 will also use the variable. So I amended my UserForm1 code as seen below. But I still get the Sub or Function not defined error when Module2 is called. Option Explicit Public rng As Range Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub "Patrick Molloy" wrote in message ... this is known as SCOPE. The DIM can be inside a SUB or in the genaral area of the module ie at the very top. Since you neeed to use the variable in a couple of subs, is scope is module, so OPTION EXPLICIT DIM rng as Range Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4).Value End Sub IF your call to Module2.test also uses this variable, then the scope is wider, so PUBLIC rng As Range would allow other code modules access to the variable. Hope this is clear. Read up on variable scope "Patrick C. Simonds" wrote: A final attempt to demo what I want to do (if possible). When UserForm1 displays I want to Set rng = Cells(ActiveCell.Row, 1) then call Module2 .test to populate TextBox1. My problem is not knowing where to place my Dim Statement (Dim rng as Range). If I do it within the UserForm code then when Module2 code runs it stops on rng and I get Sub or Function not defined error. This is just a test piece of code. Ultimately after rng is set to Cells(ActiveCell.Row, 1) there will be code which will find the next row in the active worksheet which meets a set criteria and then it will set rng1 and so on until it has set rng9 Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4) End Sub -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim statement
Pass the value to the called macro. the other choice is to make it a public
variable. I prefer pasing parameters. Sub UserForm_Initialize() Dim rng As Range Set rng = Cells(ActiveCell.Row, 1) Call Module2.test(rng) End Sub Sub test(rng As Range) TextBox1.Value = rng(1, 4) End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4) End Sub "Patrick C. Simonds" wrote: A final attempt to demo what I want to do (if possible). When UserForm1 displays I want to Set rng = Cells(ActiveCell.Row, 1) then call Module2 .test to populate TextBox1. My problem is not knowing where to place my Dim Statement (Dim rng as Range). If I do it within the UserForm code then when Module2 code runs it stops on rng and I get Sub or Function not defined error. This is just a test piece of code. Ultimately after rng is set to Cells(ActiveCell.Row, 1) there will be code which will find the next row in the active worksheet which meets a set criteria and then it will set rng1 and so on until it has set rng9 Sub UserForm_Initialize() Set rng = Cells(ActiveCell.Row, 1) Call Module2.test End Sub ''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''' Sub test() TextBox1.Value = rng(1, 4) End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Can I nest an 'If' statement in a 'With' statement? | Excel Programming | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions |