[SOLVED] Trouble with module wide variable

Hi

I am trying to get a module wide variable to provide the current filename to the various subs...

Private CurrentFileName As String
'-----------------------------------------------------------------------------------------
Sub GetCurrentFileName()

CurrentFileName = Cells("sFileName").Value
MsgBox CurrentFileName
    
End Sub 'GetCurrentFileName

It seems I can't say the "CurrentFileName" be a cell value.
why is this? I used the =CELL("fileneme") in the cell which I then gave a name to "sFileName". I get get the value if I declare the variable in the sub but not if it is declared at the beginning of the module...

I am a bit of a beginner at this
Thank you for any help you could provide

Good morning saabman

Originally Posted by saabman

I am trying to get a module wide variable to provide the current filename to the various subs...

It seems I can't say the "CurrentFileName" be a cell value.
why is this? I used the =CELL("fileneme") in the cell which I then gave a name to "sFileName". I get get the value if I declare the variable in the sub but not if it is declared at the beginning of the module...

You don't have to get the file name from a cell - you can just pick it straight up from VBA. Whether you use the .Name command or .FullName command depends on whether you want to see just the filename or the full path :

MsgBox ThisWorkbook.Name
MsgBox ThisWorkbook.FullName

HTH

DominicB