How to use the Dfunctions in Excel, these include formulas such as DSUM, DGET, DCOUNT, DCOUTA and many more.
There is also part one of a two part series on the Select Case Statement in VBA

Hi allI thought I would send this months newsletter out a bit early, normally I would send it about the 15th of the month, but I may be out of town about then.

Training
I have been getting some great feedback from people taking on the Excel and VBA for Excel training and I am very happy with what I am hearing! It seems that everyone that takes on the remote training finds the method very convenient. I have had many enquiries also regarding the "Your Project Course" training, the downside to this training is that I can only accept 4-5 projects and any one time. This is due to the fact that each of these courses are highly personalized and as you can imagine no two are ever alike! For those of you that have requested this training I apologise that some of you have had to wait a while before I can start. What I would suggest to anybody thinking about doing the "Your Project Course", or Custom training, is that they forward me their intentions and I will add their names to my list. Naturally this does not "lock" the client into anything and does not require any deposit. All it simply means is I will contact the client a week before I can start their training and they then have the option of whether to go ahead or not. I a nutshell, it simply reserves you a place!

Task Automations
The "Task Automations" seems to be a very popular area as well, especially with people that import their data into Excel and then need it formatted and set up accordingly. More often than not, the end result for the client is basically a custom Add-in. This seems to be most popular with Finance brokers and Engineers, but certainly not only these positions

---

In this months newsletter I thought we would look at the Dfunctions in Excel and the Select Case Statement in VBA.

Dfunctions
The reason I am showing the Dfunctions this week is due the large amount of Workbooks I see using array formulas. While array formulas are Ok, in the right situation, they are very, very slow at calculating. Once you have more than 10 or so that are referencing medium to large ranges your Workbook can become very slow. This is because Excel recalculates each time you save, change any value within the referenced range or open the Workbook. I will very rarely recommend the use of array formulas for this reason. When Excel users first discover array formulas they tend to believe they have found the answer to all their problems and use them far too liberally. It only later that they find they have a Workbook which is virtually unusable. Believe me, once you get the hang of the Dfunctions you will always think twice before using another array formula.

The "D" in Dfunctions stands for Database and there are 12 of these Dfunctions within Excel. They can all be found under the category "Database" in the Paste function dialog (Shift+F3). These functions are all designed to work with data that is organised in a list or database. They require that your list or database has headings. They all take three arguments: Database, Field and Criteria.

Database
Is a reference to a range of cells containing your data or list, including their headings. A valid reference for Database might be A1:H500 or MyRange if the range is a named range.

Field
Field is the Column within the List or Database you wish to use in the function. It can be represented in one of three ways

1. A single cell reference, e.g; A1 where A1 contains the column heading
2. Text that represents the column heading e.g; "Amount"
3. A number which is the relative position of the column within the List or Database e.g; If Database was B1:H500 and we wanted Column B as the Field we would use 1, for column C we would use 2 etc.

Criteria
The criteria must be a reference to a range of cells, one of which should contain a copy of the heading from the column we want to evaluate. For example we could use G1:G2 as a Criteria where G1 contains a copy of the heading "Age" and G2 contains a criteria such as >25

As with most features of Excel the best way to see how they work is with an example, so I have included a Workbook example. If for any reason you cannot get the Workbook attachment it will be available for download here:
Dfunctions.zip to go with Issue 5

For the VBA example this month, I thought we would look at the Select Case Statement.

Select Case
One of the best methods for checking single or multiple conditions is the Select Case Statement. This has the syntax
Select Case < Expression to test>
    Case
      Do something
    Case Else
      Do something else
End Select
As you can see the "Select Case" Statement is very similar to the "If" Statement in that it will only perform some action if a condition is met. Let's look at the "Select Case" Statement in it's simplest form
Sub TheSelectCase1()
Select Case Range("A1").Value
  Case 100
  Range("B1") = 50
End Select
End Sub
Now let us say you need to perform any one of 5 actions depending on the Value of Range A1. If so we could use:
Sub TheSelectCase2()
Select Case Range("A1").Value
  Case 100
  Range("B1").Value = 50
  Case 150
  Range("B1").Value = 40
  Case 200
  Range("B1").Value = 30
  Case 350
  Range("B1").Value = 20
  Case 400
  Range("B1").Value = 10
End Select
End Sub
This, in my opinion, is a far better structure and easier to read than an "If" Statement with multiple "ElseIf" Statements. If none of the above Conditions were met, nothing would occur, unless we use the optional "Case Else" Statement, like:
Sub TheSelectCase3()
Select Case Range("A1").Value
  Case 100
  Range("B1").Value = 50
  Case 150
  Range("B1").Value = 40
  Case 200
  Range("B1").Value = 30
  Case 350
  Range("B1").Value = 20
  Case 400
  Range("B1").Value = 10
  Case Else
  Range("B1").Value = 0
End Select
End Sub
So If the Value of Range A1 is NOT 100,150,200,350 or 400 then place a Value of 0 (zero) in Range B1. Now while this demonstrates how we can check multiple conditions with the "Select Case" Statement, what if we want to perform some action if the Range A1 is equal to any one of the Values 100,150,200,350 or 400. If this is the case (no pun intended) we could use:
Sub TheSelectCase4()
Select Case Range("A1").Value
  Case 100, 150, 200, 350, 400
  Range("B1").Value = Range("A1").Value
  Case Else
  Range("B1").Value = 0
End Select
End Sub

Next months newsletter will pick up where we left off on the Select Case statement and show you some other great uses of it.
Please feel free to pass this newsletter on to anybody you see fit, all I ask is you pass it on in it's entirety

Kind regards