Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
initialise declaration
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
initialise declaration
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
initialise declaration
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
initialise declaration
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
initialise declaration
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
initialise declaration
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
initialise declaration
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
initialise declaration
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
initialise declaration
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 Tom Ogilvy wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
initialise declaration
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
initialise declaration
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-8...Sudokutest.xls If some can have a look and suggest some modification, it would have been really helpful.. Thanks Joe Joe wrote: 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 Tom Ogilvy wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
initialise declaration
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" wrote in message oups.com... 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-8...Sudokutest.xls If some can have a look and suggest some modification, it would have been really helpful.. Thanks Joe Joe wrote: 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 Tom Ogilvy wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
initialise declaration
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. Bob Phillips wrote: 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" wrote in message oups.com... 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-8...Sudokutest.xls If some can have a look and suggest some modification, it would have been really helpful.. Thanks Joe Joe wrote: 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 Tom Ogilvy wrote: 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
initialise declaration
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 Joe wrote: 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. Bob Phillips wrote: 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" wrote in message oups.com... 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-8...Sudokutest.xls If some can have a look and suggest some modification, it would have been really helpful.. Thanks Joe Joe wrote: 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 Tom Ogilvy wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if declaration | Excel Programming | |||
Declaration name | Excel Programming | |||
Array Declaration | Excel Programming | |||
which declaration to use | Excel Programming | |||
Declaration? | Excel Programming |