With Application.FileDialog(msoFileDialogOpen) '(this one), we show a file open dialogue. You'll se how later, when we get to the procedure 'RandomNumbers'.Īt module level you need to declare 2 public variables: Public bAbort as Boolean and Public bBell as Boolean The trick in doing this (getting the same standard deviation etc.) is to use the worksheet function 'NormInv' together with VBA's random numbers generator, 'Rnd'. To make the bell shaped curve we use 2000 'random' numbers with the same standard deviation and mean value as the data for the histogram. In the first histogram the chart's number of columns (intervals) is fixed to 8 and the interval size is the data's standard deviation. The VBA code on this page assumes that the source data for the histogram is in another workbook, and the histogram will be put on a new sheet in that workbook. It also contains a simple userform, which is only referred to on this page. You can copy the code and paste it into a VBA module, but it is easier to download the zipped workbook with the code. The macro calculates interval size, counts frequencies and makes the histogram chart. The second example shows how to make a histogram with a user defined number of columns. In the first example I show how to make a histogram with a bell shaped curve like in Microsoft's example (like the image at the top), and you don't need Analysis Toolpak. You can make histograms manually, if you have installed Analysis Toolpak, but it is tedious work (see a guide here) - it is much, much faster to use macros. To be sure about the normal distribution-thing you need to test statistically, but I'll leave that to you - this is about VBA! It could be the population's age distribution or quality data, and from the shape you get a pretty good idea if it is a normal distribution or not.

I also show how to make a bell shaped curve with normal distribution and the same standard deviation and mean value as the histogram.Ī histogram is a graphical representation of data distribution. On this page I show how to make a histogram fast and easy in Excel with VBA macro code.

Excel histogram chart