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 , , | 3 Comments

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 , | 8 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 , , , , | 8 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

2016 Microsoft MVP Summit, Part 2: Trump Wins

Tuesday November 8 I was in Redmond at the Microsoft MVP Summit.

It was also the day of the USA presidential election. This was my flashbulb moment, observed from a European perspective:

A small group of us Excel MVPs (Roger Govier, Liam Bastick & me) were working late at MSoft with a bunch of Excel Dev Team Microsofties (including Ben who was with us at Excel Summit South in OZ/NZ earlier this year, and Joe who was at the London GTC last December).

So when we left at 18:45 we had to get a lift back to Bellevue from the Microsoft Redmond campus with Joe because all the MVP transport had finished. There was a bad crash on the freeway so we were stuck in traffic and Joe called his girlfriend to say he was going to be late. The very first thing she said was “its awful – I am very distressed – CNN says 75% probability for Trump”.
Stunned silence and disbelief in the car then we start listening to the radio.
It becomes clear that he really will win.

We eventually get to the UK MVP get-together just after 8 and manage to scrounge a glass of wine but no food left, then at 9 migrate to the Billiard Parlour (this year’s Excel haunt) and watch TV over a beer, trying to come to terms with this disaster. The Canadian Immigration website crashes because too many people try to apply to become Canadian citizens. USA MVP Jon keeps apologizing to us “ I’m sorry. I’m sorry – I did all I could …”

I wake up at 5:30 although my alarm is set for 6.30. The full scale of the disaster becomes apparent. Not only has Trump won but the Republicans have majorities in both the House and the Senate and there is a supreme court judge to be appointed who will hold the balance there.

There are no checks and balances left. The Donald has the keys to the nuclear codes.

Posted in Uncategorized | 1 Comment

2016 Microsoft MVP Summit, Part 1

I was privileged to attend the 2016 Microsoft MVP Summit in Redmond November 6-10 along with many other Excel MVPs.

We had some great sessions with the Excel and the Office Extensibility Product teams, and I have to say that IMHO some of the things they are working on (a few years out) are fairly revolutionary. Much as I would love to tell you all about it, I cannot – strict NDA applies.

Of course the Summit is also an opportunity to meet up with many old and new MVP friends (Thanks to Boriana for sharing this photo):

mvps_summit_2016

From Left -to-right by row in reverse ragged row order:

Ingeborg Hawighorst, Brad Yundt, Heidi Enho.
Charles Williams, Jacob Hildebrand, Boriana Petrova, Mynda Treacy.
Jon Acampora, Zack Barresse,  Bob Umlas.
Ken Puls, Jon Peltier, Frederic Le Guin, Roger Govier.
Henk Vlootman, Jan Karel Pieterse, Bill Manville, Kevin Jones.

This years mystery picture is the well-known  Excel Jedi Master with added beer:

jediexcelmaster

I don’t think this needs many guesses …

Posted in Uncategorized | Tagged , , | Leave a comment

Writing Efficient VBA UDFs Part 15: Adding Intellisense to your UDFs

For several years people have been asking the Excel Dev team to add the capability to create Intellisense for UDFs to work the same way as native Excel functions. Well, now Govert van Drimmelen, author of the wonderful Excel DNA, has made a solution available for both VBA and XLL UDFs. It is now available in public Beta test.

Entering a Function

Lets walk through an example of using a function that has had Intellisense enabled by Govert’s method. As you start typing the name of the function you get a list of functions and an explanation of the function:

intellisense1

Double-clicking the selected function starts entering the function in the formula bar and gives you an additional explanation of the first parameter:

intellisense2

Selecting the name of the function in the Intellisense popup shows a blue link if Help has been enabled for the function:

intellisense3

Clicking the link shows you help:

intellisense4

Pressing Control-A invokes the Function Wizard:

intellisense6

Or pressing Control-Shift-A fills the function in the formula bar and you can double-click each parameter to get text describing the parameter.

intellisense5

Implementing IntelliSense using Govert’s Method

It is really simple to implement this: see Govert’s Excel-DNA Intellisense GitHub page.

For VBA workbooks or add-ins:

  • Download and load the latest ExcelDna.IntelliSense.xll or ExcelDna.IntelliSense64.xll from the Releases page.
  • Either add a sheet with the IntelliSense function descriptions, or a separate xml file

For my example I added a worksheet called _IntelliSense_ with the descriptions:

intellisense7

Note: DNA Intellisense does not itself enable the descriptions in the Function Wizard or build the Help text for you.

Support

At the moment ExcelDNA Intellisense works with Excel 2010 and later versions, Windows 7 and later versions.

You can log issues on the Github site and Govert is very responsive.

Conclusion:

I think Govert has done a fantastic job with this.

Please help by testing this solution so that it can progress beyond Beta test.

 

 

 

 

 

 

 

 

 

Posted in .NET, UDF, VBA, XLL | Tagged , , , , | 11 Comments

Writing Efficient VBA UDFs Part 14: Handling whole columns using Implicit Intersection – Updated

Excel has an interesting and very efficient trick called Implicit Intersection which allows you to use large Named Ranges and whole column references efficiently.

What is Implicit Intersection?

When Excel expects to get a single cell reference but you give it a range of cells instead, Excel automagically works out the result of intersecting the the range of cells with the row or column of the current cell and uses that. For example:

implicit1

