none
ATPVBAEN.XLAM!Random not working in excel 2016- need help RRS feed

  • Question

  •  I wrote a large number generation macro that uses the "discrete" random number generator. It uses the discrete form as it needs to match an existing table of probabilities rather than be a set distribution. I wrote it 2.5 years ago in 2013 and it worked fantastically, generating me 45K random sets of data with a variety of characteristics, all based on probability tables.

    I've updated to 2016 and it now refuses to work. I've done a variety of debugging and it's definitely the Random function that causes the macro to hang and make it impossible to break resulting in a "hard" reset from task manager.

    Here is an example of the function it generates 45555 random numbers from a table of 1761 "probabilities".

    Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("$AF$2"), 1, _
            45555, 7, , ActiveSheet.Range("$A$2:$B$1761")

    I have even written a new single function macro number generator with this same function and it works without a problem but any time I try to run this in it's existing macro it hangs. I've even isolated this function on it's own (deleted the rest of the maco) and it still doesn't run, yet when I copy it across into the new small macro it works without hanging. Likewise if I run the macro without any of the Random functions it performs exactly as it should.

    I've tried switching on the tools->reference->atpvbaen.xls 

    Stupid options considered- delete the existing modules/macos, open new ones and copy the vba code across.

    Is the ActiveSheet wrong? do I need to put in the sheet address, and if so what is the syntax?

    Is there something screwy in the permissions/security? I'm an administrator.

    I really need to know why this causes such a critical failure, it's not even an error just an infinite unbreakable loop.

    Your help would be very much appreciated.

    Sunday, October 25, 2015 7:09 PM

Answers

  • Hi Funkmiester,

    Thanks for the detail information for this issue.

    I am also could reproduce this issue when generate the rand numbers manually. It seems that when we use this feature through UI, Excel will valid the parameter before the method called.

    If you want Excel break this limitation, you can try to submit the feedback from link below:

    >>I have since found that turning the formula calculate to Manual from Automatic allows the code to run. So my sheet that ran perfectly well in 2013 won't run 2016 as the automatic formula calculate freezes the random number generator or vice versa. Doesn't make sense to me<<

    When execute the code above in Excel 2016, it would cost some time since the larger data however still works well for me.  I suggest that you try to repair and update the Office and reboot the computer to see whether the issue could be fixed.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 29, 2015 10:21 AM
    Moderator

All replies

  • Plot thickens, after re-installing and all sort of pointless stuff it would appear that MS has capped the maximum number of data generated to 32767. Why??????? It used to run just fine on 45555. Why would you change such a thing? Is there a work around?
    Monday, October 26, 2015 9:07 AM
  • Ill keep posting in the hope someone might help, I'm still troubleshooting. When you open a "new" blank workbook and use the data random number generator, discrete function it works fine (up to 32K). Then if I open my "old" workbook alongside, do nothing with it and go back to the "new" sheet and try to rerun the function that just worked, it hangs with a hard reset needed. How can having one sheet open, with no macros running affect a single simple sheet changing a function that has just run into one that hangs?

    Tuesday, October 27, 2015 9:59 AM
  • Hi,

    As far as I test, the ATPVBAEN.XLAM!Random still works well for 45555.

    Based on my standing, if we want to write "discrete", there is no need to provide second parameter(ActiveSheet.Range("$AF$2")). Here is the test code works well for me for you reference:

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        Sheets("Sheet4").Select
         Application.Run "ATPVBAEN.XLAM!Random", "", 5, 45555, 7, , ActiveSheet. _
            Range("$A$12:$B$16")
    End Sub

    Data in sheet4:

    To use this function, we need to check the add-in via Developer->Excel Add-ins->Analysis ToolPak - VBA. And if that the function can work manually, we can using Macros Recording feature to get a quick code demo for the reference.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, October 27, 2015 10:10 AM
    Moderator
  • 

    Thank you very much for your help Fei, it is really appreciated

    The second parameter is required if you want to put the data in a specific place. which I do.

    As part of the debugging I was trying the function manually by going to data->data analysis etc. It would seem that the non VBA analysis toolpack random generator does have a limit of 32767 but, as you point out the VBA toolpack can easily do 45555. Thank you, whenever I tried this I had other things open which leads onto another issue:

    I have since found that turning the formula calculate to Manual from Automatic allows the code to run. So my sheet that ran perfectly well in 2013 won't run 2016 as the automatic formula calculate freezes the random number generator or vice versa. Doesn't make sense to me.

    SO what have we learned- the random generator works differently from the ribbon than in VBA

    One or more of my other formulas outside the VBA causes it to hang completely if left in the calculate automatically mode, yet will F9 or "calculate now" without an issue and every sheet has been checked with the "error checking" function and no errors or circular references etc.

    With what I know now I can turn of calculate as the first line of the VBA and it should run

    Does anyone know what might cause it to hang and why can't it be broken with "Esc", shft+break or ctrl+break?

    It would be helpful to have a library index of all the functions and their parameters, is there one, even for obscure functions like the one I am using? One that would show the limit of the generator on the ribbon and that it is difference from the VBA code?

    Again many thanks

     

    Tuesday, October 27, 2015 1:38 PM
  • Hi Funkmiester,

    Thanks for the detail information for this issue.

    I am also could reproduce this issue when generate the rand numbers manually. It seems that when we use this feature through UI, Excel will valid the parameter before the method called.

    If you want Excel break this limitation, you can try to submit the feedback from link below:

    >>I have since found that turning the formula calculate to Manual from Automatic allows the code to run. So my sheet that ran perfectly well in 2013 won't run 2016 as the automatic formula calculate freezes the random number generator or vice versa. Doesn't make sense to me<<

    When execute the code above in Excel 2016, it would cost some time since the larger data however still works well for me.  I suggest that you try to repair and update the Office and reboot the computer to see whether the issue could be fixed.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 29, 2015 10:21 AM
    Moderator