Excel Virtually Global

https://excelvirtuallyglobal.com/

Tue 21 July to Thu 23 July 2020

Microsoft’s Most Valuable Professionals, or MVPs, are technology experts
who passionately share their knowledge with the community. They are
always on the “bleeding edge” and have an unstoppable urge to get their
hands on new, exciting technologies. This virtual conference, now in its
fifth year, presents Excel, Data Platform and PowerPoint MVPs, together
with other acknowledged experts from Microsoft and / or around the globe
to present, answer questions and demonstrate the future of Excel and how
it will make your life easier, personally and professionally.
Topics include: Auditing Spreadsheets, Charts, Dashboards, Data
Analysis, Data Types, Dynamic Arrays, Excel Tricks & Tips, Financial
Modelling, Forecasting, Maps, Microsoft 365, New Functions and Features,
PivotTables, Power BI, Power Pivot, Power Query, Presenting Your Data,
Spilled Formulae, Testing, Timelines and developing solutions with DNA
.Net, VBA, Python, R, OfficeScript and Office-JS. And note well – it’s
not all in English, with sessions in Mandarin, Portuguese, Spanish and
Telugu, as well.

Most sessions will be recorded so you may watch them later with
downloads aplenty – there are no medals for staying up to watch the
entire event live!

From your own favourite chair, bring a laptop, an inquisitive mind and
your sense of humour. Be prepared to learn heaps. And remember, it’s
for charity – all profits will be donated to a global COVID-19 research
charity.

Price only US$23 Tickets at
https://www.eventbrite.com/e/excel-virtually-global-a-virtual-excel-summit-tickets-106319837496
(or just click ‘Register’ on the first site)

Posted in Uncategorized | Leave a comment

FastExcel Version 4 – with Introductory Offer

After several man-years of development and a lot of beta testing FastExcel Version 4 has finally hit general availability!

FastExcel V4 is a major rewrite of FastExcel V3 and provides many of the things you have been asking for:

  • Support for the new capabilities and functions of Excel 2016, 2019 and Office 365 Excel
  • 50 Powerful Dynamic Array Functions
  • Formula Explorer for reviewing, undestanding and debugging formulas
  • VBA Performance Profiling
  • Single-click Drilldown to calculation bottleneck formulas
  • Check Workbook for potential problems
  • Choice of Automatic or Manual Install without Admin Privileges, uses .Net 4
  • Separate Ribbon Tabs for Profiler, Manager Pro and SpeedTools
  • Several hundred detailed improvements

Introductory offer: 40% Discount on FastExcel V4 Bundle and FastExcel Manager Pro for Mac!

Purchase FastExcel V4 Bundle using Coupon Code FXLV4INTRO – 40% Discount Offer valid until 15 April 2020.

Purchase FastExcel V4 Manager Pro for Mac using Coupon Code FXLMACINTRO – 40% Discount Offer valid until 15 April 2020.

FastExcel V4 Profiler Improvements

    • Single-Click Drill-Down Wizard: One click drill-down to the slowest formulas on the slowest worksheets

    • Improved Profiling Statistics: Counts of Unique Formulas, CSE Formulas and What-If Tables

    • Improved Profiling Stability

      • Changes in Excel memory managements made determining memory used by each worksheet both inaccurate and a cause of crashes. The measuring worksheet memory option has been removed.
    • Profile Formulas updated for new Excel and SpeedTools functions

    • VBA Performance Profiling

      • Choose VBA Project to Profile
      • Select Modules, Classes, Subroutines and Functions to Profile
      • Drill Down Profiling to blocks or single statements
    • New Check Workbook Report shows for each worksheet a range of statistics on potential workbook trouble spots.

      • Formulas: Counts of – Total Formulas, Unique Formulas, Error Formulas, CSE (Array) Formulas
      • Counts of Data Validation and Conditional Formats
      • Counts of What-If Tables, Table Onjects, Pivot Tables, Charts, Custom XML Parts, Shapes, Hyperlinks, Links, Defined Names
      • Used Range, Waste % and Sparse %

