Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    173
    Thanks
    27
    Thanked 0 Times in 0 Posts

    Using RefEdit to return multiple addresses

    I want to be able to get results from a series of out put cells on my worksheets when I run a monte carlo simulation.

    The starting point is a UserForm that has a RefEdit control. It works fine if I want to process only one out put but I am having trouble if I want more than that. I realise I could add a second RefEdit control etc but the problem with that approach is I do not know how many outputs I want.

    I have tried this approach in change, after update and exit on the form.
    Code:
    Private Sub RefEdit1_AfterUpdate()
    Set SelRangeV = Range(RefEdit1)                                 'Sets Range to Cell Selected
    Response = MsgBox("Any More Outputs", vbYesNo)  'Any More Outputs
    Set SelRangeArr(RngCnt) = SelRangeV                         'Sets Cell Address to the Array for Outputs
    If Response = "vbyes" Then
    RngCnt = RngCnt + 1
    End If
    ReDim Preserve serangearr(RngCnt)
    End Sub
    That does not work. I have tried the same code in a Module, and while that does result in the msgbox appearing for the response when I click on yes the user form disappears.

    As can be seen I was hoping to use the same RefEdit control for each output.

    Any suggestions greatly appreciated.

    Thanks

    Peter

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,510
    Thanks
    3
    Thanked 143 Times in 136 Posts
    I am not sure I am understanding what you actually want to do with the output but...

    Have you thought of using either a listbox or combobox control to display your output info. You can add an entry for each of the outputs into the same control and then if you need to process that list you can simply iterate through however many entries there are.

    I note that Response is a MsgBox. I would expect that Response therefore returns an Integer but your code checks it against a string - this would stop RngCnt from ever incrementing since the if statement would never be true.

    Your code appears to not be changing the value in RefEdit1 which makes me think the monte carlo is actually changing the value in this control and that macro kicks off on each run. In short, I don't really know what that code is trying to do at all.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    mitchbvi (2014-06-07)

  5. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    173
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Andrew

    First thank you for taking the time to reply to my problem. My post was not from the actual routine I am running, I thought it easier to try and explain my problem with a simple user form.

    So as to what I want to do :
    A user form collects the cell address output which is a result of calculations in the worksheets. It is the result as you would imagine of calculations based on various random inputs.

    It collects the number of times the worksheet is to be calculated. Also a the number of bins for a frequency array.

    The routine goes on to produce a histogram and cumulative frequency chart based on the out put results.

    This all works for one output, but what I want to do is select a series of outputs and produce the same graphs for each one.

    I have used the response vbyesno in a number of other routines and it has worked. In this one i have not managed to get as far as rngcnt so I do not know. On reflection maybe the vbyes does not require quotes.

    Had thought about using list box or combo. Think Combo would be better as I think you can populate it as you go but not sure how to do that.

    Thanks again, by the way Perth was home for most of my life its great to get help from OZ.

    Take care

    Peter

  6. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,510
    Thanks
    3
    Thanked 143 Times in 136 Posts
    Peter

    In that case, I don't know why you are trying to populate the data into the userform at all. Any of your subsequent analysis and graphing would require the data somewhere else so you may as well store it there with each pass of the monte carlo. You can put a copy into the userform if you want to see it building but what processing do you hope to achieve there?

    I had initially considered collecting the monte carlo data outputs in a recordset and then sort the recordset to simplify categorisation of the data. However since you want to graph the data it might be best to simply write it to another worksheet so you can sort it there for whatever data processing you want to do.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  7. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    173
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Sorry did not explain the issue properly, I am not trying to populate the User Form just collect the address's of the Out Put Cells. The main part of my code process the worksheet collecting the result of each iteration in an array. The only data I put on a worksheet is the spread of the Bins and the Frequency array. That is a lot faster I have found and I am not limited by the number of vertical cells.

    I am nearly there I think. This is code I built in a separate Workbook just to test the process.

    Code:
    Private Sub CommandButton1_Click()
    Dim SelRangeArr(1), SelRangeV As Range, SelRangeVArr(1)
    Set SelRangeV = Range(RefEdit1)
    SelRangeArr(rngCnt) = RefEdit1                                       'Returns the Address of Selected Cell
    SelRangeVArr(rngCnt) = SelRangeV.Value                         'REturns the Value This for testing Purposes
    MsgBox "Address " & SelRangeArr(rngCnt) & vbCr & "Value " & SelRangeVArr(rngCnt)
    Response = MsgBox("Any More Output Cells", vbYesNo)     'Process any more OutPut Cells
        If Response = vbYes Then
        CarryMsg = CarryMsg & SelRangeArr(rngCnt) & " " & SelRangeVArr(rngCnt) & vbCr
        RefEdit1 = ""                                                               'Zeros the RefEdit Control
        RefEdit1.SetFocus                                                       'Focus on Refedt control
        Else
        MsgBox "Cells Selected " & vbCr & CarryMsg & SelRangeArr(rngCnt) & " " & SelRangeVArr(rngCnt)   'Testing make sure the value and address are being returned
        End If
    
    End Sub
    A lot of this is for testing as you can see, all I am after is the address assigned for each output "SelRangeArr(rngCnt)". The actual array that accumulates the data is two dimensional (or will be when I get it working) one for the calculation and the other for the desired cell. Not sure that will work but I will keep trying.

    Happy to post all of the code if that would make it any clearer. However my coding skills are somewhat rusty having started hardwiring IBM 601's many years ago.

    Take care

    Peter

  8. #6
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,510
    Thanks
    3
    Thanked 143 Times in 136 Posts
    In your code, there are only two possible values for SelRangeArr(rngCnt). You declared this variant array variable so it that rngCnt can only be 0 or 1.

    To build a loop to see each of these values you can do something like
    Dim i as integer
    For i = LBound(SelRangeArr) to UBound(SelRangeArr)
    debug.print SelRangeArr(i)
    Next i
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  9. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    mitchbvi (2014-06-09)

  10. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    173
    Thanks
    27
    Thanked 0 Times in 0 Posts

    Think I may Have it

    First My thanks for staying with me on this , it has been of great help. I was having trouble as you would have guessed and I had also posted a request on another Forum. The suggestion from that was I use the Ctrl button to allow a number of cells to be selected and the split function to split the result into an array. You were correct of course about the rngcnt that was dumb of me. This code seems to be working at least the test message box is returning the results of a random calculation for each cell on each iteration. I have to transfer that to the main body of my code so we will see. This is the result of your suggestions and those from Dave at MrExcel.

    Code:
    Private Sub CommandButton2_Click()
    Dim SelRangeVarr() As String, SelRangeV, ArrData(), Test
    SelRangeVarr = Split(RefEdit2.Value, ",")
    reCalc = TextBox1                                                   'No of times the worksheet is to be recalculated
    ReDim ArrData(reCalc, UBound(SelRangeVarr))        'Redimensions the Data Array to the No of Required Calcuations and the No of OutPut Cells
        For n = 0 To reCalc
        Application.Calculate                                           'Calculates the Worksheet
            For i = LBound(SelRangeVarr) To UBound(SelRangeVarr)
                Set SelRangeV = Range(SelRangeVarr(i))      'Sets The Range
                ArrData(n, i) = SelRangeV.Value                 'Assigns the Value of the Calculation for Each Out Put Cell
                Test = Test & "Test " & " Iteration " & n & " " & ArrData(n, i) & vbCr  'For Testing Purposes
            Next
        Next n
        MsgBox Test
    Unload Me
    End Sub
    Take care and thank you again.

    Peter

  11. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    173
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Hi Andrew

    I have started a new thread as I have run into a problem sorting my results and I thought that was the correct thing to do, and wanted to let you know in case you have any ideas. I have spent al day on trying to sort a two dimensional array and got nowhere.

    Take care

    peter

Tags for this Thread

Posting Permissions

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