Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Glasgow, United Kingdom
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pass a variable's value (OffXP)

    I need to pass a variable's value between projects in different workbooks
    I vba help seems to say that by declaring the variable as "Public" in the module, all projects in all workbooks would "see" the same value but that doesn't seem to work
    I'm sure you experts have a very simple answer to this but as a rookie it's baffled me for hours and I can't find anything in vba help other than the above comment
    A rough of my code is below - appreciate any help
    Mike
    --------------------------------------------
    'Code in Book1.xls......
    Option Explicit
    Public x, y, flag

    Sub test()
    'blah blah
    x = 1
    y = 2
    Application.Run "Book2.xls!anothertest"
    '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    If flag = "donething1" Then
    'do something
    ElseIf flag = "donething2" Then
    'do something
    Else
    End If
    'blah blah blah
    End Sub
    --------------------------------------------
    'Code in Book2.xls......
    Sub anothertest()
    If x = 1 Then
    'do thing1
    flag = "donething1"
    Exit Sub
    ElseIf y = 2 Then
    'do thing2
    flag = "donething2"
    Exit Sub
    Else
    End If
    End Sub

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a variable's value (OffXP)

    Declaring as Public only works for code in the same workbook, not between workbooks. If both workbooks are open at the time, you can create a Name and store it at the Application level then reference it with the full workbook name.

    <pre>' in Workbook Book1
    Application.Names.Add "Woody1", RefersTo:="Woody is here"
    ' in Workbook Book2
    Application.Names.Add "Woody2", RefersTo:="Woody is here too"
    ' Refer to the Name from either workbook
    Workbooks("Book1").Names("Woody1")
    ' returns ="Woody is here"
    Workbooks("Book2).Names("Woody2")
    ' returns ="Woody is here too"</pre>

    If needed, you could then strip the leading =" and trailing " from the stored Name or just use it as is depending on what it's used for.

    Deb <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a variable's value (OffXP)

    Why not pass them as arguments in the Run method?

    <pre> Application.Run "Book2.xls!anothertest", x, y, flag
    </pre>

    Legare Coleman

  4. #4
    Lounger
    Join Date
    Jul 2002
    Location
    Glasgow, United Kingdom
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a variable's value (OffXP)

    Thanks for that Legare
    Great to get the first variable into the called project in the other workbook - done!
    But how do I then get a different variable value from there (when it hits "exit sub") back into the origonating project in the first workbook
    Mike

  5. #5
    Lounger
    Join Date
    Jul 2002
    Location
    Glasgow, United Kingdom
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a variable's value (OffXP)

    Deb - I never would have thought of using a name like that. I thought they were just for naming cells on a sheets!
    Up to now I've been shoving the values into cells to transfer them - with all the extra code lines for worksheet protection, events etc etc
    I'll certainly have a go with that idea - it'll save me loads of code lines - and make it much easier to read
    I assume I can put the declarations into the Auto_Open?
    Should they be "undeclared" in the close code or will excel dump them when shut
    Sorry if they're basic questions but as you can see I'm on a (steep) learning curve!
    Thanks
    Mike

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a variable's value (OffXP)

    Yeh, names are great, I use them a lot, just think of them as a variable that can be assigned globally to the workbook or to a worksheet, they're always Public. <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>

    To answer your questions:
    1. Yes you can put their declaration in Auto_Open() or Workbook_Open() but you only need to do it once since they stay with the worbkook until they're deleted. If, however you want to define them differently each time the workbook is opened, then you'd need to do this in Auto_Open(). The code would be:
    <pre>ThisWorkbook.Names("Mike1"), RefersTo:="This is a message"
    ThisWorkbook.Names("Mike2"), RefersTo:=12345</pre>


    2. They do not need to be undeclared since they will stay with their parent workbook until you delete them. You can delete them manually via the Insert/Names/Define menu or use code:
    <pre>ThisWorkbook.Names("Mike1").Delete</pre>


    The "ThisWorkbook" prefix isn't required but to me it's more clear that this is a workbook-level name not a worksheet-level name. You can have the same name, Mike1, defined at the workbook or the sheet level.

    I recommend readiing up on names and practicing with them, they're very useful and have made my projects so much eaiser to handle. Be careful about the name you choose for your Name, it can't resemble a cell address in any form. You can't use something like "C5 " for a Name since it resembles a cell address. Keep your names on the short side so it's easier to read them in the 'name' box in the formula bar. I also recommend hiding your names so users can't delete them (of course if your workbook is protected them can't do this anyway but I'd rather keep them invisible as well).
    <pre>ThisWorkbook.Names("Mike1"),RefersTo:="123 Main Street", Visible:=False</pre>


    Deb <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a variable's value (OffXP)

    You can make the Sub a Function and return a value.
    Legare Coleman

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a variable's value (OffXP)

    There is one drawback with names: They can be (inadvertantly or not) changed (and deleted) by your user. One way of making that more diffcult is to hide the name (Set it's visible property to false):

    Workbooks("YourBook").Names("YourName").Visible=Fa lse

    But if one has my name manager (download at the Excel MVP site as stated below my sig), it is simple to show hidden names (and change them).

    As long as this code is just for you, there is no problem of course. If it is for other users, you'll have to take into account that your names could be changed.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Lounger
    Join Date
    Jul 2002
    Location
    Glasgow, United Kingdom
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a variable's value (OffXP)

    Thanks Deb and Jan - it works and I'll certainly use the "visible" = false trick
    Mike

  10. #10
    Lounger
    Join Date
    Jul 2002
    Location
    Glasgow, United Kingdom
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a variable's value (OffXP)

    I'm interested in that Legare but I'm still learning and at this stage I've never used a Function (in fact I don't really understand them!!)
    How would I do that? Just change the "Sub" in the second workbook to Function with the same name? and how does it get passed back?
    If it's going to too complex for a simple explanation at this stage don't worry about it - I've solved my immediate problem using the Names and your other tip on passing the variable in the Sub's name brackets has also proved very helpful for another little project
    Greatful for all the help
    Mike

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a variable's value (OffXP)

    You would use a function something like this:

    <pre>Public Sub Test()
    Dim x As Integer, y As Integer, flag As String
    flag = AnotherTest(x, y)
    If flag = "donething1" Then
    MsgBox "Thing1 done."
    ElseIf flag = "donething2" Then
    MsgBox "Thing2 done"
    Else
    MsgBox "Nothing done"
    End If
    End Sub

    Function AnotherTest(x As Integer, y As Integer) As String
    If x = 1 Then
    'do thing1
    AnotherTest = "donething1"
    Exit Function
    ElseIf y = 2 Then
    'do thing2
    AnotherTest = "donething2"
    Exit Function
    Else
    AnotherTest = "nothingdone"
    End If
    End Function
    </pre>


    However, when I wrote that I had forgotten you were using the Run statement to run this in another workbook. I don't think you can Run a Function. Therefore, I would do it like this:

    <pre>Public Sub Test()
    Dim x As Integer, y As Integer, flag As String
    Application.Run "Book2.xls!anothertest", x, y, flag
    If flag = "donething1" Then
    MsgBox "Thing1 done."
    ElseIf flag = "donething2" Then
    MsgBox "Thing2 done"
    Else
    MsgBox "Nothing done"
    End If
    End Sub

    Sub AnotherTest(x As Integer, y As Integer, ByRef flag As String)
    If x = 1 Then
    'do thing1
    flag = "donething1"
    Exit Sub
    ElseIf y = 2 Then
    'do thing2
    flag = "donething2"
    Exit Sub
    Else
    flag = "nothingdone"
    End If
    End Sub
    </pre>

    Legare Coleman

  12. #12
    Lounger
    Join Date
    Jul 2002
    Location
    Glasgow, United Kingdom
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a variable's value (OffXP)

    Thanks for the follow-thru Legrande
    I tried that after your first reply but I can't get it to work
    I've copied your code into 2 books 1 & 2 exactly as you've got them
    I set the flag to 1 by adding x=1 in the book 2 code - it recognises that and returns on the first exit sub

    But when the code returns to book1 the value of the flag is still ""
    If I add a watch to both flags, they're shown as 2 separate variables, each in the context of their respective books
    flag in book2 changes and gets the x or y value BUT
    flag in book1 never changes from "" value - so it always gives the message "nothing done"
    Thanks for trying though
    Regards
    Mike

  13. #13
    Lounger
    Join Date
    Jul 2002
    Location
    Glasgow, United Kingdom
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a variable's value (OffXP)

    whoops
    s-o-r-r-y for typing your name wrong in that last post Legare (blush) !!!

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a variable's value (OffXP)

    I should have tested that before sending it to you. It appears that the Run method does not use ByRef in its arguments, and therefore any changes made in the run sub are not passed back. So far I have not found a way around this.
    Legare Coleman

  15. #15
    Lounger
    Join Date
    Jul 2002
    Location
    Glasgow, United Kingdom
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a variable's value (OffXP)

    Don't sweat it Legare
    Thanks for your time and efforts
    Regards
    Mike

Page 1 of 2 12 LastLast

Posting Permissions

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