Strategies for getting the last row in Excel with VBA

Using VBA to get the last row in Excel can be a surprisingly complex task. This post will explore some of the different strategies you can use.

The first thing to do is to decide what you mean by “The Last Row” – some possibilities are:

  • The row Excel considers to be the last row (last row in the Used Range)
  • The last row on a sheet that actually contains data
  • The last row in a range that actually contains data
  • The last data row in a Table ignoring the Totals row
  • The last visible row containing data

Test Data

The test data is constructed to test different last row VBA strategies for ranges and tables.

  • There is a Table in cells $A$4:$A$25. The Table (called Table1) has a header row and a total row.
  • There is a Named Range (called NamedRange) in cells $E$4:$E$30 but $E$26:$E$30 are empty.
  • Rows 5:8, 13:15, 21:25 and 40:42 are hidden so that the Table and Name Range contain more than one visible region.
  • There is data in row 32 below the Table and the Named Range.
  • There is formatting in the hidden row 42.

VBA Strategies

I will look at some of the available strategies for finding the last row.

Used Range

Because Excel internally uses a sparse matrix scheme for the cells in each worksheet (instead of holding a gigantic 16384 by 1048576 array) it has to store information for each cell that has been used. So formatted cells are considered used, as well as cells containing values and formulas. Cells remain flagged as used even when all formatting, values and formulas are removed.

Two VBA methods for working with the used range are Worksheet.UsedRange and the xlCellTypeLastCell option of SpecialCells.


'
' last row in used range
'
jLastUsed = oSht.UsedRange.Rows(oSht.UsedRange.Rows.Count).Row
'
' last visible row in used range
'
jLastVisibleUsed = oSht.Cells.SpecialCells(xlCellTypeLastCell).Row

For my test data jLastUsed returns 42 because there is some formatting on that row, and xlCellTypeLastCell returns 39, which is the last visible row before row 42.

Range.End(xlDown) and Range.End(xlUp)

These VBA methods mimic pressing Ctrl and the up and down arrows.

For name ranges they skip hidden rows but stop at the row before an empty cell.:


'
' last visible cell in Named Range using End(xlUp)
'
jLastVisibleRange = oSht.Range("NamedRange").Offset(oSht.Range("NamedRange").Rows.Count, 0).End(xlUp).Row
'
' last visible cell in Named Range using End(xlDown)
'
jLastVisibleRange2 = oSht.Range("NamedRange").End(xlDown).Row

When using End(xlUp) you want start outside the range in an empty cell, so I used Offset to get to the first row below the range. jLastVisibleRange returns 20.

Using End(xlDown) is simpler for a Range: the code start at the first row in the range and ends at the first of the last visible row in the range that contains data and the last row before an empty cell. It also returns 20.

But for Tables End(xlUp) does NOT skip hidden rows!


'
' last row in Table using End(xlUP) - Note End(xlUp ) behaves differently for tables - includes hidden rows
'
jLastInTable2 = oSht.Range("Table1").Offset(oSht.Range("Table1").Rows.Count + 1, 0).End(xlUp).Row
'
' last visible table row using End(xlDown)
'
jLastVisibleTable = oSht.Range("Table1").End(xlDown).Row

So using End(xlUp) starting from the first row after the end of the table returns Row 25 even though that row is hidden.
But End(xlDown) works the same way with a table as with a Range, and so returns row row 20 which is indeed the last visible row in the table.

Range.Find

My favourite method is to use Range.Find.
Using Find on Formulas includes hidden rows, whereas using Find on Values excludes hidden rows.

You can use this method on Worksheet.Cells or on a Range or Table.


'
' last row containing data (using Find in formulas)
'
jLastRangeData = oSht.Range("NamedRange").Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'
' last visible row containing data (using Find in values)
'
jLastVisibleRangeData = oSht.Range("NamedRange").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'
' last row containing data (using Find in formulas)
'
jLastTableData = oSht.ListObjects("Table1").Range.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'
' last visible row containing data (using Find in values)
'
jLastVisibleTableData = oSht.ListObjects("Table1").Range.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

  • jLastRangeData returns 25
  • jLastVisibleRangeData returns 20
  • jLastTableData returns 25
  • jLastVisibleTableData returns 20

Methods using COUNT

Sometimes its simpler to just count the number of rows, add the starting row number and subtract 1.


‘ last cell in Named Range

jLastInRange = oSht.Range("NamedRange").Offset(oSht.Range("NamedRange").Rows.Count – 1, 0).Row

‘ last row in named range current region

jLastInRegion = oSht.Range("NamedRange").CurrentRegion.Rows.Count + oSht.Range("NamedRange").Row – 1

‘ last row in Table

jLastInTable = oSht.ListObjects("Table1").Range.Rows.Count + oSht.ListObjects("Table1").Range.Row – 1

‘ last data row in table (excludes total row)

jLastTableDataRow = oSht.ListObjects("Table1").ListRows.Count + oSht.ListObjects("Table1").Range.Row – 1

  • jLastInRange returns 30 (it counts the empty cells too)
  • jLastInRegion returns 25 (it excludes the bounding empty cells)
  • jLastInTable returns 25
  • jLastTableDataRow returns 24 (ListObject.ListRows excludes the total row and header row so I have not subtracted 1 for the header row)

