Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to the base workbook in which the module is situated
Hi,
I want to store the name of the workbook in which the module/macro is actually there, in to a string variable. So that if I change the name of the workbook the macro will still run. I wrote the following, to try out whether it works Option Explicit Public Const s As String = ThisWorkbook.Name Sub try() MsgBox s End Sub Im getting a -- Compile error : constant expression required -- in the line -- Public Const s As String = ThisWorkbook.Name -- Bascially I have to use this workbook name in different subs within the same module , hence defined it as -- Public -- I read a recent post -- Dim Vs Public -- in which it was said that if am using a variable name only within the present module then we dont need to use Public but Dim would be sufficient. Why isn that working in the case of Constants. Like I defined the above as -- dim Const s As String = ThisWorkbook.Name -- and Im getting a -- compile error : expected identifier -- in the word "const". Where is my understanding going wrong? a) Why am I getting this error? b) Is the logic of using "Thisworkbook" correct? I mean would that ensure that -- s -- the string variable would have the name of the workbook in which the module is located. (Since, the above macro didnt work so I have no way of testing it, hence the query) Thanks a lot, Hari India |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to the base workbook in which the module is situated
Hi Nick,
Thnx a lot for -- With .. End With -- Would try to get comfortable with this smarter method. I tried ur method of defining at the top of each module as you recommended, but Im getting a -- Compile error : Invalid outside procedure-- It could be because, am interested only in storing the name of the Thisworkbook, so that I could use it performing vlookup's etc. I dont want to modify the Thisworkbook. Hence, rather than using Dim wb as Workbook Set wb = ThisWorkbook I did the following :- Option Explicit Dim s As string Set s = ThisWorkbook.Name Sub try() MsgBox s End Sub On the other hand even if i use the code below, i still get the same error.. Option Explicit Dim s As Workbook Set s = ThisWorkbook Sub try() MsgBox s.Name End Sub Please guide me. Regards, Hari India |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to the base workbook in which the module is situated
Hari
Your can't 'Set' it outside the module, that was my point, rather than having a global variable, I would keep to using ThisWorkbook inside each module -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Hari Prasadh" wrote in message ... Hi Nick, Thnx a lot for -- With .. End With -- Would try to get comfortable with this smarter method. I tried ur method of defining at the top of each module as you recommended, but Im getting a -- Compile error : Invalid outside procedure-- It could be because, am interested only in storing the name of the Thisworkbook, so that I could use it performing vlookup's etc. I dont want to modify the Thisworkbook. Hence, rather than using Dim wb as Workbook Set wb = ThisWorkbook I did the following :- Option Explicit Dim s As string Set s = ThisWorkbook.Name Sub try() MsgBox s End Sub On the other hand even if i use the code below, i still get the same error.. Option Explicit Dim s As Workbook Set s = ThisWorkbook Sub try() MsgBox s.Name End Sub Please guide me. Regards, Hari India |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to the base workbook in which the module is situated
Hi Nick,
Now I get it. I thought I could get the easy way out by writing -- SET -- also on the top of the module. Anyway based on your recommendation I have used the Set statement in the *first* sub of my module and I experimented with the following and the name is available in other subs within the same module (I mean no need of writing -- SET -- statement for each Sub) Option Explicit Dim s As Workbook Sub try() Set s = ThisWorkbook MsgBox s.Name Call trying End Sub Sub trying() Windows("book2").Activate MsgBox s.Name End Sub And its working as expected. Thnx a ton for your help. Regards, Hari India "Nick Hodge" wrote in message ... Hari Your can't 'Set' it outside the module, that was my point, rather than having a global variable, I would keep to using ThisWorkbook inside each module -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Hari Prasadh" wrote in message ... Hi Nick, Thnx a lot for -- With .. End With -- Would try to get comfortable with this smarter method. I tried ur method of defining at the top of each module as you recommended, but Im getting a -- Compile error : Invalid outside procedure-- It could be because, am interested only in storing the name of the Thisworkbook, so that I could use it performing vlookup's etc. I dont want to modify the Thisworkbook. Hence, rather than using Dim wb as Workbook Set wb = ThisWorkbook I did the following :- Option Explicit Dim s As string Set s = ThisWorkbook.Name Sub try() MsgBox s End Sub On the other hand even if i use the code below, i still get the same error.. Option Explicit Dim s As Workbook Set s = ThisWorkbook Sub try() MsgBox s.Name End Sub Please guide me. Regards, Hari India |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to the base workbook in which the module is situated
Just a thought---
I wouldn't use a variable to hold thisworkbook.name. I find that the code is more readable (for debugging sake) to use ThisWorkbook.Name (or even just ThisWorkbook). Hari Prasadh wrote: Hi, I want to store the name of the workbook in which the module/macro is actually there, in to a string variable. So that if I change the name of the workbook the macro will still run. I wrote the following, to try out whether it works Option Explicit Public Const s As String = ThisWorkbook.Name Sub try() MsgBox s End Sub Im getting a -- Compile error : constant expression required -- in the line -- Public Const s As String = ThisWorkbook.Name -- Bascially I have to use this workbook name in different subs within the same module , hence defined it as -- Public -- I read a recent post -- Dim Vs Public -- in which it was said that if am using a variable name only within the present module then we dont need to use Public but Dim would be sufficient. Why isn that working in the case of Constants. Like I defined the above as -- dim Const s As String = ThisWorkbook.Name -- and Im getting a -- compile error : expected identifier -- in the word "const". Where is my understanding going wrong? a) Why am I getting this error? b) Is the logic of using "Thisworkbook" correct? I mean would that ensure that -- s -- the string variable would have the name of the workbook in which the module is located. (Since, the above macro didnt work so I have no way of testing it, hence the query) Thanks a lot, Hari India -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to the base workbook in which the module is situated
Hi Dave,
Yes, you are right. It makes sense. I have made it to Thisworkbook everywhere Nick - Your efforts didnt go waste. Due to your response to this post I LEARNT about SET statement from you today and about constants being of *Typed* assignment. Thnx a ton. Thanks a lot, Hari India "Dave Peterson" wrote in message ... Just a thought--- I wouldn't use a variable to hold thisworkbook.name. I find that the code is more readable (for debugging sake) to use ThisWorkbook.Name (or even just ThisWorkbook). Hari Prasadh wrote: Hi, I want to store the name of the workbook in which the module/macro is actually there, in to a string variable. So that if I change the name of the workbook the macro will still run. I wrote the following, to try out whether it works Option Explicit Public Const s As String = ThisWorkbook.Name Sub try() MsgBox s End Sub Im getting a -- Compile error : constant expression required -- in the line -- Public Const s As String = ThisWorkbook.Name -- Bascially I have to use this workbook name in different subs within the same module , hence defined it as -- Public -- I read a recent post -- Dim Vs Public -- in which it was said that if am using a variable name only within the present module then we dont need to use Public but Dim would be sufficient. Why isn that working in the case of Constants. Like I defined the above as -- dim Const s As String = ThisWorkbook.Name -- and Im getting a -- compile error : expected identifier -- in the word "const". Where is my understanding going wrong? a) Why am I getting this error? b) Is the logic of using "Thisworkbook" correct? I mean would that ensure that -- s -- the string variable would have the name of the workbook in which the module is located. (Since, the above macro didnt work so I have no way of testing it, hence the query) Thanks a lot, Hari India -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referring to previous worksheet in workbook | Excel Worksheet Functions | |||
Formula referring to a dynamic range in a different workbook | Excel Worksheet Functions | |||
Referring to external workbook in Data Validation | Excel Discussion (Misc queries) | |||
Referring to function in another workbook | Excel Worksheet Functions | |||
Identical menuitems referring to wrong workbook | Excel Programming |