Declaring a variable as a global constant question

[AxMan]

Hi All,

I've declared the below variable as follows:-

Global Const FilePathName As String = "X:\Sales\Tools\Available Distribution Tool\Company"

I've tried to replace multiple file paths with the above along with the rest of the file path, but when you step through the code it doesn't seem to like it.

This is an example of an instance where I've tried to replace FilePathName:-

ActiveWorkbook.SaveAs Filename:= _ "X:\Sales\Tools\Available Distribution Tool\Company\Stock Reports\" & "Stock By Owner ID" & ".xlsx", FileFormat:=51, _
CreateBackup:=False

to

ActiveWorkbook.SaveAs Filename:= _ FilePathName & "\Stock Reports\" & "Stock By Owner ID" & ".xlsx", FileFormat:=51, _
CreateBackup:=False


Have I declared the variable correctly so it will be recognised across all sub routines?

Any help would be appreciated!

 

[Joe4]

If you are trying to use it in other Modules, you need to use the word "Public" instead of "Global", and you should declare it outside of any procedure/function (usually at the top before any procedures/functions).

 

[shg]

"doesn't seem to like it"

What does that mean?

 

[AxMan]

Thanks Joe, I'm trying to use it in just one module, I have several subroutines within that module that I'm trying to use that variable in.

"Doesn't seem to like it" means I've stepped through the code and get an error:-

Compile Error:

Syntax Error

 

[Joe4]

Note that the underscore is an "end of line continuation" mark to use, so it should only ever appear at the end of a line.

So does this work?

[COLOR=#333333]ActiveWorkbook.SaveAs Filename:= FilePathName & "\Stock Reports\Stock By Owner ID.xlsx", FileFormat:=51, _[/COLOR]
[COLOR=#333333]CreateBackup:=False[/COLOR]

If not, can you confirm for us exactly which line is returning the syntax error?

 

[Joe4]

Also, read this discussion here on declaring global/public variables: http://www.mrexcel.com/forum/excel-questions/425695-global-vs-public-variable.html