Pie chart: wheel of VBA colors

If you use Excel VBA and need to set color or get color for cell, shape or chart, you may need the VBA color code list for reference purposes. The ColorIndex offers 56 basic colors and it's hard to remember VB codes for all of them. The following chart can be helpful, if you don't know the color code for your specific task:

In case you'd need to recreate the chart on your own computer,

Outliers in normally distributed datasets

I've recently touched the subject of statistical analysis of data using Excel functions. In this post I dive deeper into Excel statistical tools. It's about outliers in data sets, about numbers that distort the state of reality and can lead to unsound findings and conclusions regarding specific areas of knowledge.

There are no strict statistical rules for indisputable ways of identifying outliers; we are dealing with probabilities. Nonetheless, there are guidelines and tests we can utilize to find outlying values, and they can significantly improve our intuition, formally.

Because of the importance of detecting outliers I've prepared Excel workbook providing practical tools (tests) for identifying such deviating/departing values within any set of numerical data. The workbook includes basic guidelines for using some specific statistical tests; I'm showing here its fragment:

Simulation of the arithmetic mean and data normality

The world around us is full of uncertainties. In research, in engineering and sciences, uncertainties must be dealt with in a formalized way, using statistics.  We are required to follow procedures for dealing with inevitable variation in routine testing:  in laboratory, in production and construction, in relation to quality control and quality assurance issues.

Excel statistical functions help in solving many practical problems in that area, as well as in simulation and estimation of probabilities of certain outcomes. Here I just like to share with you couple of charts based on simulation I've carried out in Excel regarding the critical role of sampling frequency and the number of tested samples in evaluation of various processes and material properties.

The simulation example shown below is based on assumption of normal distribution of sampling and uses two basic statistical functions:

• RAND() , which returns evenly distributed random numbers from 0 to 1 (not including 1), and
• NORM.INV, which returns the inverse of the normal cumulative distribution for the specified arithmetic mean and standard deviation.

Here is the chart illustrating the effect of the number of tests (samples) on the value of Mean. The data have been obtained with the formula =NORM.INV(RAND(),2.60,0.009), where 2.60 is the expected arithmetic Mean and 0.009 is Standard Deviation of the population ('targets'). We can see that variability of the running Mean is very high up to about 15 tests. Its reliability increases with number of samples and reaches good stability starting at around 50 tests (samples). At the same time the spread of data widens up to about three standard deviations, as can be expected in any normal distribution.

Pick your lucky Lotto numbers

If you play Lotto, how do you pick your lucky numbers?

Everyone has their own method. Some people like to analyze past draws and study the statistics to select potential winning patterns. Others rely on totally random ways. My own experience shows that statistics helps a little bit with winnings at the low end only. That’s it.

No method can guarantee a big win. Lotteries are completely random. To check out my 'independent' serendipity - years ago - I decided to create my own Lotto number picker coded with VBA. It’s one of my modest and oldest Excel creations. Here's its face in a worksheet:

Simple and functional. It just picks six lucky numbers for you to play in a lottery like Lotto649 or something close to it. It can be modified quite easily for other lottery games based on random selections. You can get your very own quick picks on your own computer.

Printing multiple workbook ranges on one page

Sometimes we may need to print couple of areas located in separate ranges of our worksheet or on different worksheets in our workbook. The reason? This way we could simply save couple of paper sheets. Also, such combining of separate ranges on one page might be useful, or even necessary, for comparison / presentation purposes, etc.

So, how could we do that?

The easiest way, I'm using, is to employ the Excel Camera tool. The tool might not be available by default on your Quick Access Excel toolbar (top-left corner). If so, you can add it there by choosing Toolbar>Customize>More Commands...  Scroll through the list of Commands to find Camera and drag it to the toolbar. From now on it'll be always ready for you to use.

 
Here's how to use it, e.g. for printing:

Creating Surface Charts 3D

Surface charts in Excel deserve special attention. They allow to see your data in three dimensions and can be particularly useful in engineering applications (design and control of processes) as well as in the art of drawing and painting.

Typical surface chart shows how the dependent variable (z) behaves as a function of two independent variables (x & y). In other words, the chart illustrates outcome of any nonlinear function involving two variables. E.g. we can plot speed of some technological process (reaction) as a function of pressure and temperature.

In this post I'll show how you can prepare a template for creating surface charts based on equations including variety of math functions (algebraic, transcendental, periodic). Such template can be very useful in all kinds of design, as it allows you to visualize plenty of solutions in minutes.

