Hello guys,

My basics of VBA are poor..

I would like to know How I can Initialise certain things.. One time..

What I do now is..

' ***********
Public rng_source, rng_x As Range

Private Sub CommandButton1_Click()
n_Mat = 9

Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")

.....
end sub
' ************

1. I want to decalre it outside the function, may be when the sheet is
loaded.. Pls guide me..
2. If I declare that way, will it be visible in the Module Functions

Thanks all,

Joe

BTW mine is Office 2003..


Joe wrote:
Hello guys,

My basics of VBA are poor..

I would like to know How I can Initialise certain things.. One time..

What I do now is..

' ***********
Public rng_source, rng_x As Range

Private Sub CommandButton1_Click()
n_Mat = 9

Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")

....
end sub
' ************

1. I want to decalre it outside the function, may be when the sheet is
loaded.. Pls guide me..
2. If I declare that way, will it be visible in the Module Functions

Thanks all,

Joe

Private Sub Worksheet_Activate()
Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Joe" wrote in message
oups.com...
Hello guys,

My basics of VBA are poor..

I would like to know How I can Initialise certain things.. One time..

What I do now is..

' ***********
Public rng_source, rng_x As Range

Private Sub CommandButton1_Click()
n_Mat = 9

Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")

....
end sub
' ************

1. I want to decalre it outside the function, may be when the sheet is
loaded.. Pls guide me..
2. If I declare that way, will it be visible in the Module Functions

Thanks all,

Joe

Thanks a lot Bob..
It certainly worked perfect!

Regards
Joe
Bob Phillips wrote:
Private Sub Worksheet_Activate()
Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Joe" wrote in message
oups.com...
Hello guys,

My basics of VBA are poor..

I would like to know How I can Initialise certain things.. One time..

What I do now is..

' ***********
Public rng_source, rng_x As Range

Private Sub CommandButton1_Click()
n_Mat = 9

Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")

....
end sub
' ************

1. I want to decalre it outside the function, may be when the sheet is
loaded.. Pls guide me..
2. If I declare that way, will it be visible in the Module Functions

Thanks all,

Joe

Thanks a lot Bob..
It worked perfect!

Regards
Joe
Bob Phillips wrote:
Private Sub Worksheet_Activate()
Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Joe" wrote in message
oups.com...
Hello guys,

My basics of VBA are poor..

I would like to know How I can Initialise certain things.. One time..

What I do now is..

' ***********
Public rng_source, rng_x As Range

Private Sub CommandButton1_Click()
n_Mat = 9

Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")

....
end sub
' ************

1. I want to decalre it outside the function, may be when the sheet is
loaded.. Pls guide me..
2. If I declare that way, will it be visible in the Module Functions

Thanks all,

Joe

Since you said:
2. If I declare that way, will it be visible in the Module Functions

and Bob didn't address that, just to be sure,

Public rng_source as Range, rng_x As Range


should be placed at the top of a general module, not in the sheet module
where the activate event is located. Also, each variable should be typed
separately. As you originally wrote it, rng_source was declared as a variant
while only rng_x was declared as a range reference.

--
regards,
Tom Ogilvy


"Joe" wrote:

Thanks a lot Bob..
It worked perfect!

Regards
Joe
Bob Phillips wrote:
Private Sub Worksheet_Activate()
Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Joe" wrote in message
oups.com...
Hello guys,

My basics of VBA are poor..

I would like to know How I can Initialise certain things.. One time..

What I do now is..

' ***********
Public rng_source, rng_x As Range

Private Sub CommandButton1_Click()
n_Mat = 9

Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")

....
end sub
' ************

1. I want to decalre it outside the function, may be when the sheet is
loaded.. Pls guide me..
2. If I declare that way, will it be visible in the Module Functions

Thanks all,

Joe

Thanks Tom,

Infact I had not got that correctly as I said before.
I had forgot to blank the original code.. so it was working from
there..

But I tried to implement what Tom has told about defining as range.
still I have problem.

I try to put what the current situation is.

All my code was in the Object "Sheet1".
As Bob suggested I wanted to put that in the Object "ThisWorkBook"
under the event, "Private Sub Worksheet_Activate()"

So it looks like this..

*************
Private Sub Worksheet_Activate()
Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")
End Sub
*************