Conclusions

I was really surprised to find that End(xlUP) worked differently for Tables than for Ranges.

And with Tables sometimes it seems best to work with a Range that represents the table rather than directly with ListRows: the syntax is not always obvious to me.

There are many different ways of finding End rows but mostly I use range.find as the most fool-proof method (but there are still snags with things like Merged cells).

What’s your most frequently used method?

Advertisements
Posted in Excel, Formatting, VBA | Tagged , | 6 Comments

Excel JavaScript API Part 5: The large numbers bug

There is currently (JavaScript Excel API set 1.4) a rather nasty bug when writing large integer numbers (anything larger than int32) back to a range.

The JS can correctly read these large numbers – it just silently gets the wrong answer when writing them back.

Testing what works and what does not work

I ran 5 tests:

  1. 9876543210 – a large integer
  2. 9876543210.0 – a large double that can convert to an integer
  3. 9876543210.1 – a large non-integer double
  4. ‘9876543210’ – a large number as a string
  5. “‘9876543210” – a large number as a string prefixed with ‘

The results were:

  1. 1286608618 – wrong
  2. 1286608618 – wrong
  3. 9876543210.1 – correct
  4. 9876543210 – gives a number but the string type has been ignored by Excel
  5. ‘9876543210 – correct – Excel treats this as string because of the ‘

Here is the test code:


async function run() {
try {
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getActiveWorksheet();
let rng1 = sheet.getRange("A3");
rng1.values = [[9876543210]];
let rng2 = sheet.getRange("A5");
rng2.values = [[9876543210.0]];
let rng3 = sheet.getRange("A7");
rng3.values = [[9876543210.1]];
let rng4 = sheet.getRange("A9");
rng4.values = [['9876543210']];
let rng5 = sheet.getRange("A11");
rng5.values = [["'9876543210"]];

await context.sync();
});
}
catch (error) {
OfficeHelpers.Utilities.log(error);
}
}

The Hacky Bypass

The real problem of course comes when you use JS to read a range and write it back but you don’t know what the range contains. If it’s a large integer your code is FUBAR.

The only current bypass is to loop through the range values, test for large integers and convert them to string by surrounding with ‘.

Conclusion

I sure hope this gets fixed soon!

Posted in Excel, JS-API | Tagged , | Leave a comment

Excel JavaScript API Part 4: The shallow copy problem

I was trying to read the values from a Range and then write modified versions of the values to two different ranges. Should be straightforward:


async function setValue() {
try {
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getActiveWorksheet();
let rng1 = sheet.getRange("A1:A2").load("values");
await context.sync();
let rng2 = sheet.getRange("B4:b5");
let rng3 = sheet.getRange("B6:B7");
rng2.values = rng1.values;
rng3.values = rng1.values;
rng2.values[0][0] = rng2.values[0][0] + 0.9;
rng3.values[0][0] = rng3.values[0][0] + 0.01;
await context.sync();
});
console.log("Done!");
}
catch (error) {
OfficeHelpers.Utilities.log(error);
}
}

Well this does not work: both B4 and B6 have 0.91 added to them: wrong answer!

The reason this does not work is that JavaScript does something called “shallow copying” for objects and arrays. In effect this means that rng2 and rng3 are just different names for rng1: they all refer to the same set of values.

So when you change one variable you are actually changing them all!

Creating a real copy : “Deep Copying”

A “Deep Copy” is one where the values really are copied rather than just adding an extra name for the values. JQuery has a handy method for doing this: JQuery.extend ($ is short for JQuery)


// create deep copy of rng1
let deep1 = [];
$.extend(true, deep1, rng1.values);
// set rng2 = deep copy
rng2.values = deep1;

So now I can assign the deep copy to rng2.
But if I also assign the same deep copy to rng3 I am back in the same trap: rng2 and rng3 would just be different names for the deep copy!

So I need to either create another deep copy to use for rng3 or assign rng1 to rng3.
Of course then when I change rng3 I am also changing rng1 but that does not matter (although probably bad practice) because I am not returning rng1 back to the worksheet.
(rng1 never appears on the left of the = assignment)


async function setValue() {
try {
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getActiveWorksheet();
let rng1 = sheet.getRange("A1:A2").load("values");
await context.sync();
// create deep copy of rng1
let deep1 = [];
$.extend(true, deep1, rng1.values);
let rng2 = sheet.getRange("B4:b5");
let rng3 = sheet.getRange("B6:B7");
// set rng2 = deep copy
rng2.values = deep1;
// set rng3 = rng1
rng3.values = rng1.values;
rng2.values[0][0] = rng2.values[0][0] + 0.9;
rng3.values[0][0] = rng3.values[0][0] + 0.01;
await context.sync();
});
console.log("Done!");
}
catch (error) {
OfficeHelpers.Utilities.log(error);
}
}

Conclusion

Shallow copies are a wonderful JavaScript “Gotcha” for VBA programmers!

Posted in arrays, Excel, JS-API | Tagged , | 1 Comment

