none
Excel VBA and UserForm

    Question

  • Hi, Experts.

    1. I have one excel which on opening displays a UserForm.

    2. I have second Excel in which a Macro get on, when a button on UserFrom is clicked.

    3. Macro gets activated in second excel, but it is unable to pass values to UserFrom.

    4. What I tried.

    For Each objWorkbook in Workbooks

    If objWorkbook.Name = "" Then  'Desired name of first excel was put

    objWorkbook.UserForm.TextBox.Value = "1" or

    objWorkbook.UserForm.TextBox.Text= "1"

    End If

    Next

    'And below code also

    Workbooks("First.xls").UserForm.TextBox.Value = "1"

    5. Both code failed.

    6. Where I went wrong? And how do I pass values when two different Userforms are present?

    With regards : Ajay Check

    Wednesday, May 23, 2012 1:16 PM

Answers

  • OK I follow a bit more but there's still much I don't. Instead perhaps it's easier to explain what you can and cannot do when calling procedures with app.Run

    You can pass arguments ByVal but not ByRef. IOW you can "send" values as arguments but not receive them returned changed.

    You can call a function and receive its return value (which could be an array of values).

    With app.run you can only pass values (or arrays) subject the above but not objects (though you can sometimes send enough info in the values to recreate the object the other end).

    So if you want to both send and receive data try something like this (in the example send a value as an argument and receive a return array )

    arg = 123
    arrReturn = Application.Run("myBook.xls!myFunc", arg)
    MsgBox arrReturn(0, 0)
    
    Function myFunc(arg) As Variant
    Dim arr(0 To 2, 0 To 3) ' could declare data type here
    arr(0, 0) = 2 * arg
    myFunc = arr
    End Function

    Peter Thornton

    • Marked as answer by Ajay Check Friday, June 01, 2012 7:40 PM
    Wednesday, May 23, 2012 8:43 PM
  • Based on what you describe there's no reason why you cannot use application run. Simply populate an array with the 3 values produced by your loop. If you are "sending" include the array as one of the arguments. If you are "fetching" assign the array as the return value of the function (in workbook1) called by app.run (called in workbook2).

    Did you try the example I posted last time?

    Peter Thornton

    • Marked as answer by Ajay Check Friday, June 01, 2012 7:35 PM
    Tuesday, May 29, 2012 3:56 PM

All replies

  • Do you really mean you working with two different instances of Excel (as can be seen in the task manger) or do you mean two different workbooks loaded the same instance of Excel, one of which includes a userform and the other some code that you want to communicate with the userform.

    Assuming you are talking about the latter, your two options are -

    - Set a reference in at least one direction between the projects

    - Or simpler but less flexible you can call procedures in normal modules in other workbooks using Application.Run (see help).

    Peter Thornton

    Wednesday, May 23, 2012 1:28 PM
  • Hi Peter,

    I have two different workbooks loaded in the same instance of Excel.

    First Excel as soon as opens, opens a user form. When user clicks a button, to run macro from Second excel (To call Macro from Second I used Application.Run "Second.xls!Macro_name" in code of First excel).

    Now, when this second excel opens and its macro runs. It throws some data, which I want to capture in UserForm. But the same things is not happening.

    With regards: Ajay Check.

    Wednesday, May 23, 2012 4:46 PM
  • When you say "first Excel" and "second Excel" I take it you mean workbooks one and two?

    "But the same things is not happening."

    What's not happening, what code doesn't work, what are you trying to do.

    Does the Application Run work OK to call Macro_name, what does Macro_name do, how do you expect to return data from the form, etc.

    Peter Thornton

    Wednesday, May 23, 2012 5:50 PM
  • Hi Peter,

    1. Yep, you guessed right. There are 2 workbooks. Lets say, First I open a Excel Workbook (Name - "Main Macro.xls").

    2. As soon as this Workbook gets opened, it has UserForm in it, which gets activated.

    3. Now, this UserForm has one button on it "Display", which when clicked opens another workbook (Name- "Display data.xls").

    4.Display data.xls contains a macro ("Count") in it, which gets fired when Workbook is opened.

    5. Now Macro ("Count") works perfectly, but data which I get from this macro, doesn't get displayed in UserForm.

    a. Yes I was able to call Macro from Display data.xls using Application.Run

    b. I made use of code 'Application.Run'  in "Main.xls" to call Macro "Count" from "Display Data.xls". And it ran successfully.

    c. "Count" macro, generates sum of values of each column in Column head.

    d. I want this Sum amount of each column seprately in UserForm Textbox's, which I am not getting, even after using coding mentioned in first post. (Error message : Object doesn't support this property)

    With regards : Ajay Check

    
    
    Wednesday, May 23, 2012 7:56 PM
  • OK I follow a bit more but there's still much I don't. Instead perhaps it's easier to explain what you can and cannot do when calling procedures with app.Run

    You can pass arguments ByVal but not ByRef. IOW you can "send" values as arguments but not receive them returned changed.

    You can call a function and receive its return value (which could be an array of values).

    With app.run you can only pass values (or arrays) subject the above but not objects (though you can sometimes send enough info in the values to recreate the object the other end).

    So if you want to both send and receive data try something like this (in the example send a value as an argument and receive a return array )

    arg = 123
    arrReturn = Application.Run("myBook.xls!myFunc", arg)
    MsgBox arrReturn(0, 0)
    
    Function myFunc(arg) As Variant
    Dim arr(0 To 2, 0 To 3) ' could declare data type here
    arr(0, 0) = 2 * arg
    myFunc = arr
    End Function

    Peter Thornton

    • Marked as answer by Ajay Check Friday, June 01, 2012 7:40 PM
    Wednesday, May 23, 2012 8:43 PM
  • Hi, Peter Thornton and others,

    1. Thanks for explaining Application.Run

    2. But it seems, I can't use this method. Because my second workbook contains a big macro with many Do and one  imp For Next loop. (No. of cycle of For Next varies according to input data).

    3. For Next loop throws three different data values for each loop.

    4. So I want to display these three values in UserForm of first workbook for each round for For Next loop.

    Summary:

    1.I want to display data in Userform of first workbook, from data generated by a macro of second workbook.

    2. Can't use Application.Run to get output value because, there is no simple function like addition where i can just pass two values and get result, but a big module with For Next loop, generating three different values in each loop.

    Tuesday, May 29, 2012 1:50 PM
  • Based on what you describe there's no reason why you cannot use application run. Simply populate an array with the 3 values produced by your loop. If you are "sending" include the array as one of the arguments. If you are "fetching" assign the array as the return value of the function (in workbook1) called by app.run (called in workbook2).

    Did you try the example I posted last time?

    Peter Thornton

    • Marked as answer by Ajay Check Friday, June 01, 2012 7:35 PM
    Tuesday, May 29, 2012 3:56 PM
  • Hi, Peter and All.

    1. Thanks Peter, I used Application.Run successfully and got desired results, by passing arguments.
    2. Since mine project had long and complex modules, I was not ready to change/look into them. But, after trying your code and above explanation, when I changed the code to suit for Application.Run method, it worked in desired way.
    3. And while working with this, I could feel I have added little bit more info, about UserForm and Macros behaviour.
    • Yes Peter your example/code was very useful and clear.
    • After trying it, I could feel comfortable with Application.Run method.


    With Regards : Ajay Check


    • Edited by Ajay Check Friday, June 01, 2012 7:39 PM
    Friday, June 01, 2012 7:35 PM
  • Glad it all seems to be working and thanks for the feedback.

    Peter Thornton

    Friday, June 01, 2012 9:37 PM