Werkend met Excel, veelal in VBA, blijkt het dat bij de ontwikkelaars vaak dezelfde vragen opduiken. Om hier enigszins wat antwoorden op te geven, is deze pagina opgezet. Van simpel tot complex, gewoon een verzamelbak van weetjes. Vanwege het feit dat dit internationaal is, is deze pagina in het Engels.

Working with Excel, mainly in VBA, it turns out that developers often have the same questions. To give some answers, this page is setup. Varying from simple to complex, it's just a collection of know-how. Due to the fact that those questions are asked all over the globe, this page is in English.

Used syntax:
  WB is a defined variable type WorkBook
  WS is a defined variable type WorkSheet
  RNG is a defined variable type Range

VBA: A few warnings!! When you record macro's with Excel, it uses objects like ActiveWorkbook, ActiveSheet, Selection and so on. Mind you, that when you run a macro that takes a bit longer and in the mean time you click on another open workbook, the macro will continue to execute its actions on that workbook/worksheet. Train yourself in using ThisWorkbook (which is the workbook to which the macro belongs), and use variables to address worksheets and ranges.

Set WS = ThisWorkbook.Worksheets("MyWorksheet")

or

Set RNG = Thisworkbook.Worksheets("MyWorksheet").Range("A1")

Avoid anything starting with "Active". Also, don't do a Range.Select and then execute your actions on the Selection object. Selecting cells or ranges costs time, even if the screenupdating is switched off. Again you introduce a risk if the user selects another cell in the mean time. Execute your actions on the Range variable instead.

VBA: Get the last cell in a defined range If you have your range defined as for instance Dim RNG as Range and set it to something like ActiveSheet.UsedRange, you can get the last cell of that range using RNG(RNG.Count)

VBA: Get an area surrounding a filled cell. If you want to have the total range of used cells surrounding a specific cell, use RNG.CurrentRegion. This will give you the range that is, calculated from the indicated cell, surrounded by empty rows, empty columns or the boundaries of the worksheet. Mind you: this generates an error if the worksheet on which the range lies, is protected.

Excel: Getting MAX values grouped by another column. To do this, you can use Array Formulas. An array formula is input by pressing Ctrl-Shift-Enter after editing the formula.

VBA: Get a range in a 2-dimensional array Sometimes in VBA it is easier to have you sheet data in an array. It is very easy to do this. It doesn't matter what the size of your range is, it is always returning a 2-d array.

  Dim Arr as Variant
  Set RNG = Range("A1").CurrentRegion
  Arr = RNG

The other way around is just as easy:

  RNG = Arr.

VBA: Get a Range containing only one row into an 1-dimensional array If your range contains only one row or only one column, you can have this data in an 1-dimensional array. This makes it easy to handle the data. For a column:

  Dim Arr as Variant
  Arr = Application.Transpose(RNG.Value)

For a row:

  Dim Arr as Variant
  Arr = Application.Transpose(Application.Transpose(RNG.Value))

So for a row it is a double transpose!

VBA: Using a variable number of parameters in a Sub or Function Call It is possible to use Optional to give you the opportunity to pass a parameter or not. But if the number of parameters is unknown and can differ from call to call, it is easier to use ParamArray. ParamArray must alway be defined as an array of Variant and must always be the last parameter of the definition. Here is a simple example:

Function ConcatAll (ParamArray Arg() As Variant) As String
    Dim i As Integer
    Dim TempRes As String
    TempRes = ""
    For i = Lbound(Arg) to Ubound(Arg)
      TempRes = TempRes & Arr(i)
    Next i
    ConcatAll = TempRes
  End Function

A call will be like:

Debug.Print ConcatAll("These are ", 3, " arguments.")
Debug.Print ConcatAll("There are ", 2+2+1 , " million bicycles in ", "Bejing", " but not in ", "Tokyo")

One example where I use this is to mimic in VBA the String.Format function from .NET. This replaces {0} with the first argument, {1} with the second and so on: So String.Format ("The {0} ate {1} {2}.","ape",3,"bananas") gives "The ape ate 3 bananas.".

Public Function StringFormat(strInput As String, ParamArray Arg() As Variant) As String
  Dim i As Integer
  For i = LBound(Arr) to UBound(Arr)
    strInput = Replace(strInput, "{" & i & "}", Arg(i))
  Next i
  StringFormat = strInput
End Function