excel vba range with cells code example
Example 1: excel vba difference between .value .value2 .text .formula .formular1c1
'Go with:
Range.Value2 '<------Best way
'It is almost always the best choice to read from or
'write to an Excel cell or a range... from VBA.
'Each of the following can be used to read from a range:
v = [a1]
v = [a1].Value
v = [a1].Value2
v = [a1].Formula
v = [a1].FormulaR1C1
'Each of the following can be used to write to a range:
[a1] = v
[a1].Value = v
[a1].Value2 = v
[a1].Formula = v
[a1].FormulaR1C1 = v
'NB: The 'Text' property is READ ONLY and so cannot be used to write
'to a range.
'To read many values from a large range, or to write many values,
'it can be orders of magnitude faster to do the entire operation
'in one go instead of cell by cell:
arr = [a1:z999].Value2
'If 'arr' is a variant variable, the above line actually creates
'an array of variants 26 columns wide and 999 rows tall and places
'this entire array inside 'arr':
MsgBox LBound(arr, 1) <--displays: 1
MsgBox LBound(arr, 2) <--displays: 1
MsgBox UBound(arr, 1) <--displays: 999
MsgBox LBound(arr, 2) <--displays: 26
[a1].Resize(UBound(arr), UBound(arr, 2).Value2 = arr
'The above line writes the entire array to the worksheet no matter
'how big it is (as long as it will fit in a worksheet).
'The default property of the range object is the 'Value' property.
'So if no property is specified, the 'Value' property is actually
'silently referenced.
'However, 'Value2' is the quickest property to access range values
'and when reading, it returns the true underlying cell value. It
'ignores Number Formats, Dates, Times, and Currency and returns
'numbers as the Double data type, always. Since 'Value2' attempts
'to do less work, it is always faster.
'The 'Value' property, on the other hand, checks if a cell value is
'formatted as a Date or Time and will return a value of the
'VBA Date data type in these cases. If your VBA will be working
'with Date data type, it may make sense to retrieve them with
'the 'Value' property. And writing a VBA Date data type to a cell
'will automatically format the cell as date or time. And
'writing a VBA Currency data type to a cell will automatically
'format the cell as currency.
'Similarly, 'Value' checks for cell currency formatting and then
'returns values of the VBA Currency data type. This can lead to
'loss of precision as the VBA Currency data type only recognizes
'four decimal places and so values are rounded to four places,
'at most. And strangely, that precision is cut to just two decimal
'places when using 'Value' to write a Currency variable to a range.
'The read-only 'Text' property always returns a VBA String data type.
'The value returned by 'Range().Text' is a textual representation of
'what is displayed in each cell, inclusive of Number Formats, Dates,
'Times, Currency, and Error text. This is not an efficient way to
'get numerical values into VBA as implicit or explicit coercion
'is required. 'Text' will return ####### when columns are too thin
'and it will slow down even more when some row heights are adjusted.
'Text' is always VERY slow compared to 'Value' and 'Value2'.
'However, since 'Text' retains how values look in cells, it may
'be useful, especially for populating userform controls with
'properly formatted text values.
'Similarly, both 'Formula' and 'FormulaR1C1' always return values
'as a VBA String data type. If the cell contains a formula then
'Formula' will return its A1-style representation and 'FormulaR1C1'
'will return its R1C1 representation. If a cell has a value instead
'of a formula then both 'Formula' and 'FormulaR1C1' ignore all
'formatting and return the true underlying cell value like 'Value2'
'does... but then takes a further step and converts that value to a
'string. Again, this is not an efficient way to get numerical
'values into VBA as implicit or explicit coercion is required.
'However, 'Formula' and 'FormulaR1C1' must be used to read cell
'formulas. And they SHOULD be used to write formulas to cells.
'If A1 has the numeric value of 100.25 with a currency formatting
'of $#,##0.00_);($#,##0.00) consider the following:
MsgBox [a1].Value 'Displays: 100.25
MsgBox TypeName([a1].Value) 'Displays: Currency
MsgBox [a1].Value2 'Displays: 100.25
MsgBox TypeName([a1].Value2) 'Displays: Double
MsgBox [a1].Text 'Displays: $ 100.25
MsgBox TypeName([a1].Text) 'Displays: String
MsgBox [a1].Formula 'Displays: 100.25
MsgBox TypeName([a1].Formula) 'Displays: String
MsgBox [a1].FormulaR1C1 'Displays: 100.25
MsgBox TypeName([a1].FormulaR1C1) 'Displays: String
'NB: Since the Default Property of the Range Class is 'Value' then
'a concise way to reference the cell value is:
MsgBox [a1] 'Displays: 100.25
MsgBox TypeName([a1]) 'Displays: Currency
'Reference:
' https://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/Example 2: excel vba get a range of full columns
To get a contiguous range of entire rows, use this VBA function:
Function WSRows(ws As Worksheet, Row1&, Rows&)
Set WSRows = ws.Rows(Row1).Resize(Rows)
End Function
'-----------------------------------------------------------------
MsgBox WSRows(Sheet1, 11, 9).Address '<--displays: $11:$20
'To get the values in the range into a variant array:
v = WSRows(Sheet1, 11, 9)
'-----------------------------------------------------------------
'Sister function to a get range of contiguous full columns:
Function WSCols(ws As Worksheet, Col1&, Cols&)
Set WSCols = ws.Columns(Col1).Resize(, Cols)
End Function