Charts Based On Parametric Equations: Sinusoidal (Oscilloscope-type) Curves

Curves that can be used in physics for analysing pairs of simple harmonic motions are generally known as Lissajous Curves. They describe the superposition of two oscillations taking place at the right angle to each other,  at different frequencies. Their main application can be found in oscilloscopes. These instruments allow observation of two superimposed sine waves coming from varying signal voltages of sinusoidal nature. Depending on the frequency and amplitude of each wave, and the phase between them, we can see interesting patterns emerging.

Because the superimposed two perpendicular oscillations can be described with parametric equations for x and y in the Cartesian coordinate system, we can quite easily demonstrate the resulting shapes graphically using Excel charts. Let's look first at some of "oscilloscopic" patterns generated with Excel.

Shapes Based On Equations: Cardioids

Cardioid curve is formed by tracing a point selected on the circumference of a circle, rolling onto another circle of the same radius. Here are two examples of cardioid graphics, one is just a single cardioid curve and the other is presentation of 4 cardioids positioned within the same coordinates.

These kind of graphics can be generated using the VBA macro listed below the two charts.

Shapes Based On Parametric Equations: Epitrochoids

Epitrochoids are curves generated by a point selected on a circle of smaller radius rolling around the outside of a fixed circle of larger radius, and that selected point can be chosen at some distance from the center of the smaller circle. These are then 3-parametric curves. They can be helpful in creating decorative designs and other art applications, and can serve also as pastime and recreation for children. Since 1965 there is available a toy (geometric drawing device) known as spirograph, combining mathematics and art. It allows producing physically numerous curve designs based exactly on epitrochoid and hypotrochoid parametric equations.

