Dim statement

[Patrick C. Simonds]

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

 

[Patrick Molloy]

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

 

[Joel]

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]

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

 

[Dave Peterson]

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]

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?

 

[JP Ronse]

Hi Patrick,

Try on top of a module, so not within sub/end sub

Public rng as Range
Public rng1 as Range

Wkr,

JP

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?

 

[Patrick Molloy]

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

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]

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