initialise declaration

[Joe]

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

 

[Joe]

BTW mine is Office 2003..

 

[Bob Phillips]

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]

Thanks a lot Bob..
It certainly worked perfect!

Regards
Joe

 

[Joe]

Thanks a lot Bob..
It worked perfect!

Regards
Joe

 

[Tom Ogilvy]

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.

 

[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]

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

 

[Joe]

Hello 'Tom,

When the file is loaded, the following is showing problem!

With thisworkbook.sheet1

Can I send u the simplified file, so that u can have a look?

Thanks & Regards
Joe

 

[stormy]

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]

Hi all,

I have put a simple file.. its made as tom and bob had suggested.. but
still problem exist..

this has codes in Module1, Sheet1 and ThisWorkBook

Error1: Problem while loading.
Error2: the code {rng_x(1, 1).Value = ""} in sheet1 is not working

http://www.esnips.com/doc/abd90f51-8fdf-4c14-a4e6-0cd7d3219768/Sudokutest.xls

If some can have a look and suggest some modification, it would have
been really helpful..

Thanks
Joe

 

[Bob Phillips]

Joe,

Tom made a mistake.

You cannot use

With ThisWorkbook.Sheet1

in the workbook open event, you have to use

With ThisWorkbook.Worksheets("Sheet1")



--
HTH

Bob Phillips

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

 

[Joe]

Thanks Bob..
Its working.. I dont think, I have to correct myself again..

One correction i made is..

As we SET the range in ThisWorkBook, I have blanked the SET command in
Private Sub Worksheet_Activate()

its not showing porblem.. Hope it is not needed there.

Thanks all..
Joe.

 

[Joe]

Just to conclude the thread.. Thanks to Bob and Tom, that I cleaned up
my code a lot and its more flexible..

It was for the sudoku solver I was trying..

The output is at the following location, if you may need to see the
result..

http://www.esnips.com/web/Sudokusolverbeta

Thanks guys.. u all r great..
Joe