Excel JavaScript API Part 3: Benchmark of Processing Arrays Performance – Loop vs Reduce vs COUNTIFS vs VBA

In a previous post I compared several different ways of processing data with VBA. The fastest way was to get the data into a variant array and loop on it.

JavaScript has some powerful array methods so I wanted to see how they performed compared to VBA. I looked at four different ways of processing data using the JS API:

  • Get data into a JavaScript Array and loop
  • Get data into a JavaScript proxy Range object and loop
  • Get data into a JavaScript array and use the JS Reduce method
  • Call the worksheet function COUNTIFS from JavaScript

The Test Data

I am using the same test data as the previous post: 100000 rows with 2 columns.
Column A is randomly 50% populated with “X” and Column B is randomly 50% populated with “Y”.

The task is to count how many rows have an “X” in column A and a “Y” in column B in the same row.

Getting the data from Excel into a JavaScript array

This task is much slower than VBA: JavaScript takes around 290 milliseconds to read 100000 rows and 2 columns, but VBA takes only around 16 milliseconds.

Let’s ignore that and just focus on how efficient JavaScript is at processing arrays.

Benchmark Timings

JSArrayBench

If you ignore the time taken to get the data into the array then JavaScript and VBA are equally efficient – 19 milliseconds each.

I expected it to be faster to process the proxy range object directly rather than copying the data into an array and using that – but I was wrong. Looping directly on the proxy object is a lot slower than looping on an array derived from the proxy object.

Similarly I thought that the array Reduce method would be fast: but Reduce is slower than directly looping the array!

The overall winner in this case is COUNTIFS because the data never gets transferred to VBA or JavaScript so the extra 290 (JS) or 16 (VBA) milliseconds don’t get incurred. Calling a worksheet function from VBA also has a much smaller overhead than calling it from JS.

The JavaScript code

Here are the four different examples of the JavaScript code.

Looping the Array

The complete JavaScript code I am using for the looping the array method looks like this:


var t0;
var t1;
var t2;
$('#run').click(function () {
t0 = performance.now();
invokeRun()
.then(() => {
t2 = performance.now();
console.log("Time to read data from XL " + Math.round(t1 - t0) + " milliseconds.")
console.log("Time to process array " + Math.round(t2 - t1) + " milliseconds.")
})
.catch(OfficeHelpers.logError);
});

function invokeRun() {
return Excel.run(function (context) {
var range1 = context.workbook.worksheets.getItem("Sheet1").getRange("A1:B100000").load('values');
return context.sync()
.then(() => {
var arr = range1.values;
t1 = performance.now();
var nFound = 0;
for (var j = 0; j < arr.length; j++) {
if (arr[j][0] === 'X') {
if (arr[j][1] === 'Y') nFound++;
};
};
console.log("XY pairs found " + nFound);
})
});
}

The invokeRun function loads the values from the range into a proxy range object using context.sync() , then creates a JavaScript array (actually an array of arrays) from the proxy object and loops down the array looking for an X and a Y in the same row.

Looping the Proxy Range Object


function invokeRun() {
return Excel.run(function (context) {
var range1 = context.workbook.worksheets.getItem("Sheet1").getRange("A1:B100000").load('values');
return context.sync()
.then(() => {
t1=performance.now();
var nFound = 0;
for (var j = 0; j < range1.values.length; j++) {
if (range1.values[j][0] === 'X') {
if (range1.values[j][1] === 'Y') nFound++;
};
};
console.log(nFound);
})
});
}

This version of InvokeRun loops directly on the proxy object values rather than on an array derived from the proxy object.

Using the REDUCE array method

This version of InvokeRun uses JavaScript’s REDUCE array method, where you supply an aggregating function to be applied to each row of the array.


function invokeRun() {
return Excel.run(function (context) {
var range1 = context.workbook.worksheets.getItem("Sheet1").getRange("A1:B100000").load('values');
return context.sync()
.then(() => {
t1 = performance.now();
var arr = range1.values;
var nFound = arr.reduce(function (ct, currval, ix, arr) {
if (arr[ix][0] === 'X') {

if (arr[ix][1] === 'Y') {
ct++;
}
}
return ct;
}, 0);
console.log(nFound);
})
});
}

Using Worksheet Function COUNTIFS

This version demonstrates calling a worksheet function on the range. Since the range values never get passed across to JavaScript it is by far the fastest solution for this case!


function invokeRun() {
return Excel.run(function (context) {
var rangeA = context.workbook.worksheets.getItem("Sheet1").getRange("A1:A100000");
var rangeB = context.workbook.worksheets.getItem("Sheet1").getRange("B1:B100000");
var count = context.workbook.functions.countIfs(rangeA, "X", rangeB, "Y");
count.load();
return context.sync()
.then(() => {
t1 = performance.now();
console.log("XY pairs found " + count.value);
})
});
}

The VBA Code

For completeness sake here is the equivalent VBA code for the looping the array case.


