Referring to the base workbook in which the module is situated

[Hari Prasadh]

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

 

[Nick Hodge]

Hari

You cannot assign to a constant in that way I believe. It has to be a typed
constant

"Nick" or 1, etc

If you use the ThisWorkbook object it always refers to the workbook that
contains the code, so I would see little need to define it, except perhaps
at the top of each module

Dim wb as Workbook
Set wb = ThisWorkbook

Maybe, as I see you are trying to avoid duplicating lines in some of your
other posts, you might also use the With...End With construct on some of
these objects as it cuts down code considerably

e.g.

With ThisWorkbook
.SaveAs Filename:= .Name &"1.xls"
.Printout
.Close
End With

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Hari Prasadh]

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

 

[Nick Hodge]

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
(e-mail address removed)

 

[Hari Prasadh]

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

 

[Dave Peterson]

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]

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