Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Assigning a variable a permant value till it is reassigned

  1. #11
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  2. #12
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  3. #13
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •