VideoPhoto

Showing posts with label arrays. Show all posts
Showing posts with label arrays. Show all posts

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
Range("A10").Select
'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:

10 July, 2021

Complex Array formula

An array is a row or column of values, or a combination of them. Array formulas can be very useful in many applications where normal Excel formulas don't work. An array formula is a formula that can perform multiple calculations on one or more items in an array.

Array formulas can return either multiple results, or a single result. We can use array formulas to perform complex tasks, such as:

  • create quickly sample datasets
  • count some values contained in a range of cells
  • sum numbers that meet certain conditions, such as the lowest values in a range
  • sum every Nth value in a range of cells
  • find specific values in a cell or a range of cells
  • etc.   

The following example shows you how the single-cell array formula can find the last filled cell in a given row and show its value. Here is the formula (all entered in a single cell):