Assigning a variable a permant value till it is reassigned

[brettvba]

Can this be done without a const??

 

[brettvba]

Public wkbname

Function GetDirPath_ThisWorkbook(wkbname) As String
'Returns the path from a filespec
Dim x As Variant
x = Split(ThisWorkbook.FullName, Application.PathSeparator)
ReDim Preserve x(0 To UBound(x) - 1)
GetDir_ThisWorkbook = Join(x, Application.PathSeparator) & Application.PathSeparator
wkbname = GetDir_ThisWorkbook
End Function

Workbooks.Open Filename:=wkbname & "Days Cover.xls"

then on workbook sheet

Private Sub Workbook_Open()
GetDirPath_ThisWorkbook (wkbname)
End Sub

 

[daleyman]

OK, gotcha.

you are trying to pass wkbname through the function, which is not necessary.

change GetDirPath_ThisWorkbook from a Function to a Sub, and take the variable out so it looks like this:

Sub GetDirPath_ThisWorkbook()
'Returns the path from a filespec
Dim x As Variant
x = Split(ThisWorkbook.FullName, Application.PathSeparator)
ReDim Preserve x(0 To UBound(x) - 1)

wkbname = Join(x, Application.PathSeparator) & Application.PathSeparator
End Sub

then on workbook sheet

Private Sub Workbook_Open()
GetDirPath_ThisWorkbook
End Sub

...and this should work. You don't have to pass the variable if it is public.

...Also, you know you could do away with the procedures and public variable completely by just using...

Workbooks.Open Filename:=ThisWorkbook.Path & Application.PathSeparator & "Days Cover.xls"


..or if you are going to need the variable for the file path elsewhere and really want it in a public (if this example is not the real problem) then just declare in module and use...


Private Sub Workbook_Open()
wkbname=ThisWorkbook.Path & Application.PathSeparator
End Sub

 

[brettvba]

Your exactually right thanks for that ,, that what happens when you copy code and try to manipulate it to do what you want.

I was actually using that variable quite a lot in the macros for opening other spreadsheets and that was the only way I could think of having a workbook that could be in any directory and shared accross a network without having to change the code thanks.