Entering =A:A in cell B7 does not return the whole of columns A: it returns the intersection of row 7 and column A. Similarly if A1:A20 is named TwentyCells then entering =TwentyCells in B10 does not return all of A1:A20: it returns the intersection of TwentyCells with row 10.

What happens if there is no intersection?

If you enter =TwentyCells in row 30 there is no intersection, so Excel returns #Value.

What happens if you array-enter the formula?

If you array-enter (the Control-Shift-Enter keys all at the same time) the formula you are telling Excel that you want all the values in the range, not just one. So that is what you get. If you only array-enter the formula into a single cell, (for example array enter {=A:A} in cell B5)  then you only get the first of the result values (a is the result of {=A:A} in cell B5).
If you array enter into more than one cell you get more than one result: for example select cells B2:B5, enter =A:A into the formula bar and hit Control-Shift-Enter and B2:B5 will show a b c d.

So how does this work with functions like VLOOKUP?

Usually you give VLOOKUP a single value or reference to use for the lookup value, and a range to use for the lookup table: =VLOOKUP(A4,$A:$C,3,false).
If you give VLOOKUP a range for the lookup value (=VLOOKUP($A:$A,$A:C,3,false) and do NOT array-enter the formula Excel will do the implicit intersection on the lookup value but not on the lookup table.

implicit2

Implicit Intersection is Amazingly Fast!

Excel has implemented implicit intersection very efficiently: it only passes the single cell reference to the formula or function rather than the whole range.

And only that single cell is treated as a precedent, so the formula/function only gets recalculated when that single cell gets changed/dirtied instead of when any cell in the range gets changed/dirtied.

But UDFs require Special Tricks

Unfortunately ever since Excel 95 implicit intersection does not automagically work for VBA, Automation or XLL UDFs.

But you can still make it happen in 2 different ways:

  • Put a plus sign in front of the function parameter
  • Use VBA to do the implicit intersection for you

A VBA UDF Example

Function ImplicitV(theParam As Variant) As Variant
ImplicitV = theParam
End Function

implicit3

When you enter this very simple UDF with a whole column reference Excel pass a reference to the entire column and the UDF has to handle it all: this is slow – on my fast machine it takes 83 milliseconds.

implicit4

If you add a + sign  Excel only passes the UDF the single cell that is the intersect – this is extremely fast (0.02 milliseconds, over 4000 times faster!).

And the +sign works (very surprisingly) with both text and numbers!

(Thanks to MVP Rory Archibald for pointing this out to me!)

implicit5

As you can see when you use +$A:$A Excel treats it as an expression and therefore evaluates the expression before passing it to the UDF:

  • Evaluating the expression invokes implicit intersection
  • Excel does not pass a range to the UDF – it passes the result of the expression

Adding a plus sign works well but you and your users have to remember to do it!

 Using VBA to do the Implicit Intersection

Here is a general purpose VBA function you can call from inside your VBA UDF to do the implicit intersection for you..

Note: fixed 6/October/2016 to handle implicit intersection with a range on a different sheet.

'
' example UDF
'
Function Implicit2V(theParam As Variant) As Variant
Implicit2V = fImplicit(theParam, Application.Caller)
End Function
'
' helper function to hande implicit intersect
'
Function fImplicit(theInput As Variant, CalledFrom As Range) As Variant
'
' handle implicit intersection of an input with a calledfrom range
'
' Charles Williams - Decision Models - 3 october 2016
'
' check for input range
'
If TypeOf theInput Is Range Then
If TypeOf CalledFrom Is Range Then
'
' both input and called from are ranges
'
If Not CalledFrom.HasArray And theInput.CountLarge > 1 Then
'
' called from is not an array formula and the input has more than 1 cell so do implicit
'
' try intersect with row first
'
Set fImplicit = Intersect(theInput, theInput.Parent.Cells(CalledFrom.Row, 1).EntireRow)
'
' if no intersect try column
'
If fImplicit Is Nothing Then Set fImplicit = Intersect(theInput, theInput.Parent.Cells(1, CalledFrom.Column, 1).EntireColumn)
'
' if still nothing return #Value to mimic XL standard behaviour
'
If fImplicit Is Nothing Then fImplicit = CVErr(xlErrValue)
Else
'
' both are ranges but implicit intersect not applicable
'
Set fImplicit = theInput
End If
Else
'
' calledfrom is not a range but input is a range so return a range
Set fImplicit = theInput
End If
Else
'
' input is not a range so return it in a variant
'
fImplicit = theInput
End If
End Function

This is nearly as efficient as using the plus sign (0.04 milliseconds compared to 0.02 milliseconds) – and has the major advantage that you can build it into your UDFs.

The disadvantage compared to the +sign trick is that the whole range is treated as a precent so the UDF will be recalculated whenever ANY cell in the input range gets dirtied or recalculated.

It still works even when when array-entered or when you add the plus sign, but of course that is going to be slow.

Using Range or Object or Double or String as the parameter datatype.

If you use the + sign trick then the UDF parameter has to either be a Variant or Double/String/Boolean type that matches the data type: Range and Object don’t work because Excel always passes the result value rather than a reference.

If you use the fImplicit helper function without the + sign and pass a range then you can use a parameter data type of Variant or Range or Object.

Conclusions

  • Using Implicit Intersection with functions can be very efficient
  • The + sign trick works well but needs training and remembering to use it!
  • A general purpose helper function like fImplicit is fast and more user friendly than + sign

 

 

Posted in arrays, Calculation, UDF, VBA, XLL | Tagged , , , , | 15 Comments