FastExcel V4 Manager Pro Improvements

  • Manager Pro for Mac Excel 365

  • New Formula Explorer Pro – A better way of reviewing, understanding and debugging formulas

    • Treeview of Formula Expressions
    • Normal, Precedent or Dependent Views
    • Forward and backward stepped debugging
    • Fully expanded Arrays expressions
    • Integrated understand of Excel functions like IF, SUMIFS, SUMPRODUCT etc
    • Work with Named Formulas, Conditional Format Formulas, Data Validation Formulas
    • Supports both US English and National Language formulas
  • Name Manager Pro Improvements

    • Integration with Formula Explorer Pro: Review and Debug Named Formulas
    • Support of Office 365 Excel
  • New Check Workbook Report

    • A report for each worksheet showing a range of statistics on potential workbook trouble spots.
      • Formulas: Counts of – Total Formulas, Unique Formulas, Error Formulas, CSE (Array) Formulas
      • Counts of Data Validation and Conditional Formats
      • Counts of What-If Tables, Table Onjects, Pivot Tables, Charts, Custom XML Parts, Shapes, Hyperlinks, Links, Defined Names
      • Used Range, Waste % and Sparse %

FastExcel V4 SpeedTools Improvements

  • Easier formula entry with built-in Intellisense, Function Wizard and On-Line Help Support

  • 100 super-fast powerful multi-threaded worksheet functions

    • Advanced FILTER.IFS, ASUMIFS family of functions
    • Logical AND, OR for array functions
    • Advanced AVLOOKUP and AMATCH functions
  • Exploit the power of Dynamic Arrays with 50 Array-Handling functions

    • Totalling for Dynamic Arrays
      • Dynamically floating or static
      • Columns or Rows totals
    • Stack multiple Arrays/Ranges horizontally or vertically, handling headers
    • Unpivot using single or multiple headers and choice of columns
    • Merge (Join) dynamic arrays and find miss-matches (Anti-Join)
    • Resize & Reshape Arrays
    • Two-way Lookups and Slices
    • Multi-Column and Multi-row Lookups
    • Regular Expressions

Try it out for yourself:

Download FastExcel V4.

Get a 15-day full-featured trial license

You can convert the trial version of FastExcel V4 to a fully licensed version at any time by purchasing one of the FastExcel V4 licensing options.

Want to know more?

View the FastExcel V4 WebHelp

Posted in arrays, Calculation, Debug formulas, Dynamic Arrays, Excel, Lookups, Memory, UDF, VBA | Leave a comment

Backward Compatibility of Office 365 Dynamic Arrays (Updated 23 Sep 2020)

Office 365 Excel Dynamic Arrays are great, but:

What happens when you create a workbook with Dynamic Array  Excel (Excel DA) and send it to someone who does not have Excel DA, and how do you write VBA code that works in both Excel DA and Excel Non-DA?

My free CheckDA tool allows you to check that the formulas you create using Excel DA will not cause problems when opened in prior Excel versions.

The introduction of Dynamic Array formulas in Office 365 Excel has made a fundamental change to Excel formula behavior and syntax:

  • In previous versions such as Excel 2013, Excel automatically used implicit intersection to select a single value from a range in many circumstances.
  • In Office 365 Dynamic Array Excel 2016 (Excel DA), Excel treats all formulas as array formulas unless the implicit intersection operator @ is used, and single-cell dynamic array formulas will spill their arrays to surrounding cells.

Forward Compatibility

For workbooks authored in previous versions of Excel compatibility is great: implicit intersections will be silently converted to the new @ syntax, and VBA code generally works fine.

Backward Compatibility

When workbooks authored in Dynamic Array Excel (Excel DA) are opened in previous Excel versions, in most cases the formulas will be silently converted to the old syntax (@ will be removed where possible, and spilling dynamic arrays converted to array (CSE) formulas).

Formulas created by VBA using Range.Formula will work correctly in both DA and non-DA Excel

