VBA Excel

This page contains some useful VBA code for Microsoft Excel

Dynamic Named Range

This produces a dynamic named range of n columns excluding the title row

=OFFSET(WORKSHEETNAME!$A$2,0,0,COUNTA(WORKSHEETNAME!$A:$A)-1,n)

This uses named range in an Excel Graph (in X or Y series value of source data)

=WORKBOOKNAME.xls!RANGENAME

---

Date To Text

This formats date into text
=TEXT(A1, "dd/mm/yyyy")

---

Get Dynamic Range into Array

This takes data from spreadsheet and places it in an array. Data in first column and row must be continuous

Public Sub GetData()

Dim wks As Worksheet
Dim rng As Range, colRange As Range
Dim iColumn As Integer
Dim iRow As Integer
Dim arrRange As Variant, arrColRange As Variant
Dim c, r As Integer

'GET HEADER COLUMNS FOR RECORDS
Set wks = Worksheets("sheet1")
wks.Range("A1").Name = "topleftcell"
Range("topleftcell").Select
Set colRange = Range(ActiveCell, ActiveCell.End(xlToRight))
With colRange
  iColumn = .Columns.Count
End With
ReDim arrColRange(1, iColumn) As Variant
arrColRange = colRange.Value

'PRINT OUT COLUMN TO IMMEDIATE WINDOW
For c = 1 To iColumn
   Debug.Print arrColRange(1, c)
Next

'GET DATA WITH HEADER COLUMNS
Range("topleftcell").Select
Set rng = Range("topleftcell").CurrentRegion
With rng
    iColumn = .Columns.Count
    iRow = .Rows.Count
End With
ReDim arrRange(1, iColumn) As Variant
arrRange = rng.Value

Erase arrColRange
Erase arrRange

End Sub