Sub FindXY3()
Dim vArr As Variant
Dim j As Long
Dim n As Long
Dim dTime As Double
Dim dTime2 As Double
dTime2 = MicroTimer
vArr = Range("a1:B100000").Value2
dTime = MicroTimer
For j = LBound(vArr) To UBound(vArr)
If vArr(j, 1) = "X" Then
If vArr(j, 2) = "Y" Then
n = n + 1
End If
End If
Next j
Debug.Print "Var array " & n & " Get " & (dTime - dTime2) * 1000 & " Find " & (MicroTimer - dTime) * 1000
End Sub

 

 

Conclusions

JavaScript seems fairly efficient at processing arrays, and is very comparable to VBA.

The problem is the time taken to transfer data from Excel to JavaScript.

Of the JavaScript array methods bench-marked, Reduce and direct processing of the proxy object were slower than direct looping on an array.

Using a worksheet function such as COUNTIFS is considerably faster than looping an array as long as you can avoid transferring the data to VBA or JavaScript.

 

Posted in arrays, JS-API, VBA | Tagged , , | 4 Comments

Excel JavaScript API Part 2: Benchmark of Read/Write Range Performance

As an Excel Office Developer many of the Excel based applications I develop depend on reading data from Excel worksheet ranges, processing it and then writing the processed data back to a worksheet.

In VBA the way to do this is to read the ranges into variant arrays, loop through the arrays and then write them back. Done efficiently this kind of process can be reasonably fast even with large amounts of data.

So how do you do this kind of operation using the Excel JS-API and how does performance compare with VBA?

Note: these tests were done using JS-API release 1.4 with Office Insider Slow (1702.7870.2020). Further performance improvements may be introduced in later versions.

The Test Workbook

The test workbook has 500000 random numbers hard-coded into column A on Sheet1. The benchmark will read subsets of these numbers and then write them back to column D on Sheet1.

The set of subset volumes to be used in the benchmark are on Sheet2!B19:B25.

JS_bench1

The VBA Benchmark Code

The VBA benchmark code reads the subset volumes to be used and then times reading and writing the data.


Option Explicit
Option Base 1
Private Declare Function getFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" _
Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
Function MicroTimer() As Double
' Returns seconds.
Dim cyTicks1 As Currency
Static cyFrequency As Currency
' Initialize MicroTimer
MicroTimer = 0
' Get frequency.
If cyFrequency = 0 Then getFrequency cyFrequency
' Get ticks.
getTickCount cyTicks1
' Seconds = Ticks (or counts) divided by Frequency
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency
End Function

Sub VarBench()
Dim dT1 As Double
Dim dT2 As Double
Dim dT3 As Double
Dim rng1 As Range
Dim rng2 As Range
Dim var As Variant
Dim j As Long
Dim varIn As Variant
Dim varOut() As Variant
'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
varIn = ThisWorkbook.Worksheets("Sheet2").Range("B19:B25").Value2
ReDim varOut(1 To UBound(varIn), 1 To 2)
'
' initialise timer etc
'
dT1 = MicroTimer
'
For j = 1 To UBound(varIn)
dT1 = MicroTimer
Set rng1 = Worksheets("Sheet1").Range("A1").Resize(varIn(j, 1), 1)
Set rng2 = Worksheets("Sheet1").Range("D1").Resize(varIn(j, 1), 1)
var = rng1.Value2
dT2 = MicroTimer
rng2.Value2 = var
dT3 = MicroTimer
varOut(j, 1) = (dT2 - dT1) * 1000
varOut(j, 2) = (dT3 - dT2) * 1000
Next j
'
ThisWorkbook.Worksheets("Sheet2").Range("F19:G25").Value2 = varOut
Application.Calculation = xlCalculationAutomatic
End Sub

The JS API Code

Here is some basic JS (well actually its TypeScript) code to read 500000 cells into a JS range and then write it back to a different range:


$('#run').click(run);

async function run() {
try {
await Excel.run(async (context) => {
let rngFrom = context.workbook.worksheets.getItem("Sheet1").getRange("A1:A500000").load("values");
await context.sync();
let rngTo = context.workbook.worksheets.getItem("Sheet1").getRange("D1:D500000");
rngTo.values = rngFrom.values;
await context.sync();
});
}
catch (error) {
OfficeHelpers.Utilities.log(error);
}
}

This code is using the Typescript Async – Await pattern. The first Let statement identifies the sheet and range and queues the load of the values into the JS range array.
This statement only places a command into the command queue: it does NOT actually load the values. The next statement await context.sync() executes all the commands that are in the queue and actually gets the values.

Similarly the second Let statement identifies the range to be written to, and then the rngTo.Values=rngFrom.values statement queues a command to write the values back. The next statement await context.sync() executes the 2 commands that are in the queue.

So this is the basic JS API pattern using Typescript’s Async Await: it enables the code to be read and understood as though it is processing sequentially and synchronously just like  VBA.

The real value of this asynchronous queuing up of a batch of commands and controlling when to execute them comes when the code is executing online or accessing a server in the cloud.

So now lets look at the JS benchmark equivalent of the VBA code.