However, nowadays the epitrochoids (as well as hypotrochoids - see another post at https://draft.blogger.com/blog/post/edit/3323809043368251287/9189797010242374274?hl=en-GB) can be generated quite easily in Excel by using VBA macro like the one provided at the end of this post.

Here are some examples of charts showing epitrochoid curves generated with the macro. Some of them present combined double curves.

Shapes Based On Parametric Equations: Hypotrochoids

Hypotrochoids are curves generated by a point selected on a circle of smaller radius rolling around the inside of a fixed circle of larger radius, and that selected point can be chosen at some distance from the center of the smaller circle. These are then 3-parametric curves.

You don't need any special programs, like e.g. Mathematica, to create hypotrochoids. They can be generated in Excel quite easily by using VBA macro like the one provided at the end of this post.

Here are some examples of charts with generated hypocycloid curves, both single and combined double curves:

Shapes Based On Parametric Equations: Hypocycloids

Hypocycloid is a parametric curve traced by a fixed point on a circle that rolls on the interior of another larger circle. 

The curve - due to its parametric functionality - finds many applications, both theoretical and practical. They include mechanical and construction engineering, e.g. design of gears, cams, valves, robotics (motion planning), and machine/structures design in general. Contribute also to aesthetically pleasing art designs and animations.

The hypocycloid curves can be generated quite easily in Excel with VBA macro,  presented at the end of this post. Here are some examples of charts with generated hypocycloid curves, both single and combined double curves:

Prime Numbers: How to Find the Highest Prime Number Below Given Limit

If you need to find/generate the highest prime number not exceeding some given limit, like e.g. 100,000, using the following Excel VBA macro is the appropriate and easy to use tool; obviously, within the Excel program specifications.

Just copy and paste the code into one of the modules of your workbook. Some explanatory notes are included within the macro.

Sub CalcTopPrimeNumberBelowGivenLimit()
'Max prime <1,000,000: 999,983
'Max prime <10,000,000: 9,999,991
'Max prime <100,000,000: 99,999,989
'Max prime <1,000,000,000: 999,999,937
'For max. integer handled by my computer (=1,068,699,999) the highest prime is: 1,068,699,979
    Dim n As Long
    Dim primes() As Boolean
    Dim i As Long, j As Long
    Dim tPrime As Long
    'Set the maximum limit for prime number generation
    n = InputBox("Enter your max. limit for the prime generation:", "Set the max. limit", 199999999)
    'Initialize the array to assume all numbers are prime
    ReDim primes(2 To n)
    For i = 2 To n
        primes(i) = True
    Next i
    'Apply the Sieve of Eratosthenes algorithm (it finds all primes up to a given limit)
    For i = 2 To Sqr(n)
        If primes(i) Then
            For j = i ^ 2 To n Step i
                primes(j) = False
            Next j
        End If
    Next i
    'Find the highest prime number
    For i = n To 2 Step -1
        If primes(i) Then
            tPrime = i
            Exit For
        End If
    Next i
    'Display the result
    MsgBox "The highest prime number below " & n & " is " & tPrime
End Sub 

 

How to Find the Greatest Common Divisor of any Positive Integers

The Greatest Common Divisor (GCD) is the largest integer that divides some two or more numbers (up to 255 positive integers) without a remainder.

In Excel, the fastest and most convenient way to find the GCD of any number of integers (up to 255!) is by using its GCD function. E.g., to find this divisor for any three integers you would use formula like this one:

= GCD(60,148,280)

which - for these specific values - results in number 4.

 

To find the GCD for just two integers you could also use this kind of Excel macro:

 

Sub FindGCD()

'Calculate Greatest Common Divisor (GCD) of two integers

Dim a As Integer, b As Integer, temp As Integer

Dim c As Integer, d As Integer

'Input the two integers

a = InputBox("Enter the first integer:"): c = a

b = InputBox("Enter the second integer:"): d = b

'Perform Euclidean division algorithm

Do While b <> 0

    temp = b

    b = a Mod b

    a = temp:

Loop

'Display GCD

MsgBox "The GCD of " & c & " and " & d & " is = " & a

End Sub

 

Using Excel as Music Player

Do you like to listen in the background to your favourite music while working outside Excel, e.g. in Word, or on your emails? If so, you can use Excel as a music player. To prepare for that you need to create a music library, collection of your music files, located e.g. in MyMusic subdirectory, as in my example.

Having done that, next create in your Excel worksheet a list of Hyperlinks to those files (by using CTRL+K shortcut for each of your music files). The list can be arranged in any order and contain as many hyperlinks as needed. Here is a short example of my list:

Animation - Flying Objects

The following VBA (Visual Basic Application) code makes Excel shapes and some other graphics flying. In this instance I'm shooting a rocket on my worksheet across the computer screen. This shape is based on Excel graphic named "Graphic 4".

Option Explicit
Public Declare Sub Sleep Lib "kernel32" (ByVal iMilliseconds As Long)

Sub Flyer()
    Sheet1.Shapes("Graphic 4").Left = 1000   'Starting X
    Sheet1.Shapes("Graphic 4").Top = 450   'Starting Y
    MoveShp Sheet1.Shapes("Graphic 4"), 0!, 0!, #12:00:01 AM#   'Shape inserted
End Sub
Sub MoveShp(shp As Shape, ByVal coLeft As Single, ByVal coTop As Single, t As Date)
' Moves the shape from start to finish over the interval t
    Const xch = 0.018
    Const n1 As Long = 30       'Accelerate/decelerate steps
    Const n2 As Long = 60   'Coast steps
    Const n As Long = 2 * n1 + n2    'Total steps
    Dim i As Long   'Step index
    Dim stpv As Single   'Coasting, pixels/step
    Dim v As Single     'Velocity at current step
    Dim cLiLeft As Single, cLiTop As Single   'Left and Top num
    Dim cMi As Single      'Frctn denom
    Dim coLeftPr As Single, coTopPr As Single  'Previous Left and Top position
    stpv = 1 / (n - n1)
    With shp
        coLeft = coLeft - .Left: coTop = coTop - .Top
        coLeftPr = .Left: coTopPr = .Top
        For i = 1 To n
            Select Case i
            Case 1 To n1    'Accelerate
                v = stpv * (1 + Cos(xch * 180 * (1 + i / n1))) / 2
            Case n1 + 1 To n - n1   'Constant velocity
                v = stpv
            Case Else       'Decelerate
                v = stpv * (1 + Cos(xch * 180 * (1 + (n - i) / n1))) / 2
            End Select
            .Left = .Left + v * (coLeft - cLiLeft) / (1 - cMi)
            .Top = .Top + v * (coTop - cLiTop) / (1 - cMi)
            cMi = cMi + v
            cLiLeft = .Left - coLeftPr: cLiTop = .Top - coTopPr
            DoEvents
            Sleep t * 86400000# / n
        Next i
    End With
End Sub

To use the code in Excel on your computer follow these steps:

• find and insert Graphic 4 shape into your worksheet (if you want to use any other Excel graphic, remember to change the shape name in the above code)
• copy the code and paste it into any module of your project (workbook)
• select the "Flyer" macro from Macros in the ribbon to run it.

If you assign the macro to the shape (by right-clicking it and selecting "Assign Macro..." option), then just click on the shape and it will fly...

Try to run it using some other shapes/objects.

 

 

How to design colorful cell contents in Excel

If you need to design a colorful title or banner or something similar, so that every character or group of characters or digits has a distinct color, then you can find this post helpful. Here are just two simple examples of effects you can achieve:

You can easily produce such effects quickly by entering your text (including also digits) in any cell of your worksheet, selecting the cell, and running the macro listed below. Just remember that if your cell contains just a number it must be formatted as text for this purpose.

The macro can be entered/copied into any VBA module of your workbook. Obviously, it can be modified as needed for your specific needs. Enjoy!

Sub clrFonts()
'Colors every character within a string of selected cell
Dim cnt As String
Dim rng As Range
Dim n As Long
Set rng = ActiveCell
For n = 1 To Len(rng.Value)
    cnt = Mid(rng.Value, n, 1)
    If cnt = "a" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 255): GoTo cont 'blue
    If cnt = "b" Then rng.Characters(n, 1).Font.Color = RGB(0, 255, 0): GoTo cont 'green
    If cnt = "c" Then rng.Characters(n, 1).Font.Color = RGB(255, 0, 0): GoTo cont 'red
    If cnt = "d" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 255): GoTo cont 'blue
    If cnt = "e" Then rng.Characters(n, 1).Font.Color = RGB(153, 51, 0): GoTo cont 'brownish
    If cnt = "f" Then rng.Characters(n, 1).Font.Color = RGB(255, 0, 255): GoTo cont 'd red
    If cnt = "g" Then rng.Characters(n, 1).Font.Color = RGB(0, 255, 255): GoTo cont 'green blue
    If cnt = "h" Then rng.Characters(n, 1).Font.Color = RGB(128, 0, 0): GoTo cont 'brown
    If cnt = "i" Then rng.Characters(n, 1).Font.Color = RGB(0, 128, 0): GoTo cont 'vd green
    If cnt = "j" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 128): GoTo cont 'd blue
    If cnt = "k" Then rng.Characters(n, 1).Font.Color = RGB(128, 128, 0): GoTo cont 'd grey
    If cnt = "l" Then rng.Characters(n, 1).Font.Color = RGB(128, 0, 128): GoTo cont 'vd brown
    If cnt = "m" Then rng.Characters(n, 1).Font.Color = RGB(192, 192, 192): GoTo cont 'l grey
    If cnt = "n" Then rng.Characters(n, 1).Font.Color = RGB(128, 128, 128): GoTo cont 'l green
    If cnt = "o" Then rng.Characters(n, 1).Font.Color = RGB(153, 153, 255): GoTo cont 'l blue
    If cnt = "p" Then rng.Characters(n, 1).Font.Color = RGB(153, 51, 102): GoTo cont 'vvd brown
    If cnt = "q" Then rng.Characters(n, 1).Font.Color = RGB(255, 255, 204): GoTo cont 'vl yellow
    If cnt = "r" Then rng.Characters(n, 1).Font.Color = RGB(51, 153, 102): GoTo cont 'green blue
    If cnt = "s" Then rng.Characters(n, 1).Font.Color = RGB(102, 0, 102): GoTo cont 'vvvd brown
    If cnt = "t" Then rng.Characters(n, 1).Font.Color = RGB(255, 128, 128): GoTo cont 'd orange
    If cnt = "u" Then rng.Characters(n, 1).Font.Color = RGB(0, 102, 204): GoTo cont 'vdd green
    If cnt = "v" Then rng.Characters(n, 1).Font.Color = RGB(204, 204, 255): GoTo cont 'dd grey
    If cnt = "w" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 128): GoTo cont 'vvd blue
    If cnt = "x" Then rng.Characters(n, 1).Font.Color = RGB(204, 153, 255): GoTo cont 'violet
    If cnt = "y" Then rng.Characters(n, 1).Font.Color = RGB(51, 102, 255): GoTo cont 'md blue
    If cnt = "z" Then rng.Characters(n, 1).Font.Color = RGB(102, 102, 153): GoTo cont 'greenish
    If cnt = "0" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 0): GoTo cont 'black
    If cnt = "1" Then rng.Characters(n, 1).Font.Color = RGB(0, 255, 0): GoTo cont 'green
    If cnt = "2" Then rng.Characters(n, 1).Font.Color = RGB(255, 0, 0): GoTo cont 'red
    If cnt = "3" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 255): GoTo cont 'blue
    If cnt = "4" Then rng.Characters(n, 1).Font.Color = RGB(0, 255, 255): GoTo cont 'l blue
    If cnt = "5" Then rng.Characters(n, 1).Font.Color = RGB(102, 0, 150): GoTo cont '???
    If cnt = "6" Then rng.Characters(n, 1).Font.Color = RGB(128, 128, 0): GoTo cont 'greenish
    If cnt = "7" Then rng.Characters(n, 1).Font.Color = RGB(128, 0, 128): GoTo cont 'd brown
    If cnt = "8" Then rng.Characters(n, 1).Font.Color = RGB(0, 128, 128): GoTo cont 'd green
    If cnt = "9" Then rng.Characters(n, 1).Font.Color = RGB(255, 153, 204): GoTo cont 'rouge
    If cnt Like "[A-Z]" Then
        rng.Characters(n, 1).Font.Color = RGB(255, 0, 255)  'dark red
    ElseIf cnt <> " " Then
        rng.Characters(n, 1).Font.Color = RGB(0, 0, 0)  'black
    End If
cont:
Next n
End Sub

 

Header/Footer: What can be entered there?

Excel offers the following built-in headers and footers. They can be added to your worksheet easily, with a single mouse click.

However, we're not limited to these options. In fact, you can enter any text/string into your worksheet cell and add it to any part of the header or footer with just one line of VBA code of a macro. E.g., this line will add contents of cell B2 to the left part of a header:

ActiveSheet.PageSetup.LeftHeader=Range("B2").Value

Such a line can be included in the VBA code I'm presenting at the bottom of this post.

Open Workbook with Startup Worksheet

If you create a workbook that will be opened and used frequently by yourself and - especially - if you design it for sharing with other users, you may want to start it in a compelling, attractive way.

I'm presenting here an example of initializing a worksheet which you might want to appear when the workbook is being started. The workbook is macro-driven, so it must be saved as Macro-Enabled Workbook. This is how the worksheet looks like after loading with a randomly selected photo (this is a screenshot of the left part of my computer screen):

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

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,

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.

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?

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.

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.