Radar (or Spider) chart in Excel

Excel charts are not for numbers only...
Just an example.

 

Bubbles in Excel

 

Font color change within cell formula

Formulas in Excel usually don't care about the font color in displaying their results in a cell. However, you may need sometimes to distinguish between some results of your calculations by using specific font colors.

It is possible to assign a specific font color to a cell value, without any conditional formatting.

E.g., let's say, you want to compare two numbers (located in cells A1 and B1) and assign font color to the result of the formula like this one: =IF(A1>B1,22,33).  Depending on the outcome of comparison, if A1>B1 then you want to display 22 in green color; otherwise you want to display 33 in red.

To do that with a formula, you need to amend the formula with this kind of UDF (user defined function): 

 Function CFcolor(num1 As Double, num2 As Double) As Boolean
    If num1 <= num2 Then
        Application.Caller.Font.ColorIndex = 3       'red
    Else
        Application.Caller.Font.ColorIndex = 10     'green
    End If
End Function 

so that your formula looks like this:  =IF(A1>B1,22,33)+CFcolor(A1,B1) .

Obviously, to make use of the function, you have to copy and paste it first into a Module of your VBAProject (your workbook) or of your Personal workbook.

The function can be quite easily modified for use with other Excel formulas and colors.

 

PhotoShow: Looping through images in a folder

There are occasions when you'd like to demonstrate your photos or other images on your computer screen, in a continuous display, each one for predetermined number of seconds, or whatever time span you choose.

This can be quite easily accomplished (automated) with the following VBA macro:

Sub ImageShow()
'Displays images located in a folder on your computer for predetermined times
Dim myFolder As String, myFile As String
Dim imgPath As String
ActiveWindow.DisplayHeadings = False
Application.DisplayScrollBars = False
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayWorkbookTabs = False
Application.DisplayFullScreen = True
Application.DisplayStatusBar = False
Range("A1").Select
'Select your folder (with photos/images only)
MsgBox "Please select your folder with photos/images"
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Show
    myFolder = .SelectedItems(1)
End With
myFile = Dir(myFolder & "\", vbReadOnly)
Do
    imgPath = myFolder & "\" & myFile
    'Open consecutively each image in the selected folder
    ActiveSheet.Shapes.AddPicture Filename:=imgPath, LinkToFile:=msoFalse, _
        SaveWithDocument:=msoTrue, Left:=ActiveCell.Left, Top:=ActiveCell.Top, Width:=-1, Height:=-1
    ActiveSheet.Pictures.Select     'Resize the image to fit screen size
        With Selection
            .ShapeRange.LockAspectRatio = msoTrue
            .ShapeRange.Height = 725    'Change the height to fit your screen size
        End With
    Application.Wait (Now + TimeValue("00:00:05"))
    Range("AA5").Value = myFile        'Change the range if needed for visibility
    DoEvents
    Selection.Delete    'Delete displayed image
    myFile = Dir
    If myFile = "" Then Application.Wait (Now + TimeValue("00:00:05")): Exit Do
Loop While myFile <> ""
Application.DisplayStatusBar = True
ActiveWindow.DisplayHeadings = True
Application.DisplayScrollBars = True
ActiveWindow.DisplayGridlines = True
ActiveWindow.DisplayWorkbookTabs = True
Application.DisplayFullScreen = False
Range("AA5").Value = ""     'Change the range to match the location set above
End Sub

How to create a 'slide show' using a single formula and a chart

If you want to make use of some math formulas for practical applications or just to show off your Excel creativity, you can utilize kind of slide show: = refreshable charts based on your brilliant formula.

Follow these steps:

• Open a new workbook. Enter any two numbers into your worksheet (Sheet1), e.g. 1 in cell A2 and 2 in B2.
• Start with creating a simple XY scatter chart based on your entries. This chart is named as "Chart 1".
• Delete the two numbers you've just entered. You don't need them.
• Expand the width of column A to e.g. 200, move there the chart and resize it to the size of your choice.
• In cell D1, type the formula you want to chart, in the format using explicitly normal math signs and functions, e.g. x^2+5*sqrt(x)-3 .
• Enter the low and high limits (of your choice) for the Left value and the Right value of x axis, then also the Low and High limits for the number of Points you want to be plotted, as shown in the example below (cells D3:E5).
• In cells F3:F5, enter RANDBETWEEN formulas as shown in the example, cells F3:F5.

How to make square cells for grid paper in millimeters or centimeters

No macro needed here. Just follow these steps to fill your worksheet with square cells: 

• select the whole worksheet, or any part of it (your choice)
• in the ribbon, select View > Page Layout, then
  
• select Home > Format >Row Height, and enter e.g.:

• 0.03937 for 1mm grid (= 1 / 25.4), or
• 0.19685 for 5mm grid (= 5 / 25.4), or
• 0.3937 for 1cm grid (= 1 / 2.54), and so on (your choice)
  

• select Home > Format > Column Width, and enter the same value as for row height
• return to the Normal View.

Your grid is displayed and ready for use on your computer screen.

Here are examples of created this way 1mm and 1cm grids:

If you want to get printout of your grid on paper, the next steps depend on the size of paper, selected margins, printer settings etc. Here I'm providing just one example. Let's say that you use letter-size paper and set 2.5 cm custom margins on each side. You would proceed as follows:

• enter anything in one of the cells on the worksheet; otherwise you may get error message like this one: "We didn't find anything to print" 
• in Normal view, select one full page in the worksheet (as shown by page breaks)
• go to File > Print > Page Setup > Sheet > Print, and select Gridlines option 
• in Print Settings, select Print Selection, then set Custom Scaling Options to: Adjust to 335% of normal size.

Some tweaking may be necessary, depending on printers and Excel version, to fit the page size. I had no problem with printing the page filled with exactly 1mm size grid.

Other grids can be printed in a similar way, just with different % adjustments of normal size (e.g, I've used 262%  adjustment for 1cm grid).

 

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,