$('#run').click(function () {
invokeRun()
});
function invokeRun() {
return Excel.run(async function (context) {
// get number of cells array from worksheets
var actsheet=context.workbook.worksheets.getActiveWorksheet();
var cellsrange = actsheet.getRange("B19:B25").load("values");
// loop. on cells array
var j = 0;
var t0 = 0;
var t1 = 0;
var t2 = 0;
var range1;
var range2;
var range3;
var nCellsArr;

context.sync().then(function () {
nCellsArr = cellsrange.values;
// read and time requested number of cells
function task() {
t0 = performance.now();
range1 = context.workbook.worksheets.getItem("Sheet1").getRange("A1").getResizedRange(nCellsArr[j][0] - 1, 0).load("values");
return context.sync()
.then(function () {
t1 = performance.now();
context.application.suspendCalculationUntilNextSync();
// write back and time the cells just read
range2 = context.workbook.worksheets.getItem("Sheet1").getRange("D1").getResizedRange(nCellsArr[j][0] - 1, 0)
range2.values = range1.values;
return context.sync();
})
.then(function () {
t2 = performance.now();
// create array for timings
var millisecs = new Array(1);
// store timings in millisecs array
millisecs[0] = new Array();
millisecs[0][0] = (t1 - t0);
millisecs[0][1] = (t2 - t1);
context.application.suspendCalculationUntilNextSync();
// write timings back to sheet
range3 = context.workbook.worksheets.getItem("Sheet2").getRange("C19:D19").getOffsetRange(j, 0);
range3.values = millisecs;
return context.sync();
})
.then(function () {
if (j < 6) {
j++;
//console.log(j);
return task();
} else {
context.application.calculate("Full");
console.log("Success");
return context.sync();
}
});
}

return task();

});
});
}

This code is using the Javascript Promises syntax.

The main new things in this code are:

using performance.now() for timings

using suspendCalculationUntilNextSync() to switch off calculation until the next context.sync() has processed the queue of commands.

Benchmark Results

I ran both the VBA benchmark and the JS API benchmark once in Manual calculation mode and once in Automatic calculation mode.

JS_Bench1

JS_Bench2

Note: If you don’t use suspendCalculationUntilNextSync then in Automatic calculation mode the Javascript triggers a recalculation once for every 1500 or so cells it writes. This has a drastic slowing down effect!

Conclusions

As expected writing data back to Excel is considerably slower than reading data.

JavaScript read is between 20 and 200 times slower than VBA, and it looks like there is a much higher JS overhead per read than VBA.

JavaScript write is between 10 and 15 times slower than VBA, but the overhead looks much more comparable.

 

 

 

 

Posted in arrays, Calculation, JS-API, VBA | Tagged , , | 1 Comment

Excel Range.Value(ValueType): What is this parameter?

Whilst visiting Microsoft Research in Cambridge I was chatting to fellow MVP Roger Govier. He mentioned that he was using this strange parameter to the Range.Value (ValueType) method, which I had been meaning to look at for a long time since it was added in Excel 2007.

So I promised him a blog post!

RangeValueDataType

The first thing to do is look at Excel VBA Help.

xlRangeValueDefault 10 Default. If the specified Range object is empty, returns the value Empty (use the IsEmpty function to test for this case). If the Range object contains more than one cell, returns an array of values (use the IsArray function to test for this case).
xlRangeValueMSPersistXML 12 Returns the recordset representation of the specified Range object in an XML format.
xlRangeValueXMLSpreadsheet 11 Returns the values, formatting, formulas, and names of the specified Range object in the XML Spreadsheet format.

Unfortunately that did not really leave me any the wiser. So lets try looking at some values using the VBE Immediate and Locals windows.

I am using some very simple data, a mixture of strings and numbers:

ValueType1

And some very simple code


Sub CopyWithFormat2()
Dim var As Variant
Dim j As Long
var = Range("A8:A11").Value(xlRangeValueDefault)
Range("C8:C11").Value(xlRangeValueDefault) = var
End Sub

But this changes all textual numbers to real numbers and dates!

ValueType2

If you change xlRangeValueDefault (10) to xlRangeValueXMLSpreadsheet (11) you get the unchanged textual numbers.

ValueType3

Looking at var in the immediate window this is what it contains:

<?xml version=”1.0″?>
<?mso-application progid=”Excel.Sheet”?>
<Workbook xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:html=”http://www.w3.org/TR/REC-html40″&gt;
<Styles>
<Style ss:ID=”Default” ss:Name=”Normal”>
<Alignment ss:Vertical=”Bottom”/>
<Borders/>
<Font ss:FontName=”Calibri” x:Family=”Swiss” ss:Size=”11″ ss:Color=”#000000″/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name=”Sheet1″>
<Table ss:ExpandedColumnCount=”1″ ss:ExpandedRowCount=”4″
ss:DefaultRowHeight=”15″>
<Row>
<Cell><Data ss:Type=”String” x:Ticked=”1″>011</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type=”Number”>11</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type=”String” x:Ticked=”1″>11</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type=”String” x:Ticked=”1″>1-11</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>

So this is fine unless you want to do some processing of the data in var!

If you use xlRangeValueMSPersistXML (12) then you get an “Object doesn’t support this action” error message when you try to put var back on the worksheet.

And the immediate window shows this as the contents of var: looks like some kind of pivot cache format.