Let's start with just one example of surface chart I've created. This is 3D plot of equation shown in the title of this chart:

 

How can you create similar charts based on your own equations?

Presentations in Excel

If most of your work, you do for presentations, comes from Excel, there is no reason to use the PowerPoint instead of Excel itself for preparing your demonstration.

In fact, all your charts, tables, forms, textual info, background graphics, and even sounds, can be quite easily presented and put in order in Excel worksheets.  All the hassle with extra work of copying and pasting into PowerPoint could not be necessary.

Let's say, you've carried out your data analysis and prepared workbook with 30 perfect  worksheets ('slides') for your quarterly presentation. How would you proceed to get ready for reporting your work? Switching to PowerPoint? Not necessarily.

If you'd decide to stay with Excel, the following steps could probably be more efficient way to go. 

First, put all your presentable worksheets in consecutive order in which they'll be presented.

Add a blank worksheet dedicated to a title page. Excel has all the tools needed to create an attractive design. Such a worksheet could show just the subject of your presentation or might be a bit more elaborated, as e.g. in this figure:

Similarly, you can add another worksheet for displaying "The end" page, if appropriate.

Counting unique cell entries in a worksheet

Sometimes you may want to be sure that your large Excel dataset contains unique entries only, i. e. none of the entries appears more than once. How would you check it?

There are some array formulas and advanced filtering methods available for such occasion, but the most convenient way is probably using UDF (User defined function) I'm presenting here:

Function Count1time(cRng As Range) As Variant
'Counts each cell with a value in the selected range
'Excludes any repeats!
'example of usage: Count1time(A1:C100)
Dim cVal As Variant
Dim uniq As New Collection
Application.Volatile
On Error Resume Next
For Each cVal In cRng
    uniq.Add cVal, CStr(cVal)
Next
Count1time = "Found " & uniq.Count & " unique entries"
End Function

To use it - in your workbook - open the Visual Basic Editor (VBE) by pressing ALT+F11 shortcut, right-click within Project Explorer, select Insert > Module (if not inserted there yet). Copy and paste the function code in the Module window.

Switch back to your Excel worksheet and test the function on any selected set of data. If the function counting result is equal to the number of cell entries within the tested range, it confirms that all data in your set are de facto unique. If not - some values are entered more than once.

 

How to insert and store all of your images in Excel file(s)

This is probably one of the best, most efficient ways to organize all of your images in Excel workbooks. Easy to insert, easy to find and easy to access/display them at any size, any scale. You can arrange them in any way you want to. You can store thousands of them in just a single worksheet, available at click of a button. No, you don't need even to click. They show up when you are hovering over your cell of choice with your mouse. This is about keeping your images (photos and all kinds of graphics) hidden in cell Comments.

Personally, I keep almost all of my images in .jpg and .png formats, but this is not the limitation here. Other formats can be stored this way as well.

Here is an example how to start with inserting your images and how your worksheet could initially look like:

To start, open a new workbook. First of all you'll need to add some VBA code to your workbook, so press ALT+F11 on your keyboard and add in a module. Just right-click within VBA Project Explorer and select Insert, and then choose Module from the menu.

World of Fractals - Beauty of Recursion

In my computer programming adventures I tried many things.  Among them I've explored  iteration and recursion.

Iteration is simpler, because it's basically just a For loop used in all common languages. It handles a number of steps consecutively.  You go up or down, step by step, until you reach the top or the bottom.

Recursion is much more convoluted.  It's a way of thinking and solving problems, because there is more then iteration to it.  Steps are also repeated here, but you reduce the problem to a smaller tasks and handle them separately;  sort of divide-and-conquer strategy involving inheritance.  You define the value of your function, which has more than one variable, by using other values of the same function, i.e. you change only one variable and keep all others constant, until some endpoint is reached, and then change something else, reach another endpoint, and so forth.

Recursive algorithms take many forms.  It's the beauty of math, really, which shows up in many of those algorithms.  One example of recursion is fractals.  The whole art of fractals evolved in recent years.  Have a look at its beauty e.g. at this website.

I've just touched the recursion issue many years ago.  Sometimes it took several hours of my computer time to produce a complex picture, but it was quite satisfying to see the algorithm at work.

Here are some examples of what I was able to create in early 1990s.

Hiding/Unhiding sheets in a workbook