But it is possible to create formulas in Excel DA which cause difficulties when opened in previous versions.

CheckDA is a free open-source VBA addin that can scan Excel DA formulas, highlight potential backward compatibility problems and optionally convert some problem formulas to more compatible versions.

Backward Compatibility Problem Areas

VBA code creating formulas

For forward compatibility reasons formulas created using Range.Formula are treated as old-style non-DA formulas. To create a DA formula using VBA you should use the new Range.Formula2.

Therefore if you want your VBA code to run in both DA and non-DA Excel use Range.Formula.
For more details see https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2

VBA Object Model Extensions for Dynamic Arrays

In addition to .Formula2 the Excel team has added a few useful extensions to the VBA object model:

  • Range.HasSpill returns True for both the parent DA formula and a spilled cell
  • Range.SpillParent returns the Range object of the parent DA cell
  • Range.SpillingToRange returns a range object covering the entire spill range
  • Range.SavedAsArray returns true if the range will be converted to a CSE array formula in Non-DA Excel

Functions that do not exist in previous versions

Excel DA introduces many new functions that work with Dynamic Arrays (SORT, UNIQUE, LET …). When you open a workbook containing these functions in non-DA Excel:

  • The cells will still show the values calculated in DA Excel, until they are recalculated.
  • In Non-DA Excel the functions will be prefaced by xlfn. and as soon as you recalculate will show #Name.
  • When the workbook is re-opened in DA Excel the xlfn. prefix is removed and the cells can be recalculated.

This means that limited DA<->NonDA round-tripping is possible even when these functions do not exist in NonDA!

Unexpected @ in a formula producing =_xlfn.SINGLE()

Excel will only remove @ from a formula where previous Excel versions would have used Implicit Intersection to return a single value from a Range or Named Range or function parameter.

You will be warned by Excel DA if you try to enter a formula with @ in an unexpected place, but it is still possible to create such a formula. An unexpected @ will create a _xlfn.SINGLE() in previous versions and will result in #Name when calculated.

For example =@A1 will be converted to =_xlfn.SINGLE(A1) because Excel cannot do Implicit Intersection on a single cell.

CheckDA will flag such formulas as type @

Formula containing a Spill Reference producing _xlfn_ANCHORARRAY()

A formula reference to a dynamic array can use the spill reference suffix #. For example =SUM($B$3#) will SUM the entire dynamic array that starts in $B$3. Spill references get converted to _xlfn_ANCHORARRAY(Reference) in previous versions and will result in #Name when calculated.

