Tag Archives: array

Array Formulas in Excel – 3D Arrays

Array functions were one of my first forays into Excel. But I couldn’t do enough justice to them in the beginning, and hence felt the need for another post.

What led to this post? I was looking for a bit on Arrays, when I bumped into sites I hadn’t referred to before. And believe me, there is more to Arrays than even what I know now! But I thought some parts of it I must bring out. It all started when I was looking to get the list of unique items, and that is when I bumped into the formula I described in my prev. post. The Power Formula one. It was quite heavy in Arrays, so I decided to probe a little further into how it works, and then the ball rolled over from one topic to another, and finally landed here – 3D arrays in Excel!

Arrays in Excel Sheet space

If you want to enter an array, all you have to do is enter this: ={0,1,2,3,4,5}. And if you “Array-enter”(Ctrl+Shift+Enter) it, you find the 6 cells (in columns) populated but the array. So basically, this is it to enter an array.

Named Array Ranges : This is a little different from the usual named ranges we have in Excel. Usually, a name defines/refers to a specific cell contents. So it’s actually a reference to the cell location, and not a formula! If we need to name the array we entered above, and we do the usual naming on the top left corner cell, I think you’re going to be in for some surprise when you use the name.Named Array Range

Essentially, named array ranges are the same as “Array Variables”. So, when you go to the Name Manager, you need to specify the name as shown in the screenshot. This way, when you use the name, it substitutes the Array in its place.

This is such a handy option, and if you get the hang of Arrays, I think you’d be in love it it. If you’re used to matrices processing or stuff, like Matlab, I think you’ll find this good.

To know more on what all an Array in Excel can support, look up this link (http://www.emailoffice.com/excel/arrays-bobumlas.html) which gives every possible way of ‘how to use arrays’. The Calendar in there is too damned good a formula, that I trying to make a sweet calendar for a 25 year period with features like what Outlook offers, w/o the Mail options.

Setting out on this calendar stuff is when I hit upon the topic of 3D Arrays.

3D Arrays in Excel: In Excel sheet space, 3D array refers to cell ranges across multiple sheets, for eg. ({=Sheet1:3!A$1:A$3}) is considered a 3D array formula, since it involves 3 Dimension – Rows, Columns & Worksheets. The following link has some information on this (http://www.bettersolutions.com/excel/EED883/QN620422111.htm). But nowhere was I able to find a 3D array similar to the lines of using it in VBA i.e. X(1:2:3). In VBA, just a Dim X(2,2,2) as Variant makes it an array to hold a 3x3x3 matrix. I haven’t found enough documentation whether this is possible in Excel Sheet space.

Coming back the the power formula, it sure does suit its name, for there is so much “array munching n crunching” that happens in the formulas, it has definitely expanded my comfort zone as far as Arrays are concerned.


Static & Public Declaration – Scope of variables in VBA

The very common way to initialize a variable in VBA is to declare using the ‘Dim’ function. But the Dim function exists only within a procedure or function, and it gets reinitialized every time the procedure is run.

Can we declare a variable to retain its value from a previous run?

Static Declaration within a procedure limits the variable to that procedure – but helps retain values of variables from previous runs, until forcefully reinitialized by explicit declaration using ‘ReDim’ or value setting.

Sub MySub( )
    Static Cnt As Integer
    Dim Msg As String
    Cnt = Cnt + 1
    Msg = “You’ve run the procedure ” & Cnt & ” times”
    MsgBox Msg
End Sub

Is there a way to declare a variable outside of a procedure?

Public Declaration helps declaring variables outside of the procedure, thus making a variable available to other modules within a workbook. Additionally, declaring a variable Public serves the purpose of Static property of retaining value from previous runs!

Option Explicit
Public Cnt As Integer

Sub MySub()
    Dim Msg As String
    Cnt = Cnt + 1
    Msg = “You’ve run the procedure ” & Cnt & ” times”
    MsgBox Msg
End Sub

Can we declare Array variables as Static & Public?

Yes! It’s the same way to declare array variables as single variables.

Public Cnt(1 To 10) As Integer

Sub MySub()   
    Dim Msg, Text As String   
    Cnt(1) = Cnt(1) + 1
    Text = “1:=” & Cnt(1)
    For i = 2 To 10
        Cnt(i) = Cnt(i) + Cnt(i – 1)
        Text = Text & ” | ” & i & “:=” & Cnt(i – 1)
    Next i
    Msg = “Checking the contents of the array – ” & Text
    MsgBox Msg
End Sub