26 May, 2023

Creating Shapes, Graphics, Illustrations - Part 2

Here is yet another way of creating graphics in Excel - using VBA macro, like this one:

Sub DrawDefShape()
'Draws any defined shape representing Bézier curves
Dim x As Integer, y As Integer
Dim pt(1 To 7, 1 To 2) As Single
Dim rng As Range
Set rng = Sheets("Data").Range("A2:B8")
'Fill array of control point coordinates with values
For x = 1 To 7
    For y = 1 To 2
        pt(x, y) = rng.Cells(x, y).Value
    Next y
Next x
Set myDoc = Worksheets("Data")
myDoc.Shapes.AddCurve SafeArrayOfPoints:=pt
'Display the whole 2D array in MsgBox
For i = 1 To UBound(pt)
dPts = dPts & pt(i, 1) & "   " & pt(i, 2) & vbNewLine
Next i
MsgBox dPts
End Sub

Before going into the details of the macro structure and its use, let's look at some examples of curves and shapes you can easily create with it. Here they are:

24 May, 2023

Creating Shapes, Graphics, Illustrations - Part 1

Although Excel is not designed as a comprehensive graphic design tool, we can create in Excel all kinds of shapes/illustrations, actually without limits. There are many ways to do that, and the main methods are:

  • Obviously - charts: while primarily used for data visualization, the charting capabilities of Excel can be utilized to create simple illustrations. By manipulating the data  (applying various conditional formatting rules) and using other formatting options, you can create visually appealing illustrations within the charting framework.
  • Inserting predefined shapes: Excel provides a variety of predefined shapes that you can insert from the "Insert" tab on the ribbon. These shapes include rectangles, circles, arrows, lines, and more. They can be customized in terms of size, color, rotation, and other formatting options.
  • Using drawing tools (to draw shapes): Excel allows you to draw custom shapes using the "Shapes" tool. You can choose from different shapes, such as polygons, curves, and freeform shapes. By selecting the "Draw" option, you can manually create the desired shape by clicking and dragging on the worksheet.
  • Using SmartArt: this feature feature allows you to create diagrams, flowcharts, and other visual representations. SmartArt provides a variety of predefined layouts and styles, making it easy to create professionally-looking illustrations. You can access SmartArt through the "Insert" tab as well.
  • Combining shapes: Excel also enables you to combine multiple shapes to create new ones. This can be done by selecting the desired shapes, right-clicking, and choosing the "Group" or "Combine Shapes" option.
  • Editing shapes: Once a shape is created, you can further customize it by resizing, rotating, or changing its fill color, outline color, and other formatting options. This can be done by selecting the shape and using the available formatting tools.
  • In addition, the Lasso Select tool is now available in Excel. This technique allows you to select non-contiguous cells or objects in Excel.

The following examples give some idea of how you can use these methods to design various graphic solutions and what you can create using Excel tools.


20 May, 2023

Excel Tips and Solutions for Every User

With over 110 posts published so far in my blog I've decided to summarize them in carefully edited eBook format (Kindle edition). You can have a look at it here .

It covers many less known Excel features and topics, from workbook design principles to quite sophisticated VBA programming tasks and some creative art.

If you are serious about your proficiency in using Excel program, this eBook may be worth to get it and keep it handy.

Here's the book cover:

 348 pages of creative ideas and solutions.


15 May, 2023

Creative Charts - Excel Art

These are some of my Excel creations: charts based on the equation of circle. Unlimited Excel...


09 May, 2023

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:
'Display GCD
MsgBox "The GCD of " & c & " and " & d & " is = " & a
End Sub