It shows error.. :(

Then I tried to put the variable declaration also into "ThisWorkBook"..
and later in "sheet1"

********
Public rng_source As Range
Public rng_x As Range
***********

Both resulted in Error :(

I dont know if this info is enough to identify the problem.. But Hope
it is..

Really appriciate ur helps..

Thanks
Joe


Tom Ogilvy wrote:
Since you said:
2. If I declare that way, will it be visible in the Module Functions

and Bob didn't address that, just to be sure,

Public rng_source as Range, rng_x As Range


should be placed at the top of a general module, not in the sheet module
where the activate event is located. Also, each variable should be typed
separately. As you originally wrote it, rng_source was declared as a variant
while only rng_x was declared as a range reference.

--
regards,
Tom Ogilvy


"Joe" wrote:

Thanks a lot Bob..
It worked perfect!

Regards
Joe
Bob Phillips wrote:
Private Sub Worksheet_Activate()
Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Joe" wrote in message
oups.com...
Hello guys,

My basics of VBA are poor..

I would like to know How I can Initialise certain things.. One time..

What I do now is..

' ***********
Public rng_source, rng_x As Range

Private Sub CommandButton1_Click()
n_Mat = 9

Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")

....
end sub
' ************

1. I want to decalre it outside the function, may be when the sheet is
loaded.. Pls guide me..
2. If I declare that way, will it be visible in the Module Functions

Thanks all,

Joe

In a general module (insert=Module in the VBE) at the top

Public rng_source As Range
Public rng_x As Range

in the Sheet1 Module (assume the ranges refer to ranges in shee1)

Private Sub Worksheet_Activate()
Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")
End Sub

in the thisWorkbook module

Private Sub Workbook_Open()
With thisworkbook.sheet1
Set rng_source = .Range("B3:J11")
Set rng_x = .Range("L3:T11")
End With
End sub

--
Regards,
Tom Ogilvy

"Joe" wrote:

Thanks Tom,

Infact I had not got that correctly as I said before.
I had forgot to blank the original code.. so it was working from
there..

But I tried to implement what Tom has told about defining as range.
still I have problem.

I try to put what the current situation is.

All my code was in the Object "Sheet1".
As Bob suggested I wanted to put that in the Object "ThisWorkBook"
under the event, "Private Sub Worksheet_Activate()"

So it looks like this..

*************
Private Sub Worksheet_Activate()
Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")
End Sub
*************

It shows error.. :(

Then I tried to put the variable declaration also into "ThisWorkBook"..
and later in "sheet1"

********
Public rng_source As Range
Public rng_x As Range
***********

Both resulted in Error :(

I dont know if this info is enough to identify the problem.. But Hope
it is..

Really appriciate ur helps..

Thanks
Joe


Tom Ogilvy wrote:
Since you said:
2. If I declare that way, will it be visible in the Module Functions

and Bob didn't address that, just to be sure,

Public rng_source as Range, rng_x As Range


should be placed at the top of a general module, not in the sheet module
where the activate event is located. Also, each variable should be typed
separately. As you originally wrote it, rng_source was declared as a variant
while only rng_x was declared as a range reference.

--
regards,
Tom Ogilvy


"Joe" wrote:

Thanks a lot Bob..
It worked perfect!

Regards
Joe
Bob Phillips wrote:
Private Sub Worksheet_Activate()
Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Joe" wrote in message
oups.com...
Hello guys,

My basics of VBA are poor..

I would like to know How I can Initialise certain things.. One time..

What I do now is..

' ***********
Public rng_source, rng_x As Range

Private Sub CommandButton1_Click()
n_Mat = 9

Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")

....
end sub
' ************

1. I want to decalre it outside the function, may be when the sheet is
loaded.. Pls guide me..
2. If I declare that way, will it be visible in the Module Functions

Thanks all,

Joe

Another method you might consider is "lazy initialization"...
Public rng_source, rng_x As Range

if TypeName(rng_source) = "Nothing" then Set rng_source = Range("B3:J11")
if TypeName(rng_x) = "Nothing" then Set rng_x = Range("L3:T11")

This may not be very efficient for a click handler. I can't find the code
where I used Worksheet_Activate, but I think it fires every time you switch
to the sheet, which may not provide the desired results. If it does fire in
that manner, you might combine the "lazy" method with the event handler.

(Excel 2003)

"Joe" wrote:

Hello guys,

My basics of VBA are poor..

I would like to know How I can Initialise certain things.. One time..

What I do now is..

' ***********
Public rng_source, rng_x As Range

Private Sub CommandButton1_Click()
n_Mat = 9

Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")

.....
end sub
' ************

1. I want to decalre it outside the function, may be when the sheet is
loaded.. Pls guide me..
2. If I declare that way, will it be visible in the Module Functions

Thanks all,

Joe