CheckDA will flag such formulas as type {#}

Unwanted Array Formulas (CSE) in previous versions

Any formula that Excel DA thinks could return an array (even if it is actually only returning a single value) is converted to a CSE array formula in previous versions.

This may or may not produce a backward compatibility problem – you have to inspect these formulas carefully.

CheckDA flags these formulas as types:

  • {1} a single-cell non-spilling formula
  • # An Excel DA Dynamic Array formula that is spilling or blocked (#Spill!)  will be converted to a fixed CSE formula in previous Non-DA Excel versions such as Excel 2013.
  • {n} a formula entered in Excel DA as a multi-cell CSE formula

Using CheckDA

CheckDA is an open-source VBA XLAM addin.
CheckDA only runs in Windows Office 365 Dynamic Array Excel.

To protect against accidental change CheckDA is protected with the password dm.

You can download the CheckDA addin from here. The zip file contains the CheckDA.xlam and the CheckDa.docx Word document.

Install the CheckDA.XLAM file using Excel’s Addin Manager if you want it permanently installed, or just open the xlam workbook using Excel File Open if you only want to use it in the current Excel session. Once successfully installed you will see CheckDA available on the ribbon.

CheckDARibbon

Control-Shift-J or clicking the Check DA Formulas button shows the Check DA form.

CheckDAForm

The form:

  • Shows formulas in Non-DA Syntax
  • Is Modeless (you can edit formulas while still showing the form)
  • Is Resizeable
  • At start-up scans the active worksheet for unique formulas
  • At start-up filters out formulas with unexpected @ or Spill Ref or Spill or single-cell array formulas

Restate Selected Formulas

Clicking the Restate Selected Formulas button will change any @ or {1} formulas selected in the form to more compatible syntax:

  • @ formulas will have their unexpected @s removed.
  • {1} formulas will be re-entered as non-array formulas.
  • # Spill formulas and {#} Spill Ref formulas cannot be automatically restated.

Undo Restate

Clicking the Undo Restate button will undo the restatement of any formulas restated in this session of CheckDA.

Show Office 365 DA Syntax

This button toggles between showing the formulas in Non-DA syntax and Office 365 Dynamic Array syntax.

Conclusion

The Microsoft Excel team have done an outstanding job of creating, as far as is possible, compatibility between Dynamic Array Excel and previous versions. With a little care you can easily create workbooks using Excel DA that work correctly in Non-DA Excel.

Further Reading

Posted in arrays, Dynamic Arrays, Excel, Uncategorized, VBA | Tagged | 18 Comments

Excel Summit South 2019

If you can get to Australia in July/August check out the Excel Summit South 2019 conference.

http://excelsummitsouth.com/

There is a great list of speakers and a chance to discuss Excel with a Microsoft Dev Team member and a host of Microsoft Excel MVP’s – acknowledged as Experts and Community contributors by Microsoft.

SummitSouth2019

Posted in Uncategorized | Leave a comment

Formula Explorer Pro Beta 4.2

After another 6 months on my quest to create the worlds best tool for exploring, validating, debugging and editing Excel formulas I have made Explorer Pro Beta 4.2 build 418.796 available. Hopefully this will be the final Beta!

The improvements are too numerous to cover in detail but come under these headings:

Improved Functionality Includes:

  • Full support for Dynamic Ranges, Linked Data Types and Rich Data Type Field expressions.
  • Integration with Name Manager Pro to explore and debug Named Formulas.
  • Explore Data Validation formulas, Conditional Format Formulas as well as cell formulas.
  • Support for formulas in 91 languages (thanks to Mourad Louha).
  • Resolved many Mac Excel issues.
  • Lots of bug fixes!

User Interface Cleanup

The previous user interface tried to present too much information
So I simplified, de-cluttered and made it more intuitive.

explorerpro42

See my previous post on Explorer Pro for a comparison

Performance

Profiling the code showed several areas  that could be significantly improved. Some expensive operations such as coloring large precedent ranges are now optional.

Try it out yourself!

Watch the video Explorer 4.2 Overview

Download the Beta zip file

This build expires on the 31st of March 2019

Posted in Calculation, Debug formulas | Tagged , , , | 7 Comments

Develop Excel Conference

ReactorLogoThe Develop Excel conference will take place in London on Thursday October the 18th 2018. It will be held at the Microsoft Reactor (70 Wilson Street London EC2A 2DB).

Are you responsible for building Excel based Addins and Solutions?

Develop Excel gives you a unique opportunity to meet and learn from the leading international developers of the major Excel extension technologies.

You will:

  • Gain understanding of the comparative merits of available tools, languages and APIs for building Excel extensions
  • Discover the implications for developers and solution builders of Microsoft extensions to core Excel (Co-Authoring, Linked rich data types, built-in Power Query, M Language, Javascript interpreter and APIs, cross-platform common code-base etc.)
  • Meet the Developers responsible for:
    • Microsoft Excel Extensibility including Javascript
    • Excel DNA: .Net and Excel
    • PyXLL: Python and Excel
    • Planatech XLL Plus: C++ and Excel
  • Network with, socialize and meet other Excel developers of Addins and Solutions.

The conference focuses on the two dimensions of Excel Development: the extensions Microsoft are making to the Excel platform and APIs, and the Addins and Solutions that developers can build on this extended base.

This event is a community driven one. There is no profit motive, just a desire to gather the best brains in the Excel extension space together to share knowledge.

Register Now.

Please help us to spread the word and encourage people to join us at Develop Excel.

Free sponsorship offer – see Sponsors page for details.

Special thanks to Microsoft for hosting us at the new London Reactor, and for sending a speaker from the Development team responsible for Excel extensibility.Dev-Team

 

Posted in .NET, arrays, Excel, JS-API, UDF, VBA, XLL | 2 Comments

Formula Explorer Pro

I have spent most of the last 18 months or so working on a new tool to improve on Excel’s ancient Trace Precedents and Evaluate formulas tools. It is called Formula Explorer Pro, and it is part of FastExcel Manager Pro.

Formula Explorer Pro supports Windows Excel 2007 through Excel 2016 and Office 365, and also Mac Excel 2016.

And now it’s time for a serious test of Beta version 2.5.

(Beta 2.6 Build 362.792 just uploaded)
For a quick overview of Formula Explorer Pro see https://vimeo.com/272986447

And to download the latest Beta version see http://www.decisionmodels.com/fxl_mgrpro_beta2.htm

Please let me have your feedback, bug reports, suggestions etc.

Explorer Pro Technology

The Explorer Pro Userform is probably the most complex VBA userform I have ever built.

ExplorerProForm

Because it has to work on both Mac and Windows Excel I cannot use any Windows API calls.

The userform:

  • Is modeless
  • Resizeable
  • Has 3 splitter bars
  • Has 2 synchronised Treeview controls and 2 textboxes
  • Adaptive positioning of controls, dependent on both mode and expression selection.

I wish I could say I developed all the technology that underlies this, but it relies heavily on Jan Karel Pieterse and Peter Thornton’s VBA Treeview, and Andy Pope’s work on resizeable userforms and splitter bars …

Other major components of Formula Manager Pro are the formula parser, expression builder, formula indenter, expression evaluator and forward/backward formula debugger.

The formula parser splits a formula into token strings in the local language being used, and then translates the token strings into US English (thanks to Mourad Louha for his help with testing and extending this to 91 different languages).

The expression builder assembles the token strings into expressions that can be evaluated (this includes the strange criteria expressions that occur in functions like SUMIFS).

The formula indenter takes the expressions and builds indented formula strings using the indenting rules that the user has chosen.

The expression evaluator evaluates each of the expressions to produce a scalar or array result. Special handling is needed for things like the criteria expressions in SUMIFS and finding the source precedents that functions like MAX, LOOKUP, SUMIFS, SUMPRODUCT etc are pulling from. Designing the evaluator is hard because there are many different ways you can build sub-expressions from a formula like =A1+B2+C3+D4*E5+F6.

Another tricky evaluation problem is correctly handling implicit intersection, particularly for formulas like =VLOOKUP(A:A,B:F,G:G,H:H) because some of the arguments do implicit intersection and others (B:F) do not. This requires a lookup table for every argument of every native Excel function.

The step-by-step formula debugger actually drove some of the design choices in the expression builder because it requires an unambiguous set of expressions to can be condensed to results for replacing the expression string in the indented formula.

Anyway this is why we need an extended Beta test period!

Posted in Uncategorized | 6 Comments

Excel 2016 Performance Improvements

Excel 2016 Performance Improvements

The Excel team has made a number of performance improvements to Excel 2016. These improvements were rolled out as part of the Office update cycle. The timescale that updates become available to you depends on which update option you are using:

  • Insider
  • Monthly Channel
  • Semi-annual Channel

For more details on the Office 2016 release cadence names see Slow – Fast Level Names.

This post discusses some of the features that have been introduced in Excel 2016 that you can use to improve performance with large or complex workbooks.

Large Address Aware (LAA) Memory improvement for 32-bit Excel

Although 64-bit Excel has extremely large virtual memory limits, 32-bit Excel has been limited to 2 Gigabytes (GB). And many Excel customers have found it difficult to migrate to 64-bit Excel because they use third-party addins and controls that are not available in 64-bit versions.
LAA has now been enabled for 32-bit versions of Excel 2013 and Excel 2016, and will minimize out of memory error messages.
LAA doubles available virtual memory from 2GB to 4GB when using 64-bit Windows, and increases available virtual memory from 2GB to 3GB under 32-bit Windows.

For more details see LAA Capability Change for Excel
To download a tool that shows how much virtual memory is available and how much is being used see Excel Memory Checking Tool

Full Column References

Previously, workbooks using large numbers of full column references and multiple worksheets, for example =COUNTIF(Sheet2!A:A,Sheet3!A1), might use large amounts of CPU and memory when opened, or rows were deleted.
An improvement in Excel 2016 build 16.0.8212.1000 substantially reduces the memory and CPU used in these circumstances.

My test on a workbook with 6 million formulas using full column references failed with an Out of Memory message at 4 GB of virtual memory with Excel 2013 LAA and with Excel 2010 but only used 2 GB virtual memory with Excel 2016.

Structured References

In some circumstances editing Excel Tables where formulas in the workbook use Structured References to the Table could be slow with Excel 2013 and previous versions. This led to the perception that Tables should not be used with large numbers of rows.
Excel 2016 has now fixed this problem.

My test showed an editing operation that took 1.9 seconds in Excel 2013 and Excel 2010 took about 2 milliseconds in Excel 2016.

For more details see Why Structured References are Slow in Excel 2013 but fast in Excel 2016.

Filtering, Sorting Copy/Pasting

The Excel 2016 team studied a number of large workbooks that show slow response when using Filtering, Sorting and Copy/Pasting, and a number of improvements have been made:

In Excel 2013 after Filtering or Sorting or Copy/Pasting many rows Excel could be slow responding or would hang. Performance was highly dependent on the count of all rows between the top visible row and the bottom visible row. An improvement made to the internal calculation of vertical user interface positions in build 16.0.8431.2058 has made these operations much faster.

Opening a workbook with many filtered or hidden rows, merged cells or outlines could cause high CPU load.
A fix in this area was introduced in build 16.0.8229.1000

In the past you could see very slow response after pasting a copied column of cells from a Table with filtered rows where the filter resulted in a large number of separate blocks of rows.
This area has been substantially improved in build 16.0.8327.1000

My test on copy pasting 22000 rows filtered from 44000 rows showed a dramatic improvement:

  • For a Table the time went from 39 seconds in Excel 2013 and 18 seconds in Excel 2010 to 2 seconds in Excel 2016
  • For a Range the time went from 30 seconds in Excel 2013 and 13 seconds in Excel 2010 to virtually instantaneous in Excel 2016

Copying Conditional Formats

In Excel 2013 copy/pasting cells containing conditional formats could be slow.
This has been significantly improved in Excel 2016 build 16.0.8229.0

My test on copying 44000 cells with a total of 386000 conditional format rules showed a substantial improvement:

  • Excel 2010: 70 seconds
  • Excel 2013: 68 seconds
  • Excel 2016: 7 seconds

Adding and deleting Worksheets

My test on Excel 2016 build 16.0.8431.2058 shows a 15-20% speed improvement compared to Excel 2013 when adding and deleting large numbers of worksheets. However Excel 2016 was 5-10% slower than Excel 2010 on this test.

New Functions

Excel 2016 build 16.0.7920.1000 introduced several very useful new worksheet functions:

MAXIFS and MINIFS extend the COUNTIFS/SUMIFS family of functions. These functions have good performance characteristics and should be used to replace equivalent array formulas.

TEXTJOIN and CONCAT let you easily combine text strings from ranges of cells. These functions can replace the slow VBA UDFs typically used in previous versions.

Other Updates to Excel 2016 for Windows

You can find more details of all the other month-by-month improvements that have been made to Excel 2016 at
What’s new in Excel 2016 for Windows.

 

Posted in Uncategorized | 6 Comments

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?

Posted in Excel, Formatting, VBA | Tagged , | 10 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.

30 January 2018 – API set 1.7 – this bug is still there!

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