Going from Storage to a Count

**Phlyers18** · 09-13-2011, 02:49 PM

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!

**Phlyers18** · 09-13-2011, 04:08 PM

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.

**Phlyers18** · 09-13-2011, 04:16 PM

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

**TMS** · 09-13-2011, 05:10 PM

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

**TMS** · 09-13-2011, 05:21 PM

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

Please Login or Register  to view this content.

Regards

**TMS** · 09-13-2011, 05:24 PM

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

**Phlyers18** · 09-13-2011, 05:44 PM

Originally Posted by TMShucks

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

**watersev** · 09-13-2011, 05:51 PM

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

**Phlyers18** · 09-13-2011, 07:02 PM

Originally Posted by watersev

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.

**watersev** · 09-14-2011, 03:15 AM

please check attachment, the code has been modified.

Range B1:B2 is checked for having integers by validation tool. Code comments added

**Phlyers18** · 09-14-2011, 10:03 AM

Originally Posted by watersev

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.

**watersev** · 09-14-2011, 11:14 AM

amended (removed validation from B3 cell value)

What values would you like to have on the worksheet?

**Phlyers18** · 09-14-2011, 11:24 AM

Originally Posted by watersev

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?

**watersev** · 09-14-2011, 04:48 PM

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.

**Phlyers18** · 09-14-2011, 08:00 PM

Originally Posted by watersev

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.

Going from Storage to a Count

LinkBack

Thread Tools

Rate This Thread

Display

Going from Storage to a Count

Re: Going from Storage to a Count

Re: Going from Storage to a Count

Re: Going from Storage to a Count

Re: Going from Storage to a Count

Re: Going from Storage to a Count

Re: Going from Storage to a Count

Re: Going from Storage to a Count

Re: Going from Storage to a Count

Re: Going from Storage to a Count

Re: Going from Storage to a Count

Re: Going from Storage to a Count

Re: Going from Storage to a Count

Re: Going from Storage to a Count

Re: Going from Storage to a Count

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions