+ Reply to Thread
Results 1 to 15 of 15

Going from Storage to a Count

  1. #1
    Registered User
    Join Date
    09-13-2011
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    8

    Going from Storage to a Count

    Hi All,

    I'm really new to VBA and I'm currently having some issues that I hope someone could help me with or at least point me in the right direction. Right now, I have a VBA program that looks at a column of randomized data and determines if it is higher than a certain probability. In Column A I have a running count of how many times that happens in each column. Following that, I have a few equations that get calculated from that data.

    What I need to do, instead of the current set up as when I have it run a large amount of replications it becomes very slow, is make it so that the randomized data doesn't get stored, but just gets counted. Each time one of the replications runs with no # over the probability, I need it to count 1. After all replications are run, it should then do the equations.

    I just really need suggestions for how that could be completed at this point. I've gone through a bunch of ideas, but the only way I was able to make it work correctly was storing all the data.

    Any help would be great. Thanks all, I look forward to some good brainstorming!
    Attached Files Attached Files
    Last edited by Phlyers18; 09-14-2011 at 08:01 PM.

  2. #2
    Registered User
    Join Date
    09-13-2011
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Going from Storage to a Count

    I just did quite a bit of editing to something that should theoretically work...but I'm still having issues. Anytime I try to run the program, it just freezes excel.

    Any thoughts would be great. I'm now really at a loss.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-13-2011
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Going from Storage to a Count

    In case people don't want to download the .xlsm (I just realized people might be weary of that...)

    Sub Button8_Click()

    Dim count, vari, counter, Rep_Counter, Zero_Counter, reps, patients As Integer
    Dim CountAccept As Integer
    Dim n, r, p As Single
    Dim sd, zero_fraction As Double


    n = Cells(1, 2)
    r = Cells(2, 2)
    p = Cells(3, 2)

    'Generates random numbers
    For reps = 1 To r
    count = 0
    For patients = 1 To n
    Do
    vari = "=rand()"
    If vari < p Then Set count = count + 1
    Loop While counter < n
    Next patients
    If count = 0 Then Set Zero_count = Zero_count + 1
    Next reps

    zero_fraction = Zero_count / reps

    sd = Sqr(((zero_fraction) * (1 - zero_fraction) / reps))


    'MsgBox ("Probability no one accepts organ is " & Range("A10") & " and " & Range("A12"))
    Cells(3, 3) = zero_fraction


    End Sub

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Going from Storage to a Count

    Please Login or Register  to view this content.

    counter is defined but not incremented; count is.


    Please Login or Register  to view this content.

    Sets vari equal to a text value, =rand(), not a random number.

    Most of your variables will be variants as they are not explicitly defined.

    Please Login or Register  to view this content.

    Only Zero_fraction is actually defined as Integer.


    Regards
    Last edited by TMS; 09-13-2011 at 05:14 PM.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Going from Storage to a Count

    Amended code ... don't know if it gives you what you want:

    Please Login or Register  to view this content.
    Regards
    Last edited by TMS; 09-13-2011 at 05:23 PM.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Going from Storage to a Count

    Sorry, have run the code now I have more confidence that it won't crash/loop and just noticed that you don't put anything in A10 and A12


    Regards

  7. #7
    Registered User
    Join Date
    09-13-2011
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Going from Storage to a Count

    Quote Originally Posted by TMShucks View Post
    Sorry, have run the code now I have more confidence that it won't crash/loop and just noticed that you don't put anything in A10 and A12


    Regards
    I should have noted a bit was yet to be completed to the code, so that is certainly alright. I had looked at the code so much that I missed some apparently obvious notation changes. With your changes it at least runs without error.

    My end goal is for the to determine a range of probabilities by adding/subtracting 1.96 to SD. So, I already know I need to change Cells(3,3) = Zero_Fraction to Cells(3,3) = SD.

    I can't currently tell if it is doing anything at this point though. The initial .xlsm that I posted actually worked, but just could be bulky and would be a pain with its intended usage. It generated random numbers in set sizes determined by inputs for an input of replications. It should read through every column and determine if any column had any instances where a number didn't go over the listed probability...if a column, did, it would be added to the Zero_Count.

    Sub Button8_Click()

    Dim count As Integer, vari As Integer, Zero_Count As Integer, reps As Integer, patients As Integer, Zero_fraction As Integer
    Dim n As Single, r As Single, p As Single
    Dim sd As Double

    n = Cells(1, 2)
    r = Cells(2, 2)
    p = Cells(3, 2)

    'Generates random numbers
    For reps = 1 To r
    count = 0
    For patients = 1 To n
    Do
    vari = Rnd
    If vari < p Then count = count + 1
    Loop While count < n
    Next patients
    If count = 0 Then Zero_Count = Zero_Count + 1
    Next reps

    Zero_fraction = Zero_Count / reps

    sd = Sqr(((Zero_fraction) * (1 - Zero_fraction) / reps))

    Cells(3, 3) = sd
    End Sub
    Last edited by Phlyers18; 09-13-2011 at 05:45 PM. Reason: forgot new code

  8. #8
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Going from Storage to a Count

    hi, Phlyers18, please check attachment, push the button, hope this helps
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-13-2011
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Going from Storage to a Count

    Quote Originally Posted by watersev View Post
    hi, Phlyers18, please check attachment, push the button, hope this helps
    Only problem I've noticed with this one is that it gets an error when running large numbers. Not sure why though.

  10. #10
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Going from Storage to a Count

    please check attachment, the code has been modified.

    Range B1:B2 is checked for having integers by validation tool. Code comments added
    Last edited by watersev; 09-14-2011 at 04:37 AM.

  11. #11
    Registered User
    Join Date
    09-13-2011
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Going from Storage to a Count

    Quote Originally Posted by watersev View Post
    please check attachment, the code has been modified.

    Range B1:B2 is checked for having integers by validation tool. Code comments added
    That works rather smoothly now, though a change in the probability line prompts me with an error of "Acceptable Value Range".

    My main question now though is can this be made into a system where the generated numbers don't appear in the sheet at all? Currently, excel constrains the program to only be able to run 254 replications and I'm trying to make it potentially run thousands. This way I can just have the program output the final answers I'm looking for.

    Sorry if these questions are confusing, but as I said, I'm very new to all this. Thanks again for any help.

  12. #12
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Going from Storage to a Count

    amended (removed validation from B3 cell value)

    What values would you like to have on the worksheet?

  13. #13
    Registered User
    Join Date
    09-13-2011
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Going from Storage to a Count

    Quote Originally Posted by watersev View Post
    amended (removed validation from B3 cell value)

    What values would you like to have on the worksheet?
    Besides the inputs, I just wanted to have the +/- 1.96 values displayed. Everything else isn't necessary for the user to see.

    Is there a way to allow for more than 254 replications or is excel a limiting factor?

  14. #14
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Going from Storage to a Count

    please check attachment. As soon as you do not need probability data and zeroes counting array on the sheet, the code will work until you have patience to wait for the result.

  15. #15
    Registered User
    Join Date
    09-13-2011
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Going from Storage to a Count

    Quote Originally Posted by watersev View Post
    please check attachment. As soon as you do not need probability data and zeroes counting array on the sheet, the code will work until you have patience to wait for the result.
    I think this is exactly what I needed. Wow, thank you so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1