The number of sheets in a workbook is limited by your available computer memory only. So, in some cases you may have to work on projects that include a very high number of sheets, e.g. for some weekly routine testing and analysis of data. In such cases you don't need to see and access most of the sheets. You'd rather like to remove them from the scroll bar to make selections easier and faster. This is normally not necessary if you're dealing with just several sheets. You can simply right-click on any of the tabs and Hide them. But what if you have e.g. 100 or more sheets in your workbook? It would be better to automate the task of hiding and unhiding most of them.

I'll show you here such a better way to do that.

Here's an example how the scroll bar with tabs would look like before showing all the sheets/tabs and after hiding unnecessary tabs:

Bar with unhidden tabs

 Bar with hidden multiple tabs

 

The following steps

Keep record of Maximum (or other statistics) achieved value in a cell

If you'd like to keep automatically the record of some statistics (max, mean, average, standard deviation, etc.) related to your set of data that are subject to change periodically over time, then a simple macro can help. 

Here is an example VBA code that you can copy and paste in your VBE window appearing for your worksheet after using the ALT+F11 shortcut. This code sample, you see below, will record just the maximum value of the SUM of numbers entered within A1:A10 range. It can be easily modified for any other special purpose.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myVar As Double
myVar = Range("A11").Value 'Sum of numbers in a range
Range("Z100").Value = myVar    'Replace Z100 with any other cell, if needed
If myVar > Range("A12").Value Then Range("A12").Value = Range("Z100").Value
End Sub

As shown in the fragment of my worksheet, the data are entered in A1:A10 range. Cell A11 contains formula =SUM(A1:A10). The result, updated each time you change something in the worksheet, is displayed in cell A12. The helper cell location (Z100 here) can be changed in the code at your will.

 

 

Set worksheet CELLS to SQUARE shape

There are situations, especially in graphic design area, when it's convenient to work with worksheet cells that are square in shape. You can get such effect in many ways, including a macro. However, the best method is probably the one I'm suggesting here. Just follow these easy steps:

1. Start with the main Menu strip; click on View and select PageLayout. Select a range or the whole worksheet, whichever you want to format.
   
2. While in the page layout, click on Home in the main Menu and select Format in Cells group.
3. Click on Row Height and enter the height value you want, e.g. 5 mm (if mm is your default or preferred display option). The unit can be changed to cm or inch, if necessary, by clicking on File>Options (at the bottom of vertical strip)>Advanced>Display and selecting one of the Ruler units.
4. Go back to Format and click on Column Width; enter the same value as you entered for the row height.

That's it. You've got square cells. Return to the Normal workbook view.

Your worksheet display format could now look like this one:

How to find something with FIND function

While working in Excel we're always looking for something. The FIND function can be very helpful in our searches. 

Remember two things: it is case-sensitive (differentiates between "a" and "A") and doesn't work with date format (cells formatted as dates)

Used by itself, it just finds position of a character or text within a longer string. However, when used in combination with some other functions, it can be very useful in solving complex tasks. Here is summary of some examples of Find usage, described beneath the table.

Example 1: Find the directory/folder name of the file, you are using right now, with this formula:

=IF(CELL("Filename",A1)>"",LEFT(CELL("Filename",A1),FIND("[",CELL("Filename",A1))-1),"")

Example 2: Extract some number of characters (e.g. 4) following the second occurrence of "/":    =MID(A1,FIND("/",A1,FIND("/",A1,1)+1)+1,4)

Example 3: Return all characters between 2nd and 3rd "%":

=MID(A1,FIND("%",A1,FIND("%",A1)+1)+1,FIND("%",A1,FIND("%",A1,FIND("%",A1)+1)+2)-FIND("%",A1,FIND("%",A1,+1)-1)

Example 4: Find position of any of (2nd,3rd,4th...) occurrences of a given character in a string (e.g. 3rd occurrence of "e"):

=FIND(CHAR(10),SUBSTITUTE(A1,"e",CHAR(10),3))

Example 5: Find some substring of the string after a given character (e.g. after ">"):

=RIGHT(A1,LEN(A1)-FIND(">",A1))

Example 6: Find position of the second occurrence of some character in a string (e.g. "S"):

=FIND( "S",A1,FIND("S",A1,1)+1)

Example 7: Find text between two characters (can be the same or different); in this example, between "$" and "£": 

=MID(A1,FIND("$",A1)+1,FIND("£",A1)-FIND("$",A1)-1)