<xml xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:dt=”uuid:C2F41010-65B3-11d1-A29F-00AA00C14882″
xmlns:s=”uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882″
xmlns:rs=”urn:schemas-microsoft-com:rowset” xmlns:z=”#RowsetSchema”>
<x:PivotCache>
<x:CacheIndex>1</x:CacheIndex>
<s:Schema id=”RowsetSchema”>
<s:ElementType name=”row” content=”eltOnly”>
<s:attribute type=”Col1″/>
<s:extends type=”rs:rowbase”/>
</s:ElementType>
<s:AttributeType name=”Col1″ rs:name=” 011″>
<s:datatype dt:maxLength=”255″/>
</s:AttributeType>
</s:Schema>
<rs:data>
<z:row Col1=”11″/>
<z:row Col1=”11″/>
<z:row Col1=”1-11″/>
</rs:data>
</x:PivotCache>
</xml>

Conclusion

Well: I can’t actually see a scenario in which I could find a use for this!

Have you ever used this in anger?

Any ideas for scenarios where it would be useful?

 

 

 

Posted in Formatting, VBA | Tagged , | 5 Comments

Excel JavaScript API Part 1: Overview & Comparison

I have been spending time in the last few months experimenting with the new JavaScript Excel API.

This is the first of a planned series of posts examining the JS-API from the point of view of an Excel Office developer.

The main advantage of the JS API is that it allows you to write code that works for Windows Excel, Mac Excel, Excel Online and Excel on IOS. The JS API is still immature but is undergoing rapid development by the Microsoft Office Extensibility team, with new releases of the API arriving roughly quarterly.

The JS API uses browser-based JavaScript/TypeScript and HTML and CSS technologies. It’s main objectives are:

  • To work across such a wide variety of end-points (mobile, tablet, cloud, desktop).
  • To target today’s developers who work mainly or exclusively using web technologies .

The API executes asynchronously by queuing up the API interactions with Excel until you request them to be executed in a batch (Context.Sync()).

JavaScript & TypeScript

For VBA or C developers JavaScript is a weird, exasperating but surprisingly powerful language. To mention a few differences:

  • Variables don’t really have Types and are case sensitive. The scope of variables (called Lexical Scope) is seriously weird and seems to often catch out even seasoned JavaScript developers.
  • Arrays are not really arrays and only have one dimension, so you quickly learn about arrays of arrays. They don’t have fixed upper bounds and can be sparse. They have powerful methods like MAP, REDUCE, SLICE, and SORT. (But its really hard to make JS array sort work the same way as an Excel sort!)
  • Functions are first class citizens – you can pass them around like variables – Anonymous/Lambda functions are fully supported – you really need to get your head around this!
  • Asynchronous processing is handled by things called Promises which can be chained together with some difficulty (this situation is known as the Pyramid of Doom)
  • JavaScript as a language is still changing fairly rapidly and has features that may or may not be supported by particular browsers
  • JavaScript’s popularity is partly bound up with and fed by the many very powerful and freely available frameworks (JQuery, AngularJS, …)

And by the way JavaScript is not a script language version of Java: it is a completely different language.

TypeScript is an attempt by Microsoft to fix some of the more egregious JavaScript failings.
TypeScript is basically a Transpiler – it compiles TypeScript into JavaScript so that just about anything that can execute JavaScript can also execute transpiled TypeScript.
Two major advantages of TypeScript are:

  • You can assign Types to variables and they have sensible scope!
  • You can use Async Await instead of Promises which makes your code much more readable and avoids the Pyramid of Doom.

Excel API Technologies Compared

There are 4 main families of Excel APIs:

Excel ‘C’ XLL API

