VideoPhoto

20 January, 2022

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.

 

 

17 January, 2022

Calculating an AVERAGE between any two dates

You've created a table which contains a column with dates. Now you need to do some calculations on numeric data included between some two selected dates. E.g., you want to calculate average of some 'Output' numbers falling within one month, December 2021, like in this table:


If your table is sorted by the 'Date' column, the simplest way to get your average 'Output' is as follows:

15 January, 2022

How to Generate Random Strings of Characters

To generate any random string of characters, including letters, digits and symbols, enter all the characters you want to use, into any cell of your worksheet. E.g., enter this sequence in cell A1:

abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789

and then use the following formula in any other cells to get your random strings:

=MID($A$1,RANDBETWEEN(1,62),1)&MID($A$1,RANDBETWEEN(1,62),1)&MID($A$1,RANDBETWEEN(1,62),1)&MID($A$1,RANDBETWEEN(1,62),1)&MID($A$1,RANDBETWEEN(1,62),1)&MID($A$1,RANDBETWEEN(1,62),1)&MID($A$1,RANDBETWEEN(1,62),1)&MID($A$1,RANDBETWEEN(1,62),1)&MID($A$1,RANDBETWEEN(1,62),1)&MID($A$1,RANDBETWEEN(1,62),1)

This specific formula produces 10-character string, but you can change the number of segments ( MID($A$1,RANDBETWEEN(1,62),1) ) in it, to generate string of any length, from 1 to 100 characters, and even more.

The above sequence is 62 characters long. If you want to use a different one then the number 62 in the formula must be replaced to reflect the length of your new sequence.

 

07 January, 2022

How to REPLACE any substrings or COUNT them in a string

Let's say we are dealing with the following string entered in cell A1:

"Examples of replacing or counting any string elements in a cell"

To replace e.g. the 3rd letter 'a' with 'A' in the string, we would use this formula:

=SUBSTITUTE($A$1,"a","A",3)

To count e.g. the number of SPACES in the string, the following formula can be used:

=LEN($A$1) -LEN(SUBSTITUTE($A$1," ",""))        (result = 10)

To count the number of substrings, such as e.g. "e", "in", "le" or "count" in the string, the following formulas can be used:

  • =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"e","")))/LEN("e")    (result = 6)
  • =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"in","")))/LEN("in")    (result = 4)
  • =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"le","")))/LEN("le")    (result = 2)
  • =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"count","")))/LEN("count")     (result = 1)

 

04 January, 2022

Template for Basic Data Summary/Analysis

If you have collected and organized some data in table(s) or list(s), the first thing you might need could be to get quick general analysis/evaluation of that data - before going any further with some math, statistical or graphic analysis.

The following example provides easy solution, kind of a template, for that preliminary summary and basic analysis.

Here's just illustrative table of random data. Let's say you want to know "everything" about this set of values.

02 January, 2022

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

 

25 December, 2021

COLORS in Cells: How to Get, Set and Use them

It's really easy to color Excel cells, their background, content or borders. We can do it either directly (using Format Cells... or some Font options) or indirectly - using Conditional Formatting in a variety of available ways in the Excel menu.

When it comes to determining (getting) exact codes/names of colors being already used in a worksheet, it can be a bit complicated. And it may happen that you need to know what colors were originally used for background filling of some cells. Well, we can't find it out by using any Excel function or formula. So, practically, we must use macros (VBA codes) to solve such puzzles. E.g., the following code will determine Color Index for background color of cell A2.

Press ALT+F11 keys to display the VBA editing window and enter there this code:

Sub getCellBG()
Dim getC as Integer
getC=Range("A2").Interior.ColorIndex
End Sub