Advice required...Excel's ability to create a user interactive simulation module...

Morning All

I am looking for advice on whether the simulation in the link below can ever be achieved by making use of Excel VBA...
Site will not allow me to attach link as per usual...

https://seeing-theory.brown.edu/freq....html#section2

In Chapter 4, go to the Confidence Interval module and choose “Normal” distribution, n = 10 and 1 – α = 0.95.
Then select “start sampling”.

Thank you in advance for your valuable input and suggestions...

Anyone have any thoughts...Perhaps some links that might assist...

https://seeing-theory.brown.edu/freq....html#section2
Nobody...Really...That's a first...

Short but useless answer -- I am not a web/javascript programmer, but it looks like they are using javascript to perform the simulation. I know next to nothing about javascript, but I don't see that they are doing anything magically unique to javascript that could not be translated into VBA (or Excel worksheet functions or python or any other programming language). My short, useless, yes/no answer is, yes, it is possible to do something like this in VBA.

The linked website does not offer much explanation of what they are doing, and I am not enough of a statistician to easily infer what they are doing from the visualization alone. It looks like they are illustrating some kind of resampling technique (https://en.wikipedia.org/wiki/Resampling_(statistics) ). As with a lot of programming problems, I like to break it down into a flowchart or outline before I tackle the actual programming.

1) Assuming this is about resampling techniques, figure out and research the kind of resampling techniques you want to put into this. Also identify the essential and important elements of the project (for example, is the animation essential to your project).
2) Once you understand conceptually what you need to do.
3) Resampling -- seems to usually involve a) generating uniform random numbers (VBA's Rnd() function), then using those numbers to resample the distribution or sample set.
4) Compute and store statistics for the resampled set.
5) Work out visualization and animation aspects of the project.

None of the details for this are clear to me, so I don't yet have any specific recommendations. With that outline, what parts are you having trouble with?

Hi MrShorty

Tx for you input...I am currently developing a 21 module Statistical Analytical Add-in with the below components...
The next step...if possible...was a requirement to make use of the module outputs to create the visual simulation of each module below...
This link was an example of what the client was wanting...Was just enquiring if someone had ever come accross something similar which might give me an indication of how visuals in excel works...i.e. using data and loops to create the consistent visuals...

Private Sub UserForm_initialize()
Set Uform = Me
'Call HideUFX
Me.ListBox1.List = Array("Descriptive Statistics", "Summary Tables and Graphs", "Correlation Analysis", _
"Confidence Interval: Single Mean", "Confidence Interval: Single Proportion", _
"t-Test: Single Mean", "z-Test: Single Mean", "z-Test: Single Proportion", _
"F-Test: Equality of Variances (Two Populations)", "t-Test: Two Means (Equal Variances)", _
"t-Test: Two Means (Unequal Variances)", "t-Test: Matched Pairs", "z-Test: Two Means", _
"z-Test: Two Proportions", "Anova: One Factor", "Anova: Two Factor (with interaction)", _
"Anova: Two Factor (without interaction)", "Chi Square: Independence of Association Test", _
"Chi Square: Goodness-of-Fit Test", "Chi Square: Single Variance Test", "Multiple Regression")
ListBox1.Selected(0) = True
End Sub

Came across a site... which has a few examples...See attached...
None as elaborate as the link in above post...
http://iris.nyit.edu/~fgordon/Statis...imulations.htm

what parts are you having trouble with?

In my first link, I can easily replicate the left side Bar chart with randomizing...
The right side chart is what is baffling me...How would one achieve such visuals - if at all possible...I mean what kind of chart would be used to replicate such an output???

The right side chart is what is baffling me...How would one achieve such visuals - if at all possible...I mean what kind of chart would be used to replicate such an output???

It looks to me like a "basic" XY scatter chart. What exactly do you find baffling? Drawing the distribution (static) should be straightforward. The "difficult" parts are probably the animation parts. I would expect a static (non-changing) data series for visualizing the distribution. If you don't need the animation, draw the random sample/resample sets as another data series (at a suitable y value to place them where you want them on the chart), then draw the mean+error bars as a third data series. [up to this point, I see no particular need for VBA].

If the animation is essential, then you would add some VBA to continuously/periodically change the y and/or x values for the sample/resample data series and/or the mean+error bars data series to give the desired animation. Again, I am not sure what part you are baffled by. I believe you have the VBA skill to manipulate the values in a spreadsheet range and how to access Excel's functions in VBA (if/when needed).

At this point, even if the animation is essential, I would focus on getting the static, non-animated version working (I would probably do this with all spreadsheet formulas, but it could be just as easily done using VBA). Once the non-animated version is working, then it should be relatively easy to add the animation.

Am going to get some more info, put a demo together and I would like to revisit this post later in the week...
Thanks again for your continued response...WATCH THIS SPACE...

Hi Mrshorty

Just an update...See attached sample file...A work in progress...
User selects values...Once mean & std dev is selected that chart remains constant for comparison...
User then plays around with sample size and number of samples...
Just need to streamline the transition when the charts source data changes...
Any suggestions would be appreciated...

I understand that when the chart's source data is populated via a range that is controlled by formulas, the transition when data changes is seamless...I have come to realize that the chart construct itself will remain constant...the data will always change though...larger or smaller...How can I get the chart to visually become smoother when my code creates new source data...
I have kept the chart construct and am trying to make the change by setting the new source data after it is created??/

Option Explicit

Sub Histogram1()
Dim Chrt As Object
Application.ScreenUpdating = False
Set Chrt = ActiveSheet.ChartObjects("Fixed")
With Chrt.Chart
     .SetSourceData Source:=Histdata1, PlotBy:=xlColumns
     .ChartType = xlColumnClustered
    .HasTitle = True
    .ChartTitle.Text = "Underlying Normal Population (µ = " & Mean & ", " & ChrW(963) & " = " & StdDev & ")"
    .ChartTitle.Font.Name = "Calibri"
    .ChartTitle.Font.Size = 14
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Intervals"
    .Axes(xlCategory, xlPrimary).AxisTitle.Font.Name = "Calibri"
    .Axes(xlCategory, xlPrimary).AxisTitle.Font.Size = 9
    '.Axes(xlValue, xlPrimary).HasTitle = True
    '.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Number of Objects"
    '.Axes(xlValue, xlPrimary).AxisTitle.Font.Name = "Calibri"
    '.Axes(xlValue, xlPrimary).AxisTitle.Font.Size = 11
    .ChartGroups(1).GapWidth = 4
   .SeriesCollection(1).ApplyDataLabels
    .SeriesCollection(1).DataLabels.Font.Size = 8
End With
Application.ScreenUpdating = True
End Sub

It is difficult for me to test, because you are using newer functions (NORM.INV()), for example) that are not available to my older version of Excel.

I have kept the chart construct and am trying to make the change by setting the new source data after it is created??/

The standard non-VBA solution to this kind of question is to define dynamic named ranges in the spreadsheet, then have the chart use those dynamic named ranges as the different series definitions. You seem to prefer to do things in VBA, where I tend to prefer spreadsheet solutions, but is there some reason you are avoiding defining dynamic named ranges for the charts? I guess the advantage I see to this approach would be that your VBA code does not need to define the chart source data after the fact. It just needs to enter the data into the spreadsheet, then the spreadsheet will figure out what the chart's source data ranges should be.

Not to worry... I think I have perfected the Gist of the initial requirement...10 more to go...
Thank you for your ongoing interest and feedback

For those who have been following...hereby an updated trial version...