This API is the most tightly integrated and lowest publicly available interface to Excel:

  • Best performing API
  • Low-level interface requires C programming and memory management skills.
  • Visual Studio IDE
  • Runs in the Excel Process.
  • Most capability for UDFs (multi-threaded, asynchronous, RTD, Cluster etc)
  • Only covers a subset of the Excel Object Model
  • Limited native UI support
  • Used as a foundation layer by many other products/platforms/languages (XLL Plus C++, Excel DNA .NET, Addin Express .NET, PyXLL Python, FCell F# …)
  • Windows Desktop only
  • Application addin level only
  • Can use all the C and C++ frameworks (Standard Template Library, BOOST …)

Excel COM and Automation API

This API is the richest and most widely used API

  • Primary languages VBA and VB6
  • Good performance
  • Widest object model support
  • Supports single-threaded synchronous UDFs
  • Runs in the Excel Process
  • Windows
  • Mac OS Desktops/Laptops (but VBA only with some differences to Windows VBA)
  • Macro recorder provides low entry point
  • Built-in but ancient IDE for Windows VBA
  • Can be embedded in a document or used as an addin at application level.
  • VBA largely unchanged for many years and has only a limited and dying framework

COM-Interop API

This API adds an additional .NET interop layer on top of the COM/Automation interface. Ideally this would have been a useful stopgap until a proper .NET Excel interface was developed, but sadly that never happened.

  • Poor performance
  • Runs in separate process
  • Supports .NET framework and languages
  • Windows only
  • Visual Studio IDE
  • Primarily application level but can be bound to a document using VSTO
  • Not suitable for UDFs
  • Microsoft’s offering is VSTO

Products such as Excel DNA and Addin Express support both the COM-Interop .NET world and the ‘C’ API to enable proper UDF support, improved performance whilst keeping the richness of the .NET framework.

Excel JS-API Release 1.4

The new kid on the block! This is the API that is currently being actively developed by Microsoft, so it is very much a moving target. Comments below reflect the 1.4 Version of the API as at February 2017.

  • Your code runs unchanged across multiple endpoints.
    • But this can result in lowest common denominator support
  • Supports the many JavaScript frameworks
  • Works best with TypeScript
  • Good Web Support
  • Office Store support
  • Runs in a separate browser process
  • Requires Internet: no offline capability
  • No UDF support currently
  • Very poor performance
  • Targeted at professional web developers
  • Object model support
    • Limited but rapidly expanding
    • Single workbook only: cannot copy between, open, close or save workbooks
    • Limited control of Calculation and Screen Updating
    • No Copy Paste Special
  • Hybrid Cloud/Application/Workbook Level
  • Batched Asynchronous processing only

What Next?

My next blog posts on JS will focus on the performance of the JS-API.

If you want to learn more about the JS-API I recommend Building Office Addins by Michael Zlatkovsky, who is a developer on the Microsoft Office Extensibility team.

 

 

Posted in .NET, JS-API, UDF, Uncategorized, VBA, XLL | Tagged , , , , | 7 Comments

Why Structured References are slow in Excel 2013 but fast in Excel 2016

Tables have a bad reputation for performance.

Zack (Firefytr) Barresse (who wrote the definitive guide to Excel Tables with Kevin (Zorvek) Jones) recommends a limit of around 10K rows for tables if you want to keep performance reasonable.

Prompted by a thread on the Excel-L forum I thought I should spend some time researching this.

Example Problem

Eric Lacroix kindly posted a test problem on Dropbox. I have simplified it further to make it clearer what is going on.
The workbook has a Table with 15000 rows and 2 calculated columns.

structref1Columns C and D contain COUNTIFS formulas referencing columns :

structref2XL 2013 Table Calculation Speed

A full calculation on this workbook with Excel 2013 takes 3.6 seconds on my 4.5GHZ I7 6700K.

XL 2013 Table Editing Speed

In Manual Calculation mode if you copy Column B2:B15000 (which do NOT contain any formulas) then doing a Past Special Values back onto column B takes 1.9 seconds!

There is no calculation time involved in this operation and none of the formulas in columns C and D are recalculated or re-evaluated. It is just the paste values operation that takes the time.

XL 2013 Range Calculation Speed and Editing Speed.

If you convert the table to a normal range, which converts the structured references to normal range references, then

  • Full Calculation still takes 3.6 seconds
  • But the Paste operation takes about 2 milliseconds! About 1000 times faster.

So the slowdown is :

  • Not caused by Calculation
  • Caused by Structured References

Bypassing the problem in Excel 2013

After doing some more research I discovered that the problem is caused by Excel 2013 being extremely slow to flag all the formulas containing structured references to the data in Column B for recalculation (make them dirty).
And its only slow if the formulas are not already dirty (but note that doing a recalculation automatically “cleans” all the formulas)

If you set the ForceFullCalculation property of the workbook to true then Excel does not bother to dirty formulas. The downside is that Excel then always does a full calculation of all the formulas in the workbook rather than a smart recalculation of only the dirtied formulas.

So it’s a trade-off: faster editing but slower calculation.

Excel 2016 fixes the Problem

I was surprised to find that when I tried to duplicate the problem with Excel 2016 I could not!

The Excel team have fixed the slowdown! (But don’t seem to have told anyone).

Posted in Calculation | Tagged , , , , , | 5 Comments

Excel Versions Screen Test (Updated): how fast is Screen Updating?

Gurs has an interesting benchmark he has been running on various systems and Excel versions over the years. His results seem to show a massive performance decline in later Excel versions.

Looking at his benchmark and its VBA code you can see that has ScreenUpdating ON and repeatedly calculates in Automatic mode. Each calculation triggers some RANDBETWEEN functions with a number of dependent cells.
Gurs does not want to speed up his benchmark because that would destroy his historic speed comparisons.

But the problem is that a large portion of the time in his benchmark is taken by screen updating, and so his benchmark results vary significantly depending what part of the worksheet is actually visible on the screen, and hence how many visible cells get refreshed at each calculation.

Running Gurs Benchmark with different Excel versions but on the same PC

I ran Gurs benchmark on my desktop PC with Excel 2003 to Excel 2013., with a constant screen area visible (rows 1:118 and columns A:BF). Since I cannot install Excel 2016 on the same PC as previous versions without causing unwanted problems I used a VM on my desktop and also ran the benchmark on my Surface Pro 3.

gurs

This shows loops per second (higher is better) with Screen Updating On and Off, the ratio of OFF to ON and the ratio to Excel 2003.

The results for Excel 2003, 2007 and 2010 show that:

  • Excel 2007 and 2010 are slower than Excel 2003
  • Screen Updating On is 15-20 times slower than Screen Updating Off

But something changed with Excel 2013!

  • Screen Updating ON gets significantly faster
  • Screen Updating OFF gets significantly slower

My visual impression is that Excel 2013 does not try to update the screen on every iteration when the update frequency is high, and this is the reason for the change.
And although I don’t have an exact comparison for Excel 2016 it looks comparable to Excel 2013.

Creating a Pure Screen Updating Benchmark

So my next step was to try to create a pure screen updating benchmark.

In column A I put 28 =RAND() formulas, and then in columns B:V i put very simple formulas that linked back to column A.

screentest1

This gives me 616 cells that will change on each calc, and its easy to keep all 616 cells visible on the screen. The VBA code times 10000 calculations with Screen Updating Off and again with Screen Updating Nn. The difference between these is the time taken by the screen updating.


Sub ScreenTest()
'
' time screenrefresh
'
Dim i As Long

Dim tStart As Double
Dim tEnd As Double
Dim tScreenOn As Double
Dim tScreenOff As Double

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
tStart = MicroTimer
For i = 1 To 10000
.Calculate
Next i
tEnd = MicroTimer
tScreenOff = (tEnd - tStart)
.ScreenUpdating = True
tStart = MicroTimer
For i = 1 To 10000
.Calculate
Next i
tEnd = MicroTimer
tScreenOn = (tEnd - tStart)
End With
    MsgBox "Off " & Int((tScreenOff) * 1000) & _
" On " & Int(tScreenOn * 1000) & _
" Diff " & Int((tScreenOn - tScreenOff) * 1000) & " Millisecs"

End Sub

The timing results for this test of screen updating are:

screentest3

For this (very extreme) benchmark Excel 2013 screen updating is about 180 times faster than previous versions.

But with screen updating turned off Excel 2013 runs this benchmark 5 times slower!

I think what has happened is that Excel 2013 is still doing all the work to update and format the values, but has added a check to limit the frequency of requests to Windows to actually repaint the screen.

Note: this does NOT mean that Excel 2013 runs everything 5 times slower: this is a very extreme case benchmark.

 

Posted in Calculation, Formatting, Uncategorized, VBA | 9 Comments

Excel Memory Checking Tool: Using LAA to increase useable Excel memory

Most of us sooner or later get hit by one or more of the dreaded messages “Out of Memory” or “Excel cannot complete this task with available resources” or “Not enough System Resources/Memory to Display Completely” , regardless of how much RAM or how big a swap-file you have.

Excel’s usable memory has been increasing steadily with each version:

  • Excel 2003: 1 Gigabyte of working set memory
  • Excel 2007: 2 Gigabytes of virtual memory
  • Excel 2010, Excel 2013 and Excel 2016 32-bit: 2 Gigabytes of virtual memory
  • Excel 2010, Excel 2013 and Excel 2016 64-bit: 131072 Gigabytes of virtual memory

Although the introduction of the 64-bit versions of Excel  in theory removed any real limitation many people were not able to switch to 64-bit Excel because

  • Most OCX controls are only available in 32-bit
  • Many third party addins are only available in 32-bit

But the need for larger usable memory has also been increasing:

  • Excel models seem to get larger every year
  • Each successive Excel version uses more memory than the previous version
  • PowerPivot and other BI tools need a lot of memory

So earlier this year the Excel team announced and made available a change to 32-bit Excel 2013 and 2016:

Large Address Aware (LAA) capability change for Excel

If you are using a 64-bit version of Windows this change doubles available virtual memory for 32-bit Excel 2013 and 2016 to 4 Gigabytes.

If you are using a 32-bit version of Windows then this change can increase virtual memory for Excel 2013 and and 2016 to 3 Gigabytes, BUT:

  • With 32-bit Windows you need to enable the /3GB boot switch
  • This switch halves the amount of memory (from 2GB to 1 GB) available to 32-bit Windows.

This LAA change was introduced in updates in May and June 2016:

  • For Excel 2013 you need to be using Build 15.0.4833 or later.
  • For Excel 2016 Office 365 you need to be using Build 16.0.6868.2060 or later
  • For Excel 2016 MSI you need to be using Build 16.0.4394.1000 or later

For more details on the LAA change see this Knowledge Base article

Excel Memory Checking Tool

Finding out how much virtual memory Excel is actually using, and what the current maximum limit is for your installation, is surprisingly difficult.

  • Task Manager only shows working set memory, which is not the same thing as virtual memory.
  • Process Explorer can show virtual memory used, but you have to add an additional column.

And I have not found a readily available tool that tells you what Excel’s maximum usable memory is. So I decided to create one using Windows API calls and VBA.
Here are a few examples of the output:

Excel 2013 32-bit with 64-bit Windows: 4GB

win64_xl2013_32Excel 2016 32-bit with 32-bit Windows without the /3GB boot switch: 2GB

excel_2016_32_win_32Excel 2016 64-bit with 64-bit Windows: 131072 GB

excel_64_win64You can download the ShowMemory2 tool from here or here

Let me know of any problems!

Posted in Memory, VBA | Tagged , , , , | 9 Comments