Are you over 18 and want to see adult content?
More Annotations
ГДЗ: готові домашні завдання, підручники, презентації та матеріали з літератури | GDZ4YOU
Are you over 18 and want to see adult content?
Holger Freier - Online Marketer & Coach
Are you over 18 and want to see adult content?
/jdrch – I AM your target demographic. Listen up.
Are you over 18 and want to see adult content?
International Kindergarten Preschool Singapore
Are you over 18 and want to see adult content?
NABA Butterfly Garden and Habitat Program
Are you over 18 and want to see adult content?
웹툰 소식, 정보, 추천 그리고 리뷰가 있는 곳, 웹툰인사이트(WebtoonInsight)
Are you over 18 and want to see adult content?
A complete backup of searchbourne.com
Are you over 18 and want to see adult content?
Favourite Annotations
A complete backup of boysonshow.blogspot.com
Are you over 18 and want to see adult content?
A complete backup of fullstacktutorials.com
Are you over 18 and want to see adult content?
A complete backup of photoshopofpyrates.over-blog.com
Are you over 18 and want to see adult content?
A complete backup of cambiodecamiseta.com
Are you over 18 and want to see adult content?
A complete backup of igonenatural.com
Are you over 18 and want to see adult content?
A complete backup of nana-turopathe.com
Are you over 18 and want to see adult content?
A complete backup of practicalbusinessideas.com
Are you over 18 and want to see adult content?
A complete backup of decoyfairy.tumblr.com
Are you over 18 and want to see adult content?
A complete backup of simplyinvesting.com
Are you over 18 and want to see adult content?
A complete backup of centerpointsecurities.com
Are you over 18 and want to see adult content?
Text
SCREENTEST2
←Excel Versions Screen Test (Updated): how fast is Screen Updating? screentest2. By fastexcel | Published December 14, 2016 | Full size is583 × 227 pixels
WHATIF2 | EXCEL AND UDF PERFORMANCE STUFF Visit the post for more. FULLCOL | EXCEL AND UDF PERFORMANCE STUFF ←Excel Full Column References and Used Range: Good Idea or Bad Idea?SCREENTEST3
By fastexcel | Published December 21, 2016 | Full size is 571 × 243 pixels. screentest2. Bookmark the permalink . WHATIF1 | EXCEL AND UDF PERFORMANCE STUFF By fastexcel | Published December 16, 2015 | Full size is 704 × 176 pixels. WhatIf4. Bookmark the permalink . LASTROWS | EXCEL AND UDF PERFORMANCE STUFF Visit the post for more.WIN64_XL2013_32
←Excel Memory Checking Tool: Using LAA to increase useable Excelmemory
EXCEL AND UDF PERFORMANCE STUFF 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 STACKING (APPENDING) ARRAYS/RANGES IN EXCEL: FORMULAS This post is a follow up to my last post Using Constant Arrays and Expressions in Excel Formulas. I will explore how some of the general purpose array-handling functions in FastExcel SpeedTools Extras compare with formulas using native Excel functions. I asked Sam (sgbhide@gmail.com who frequently comments on these blog posts) to collaborate by creating EXCEL WHAT-IF DATA TABLES: FASTER CALCULATION WITH VBA For some reason this year I keep on meeting Excel's What-If Data Tables. These are used (often in real estate and financial valuation models) to simplify doing sensitivity analysis against a range of values for certain inputs such as interest rates. What-If Data Tables are a great tool but they tend to calculate very slowly,SCREENTEST2
←Excel Versions Screen Test (Updated): how fast is Screen Updating? screentest2. By fastexcel | Published December 14, 2016 | Full size is583 × 227 pixels
WHATIF2 | EXCEL AND UDF PERFORMANCE STUFF Visit the post for more. FULLCOL | EXCEL AND UDF PERFORMANCE STUFF ←Excel Full Column References and Used Range: Good Idea or Bad Idea?SCREENTEST3
By fastexcel | Published December 21, 2016 | Full size is 571 × 243 pixels. screentest2. Bookmark the permalink . WHATIF1 | EXCEL AND UDF PERFORMANCE STUFF By fastexcel | Published December 16, 2015 | Full size is 704 × 176 pixels. WhatIf4. Bookmark the permalink . LASTROWS | EXCEL AND UDF PERFORMANCE STUFF Visit the post for more.WIN64_XL2013_32
←Excel Memory Checking Tool: Using LAA to increase useable Excelmemory
EXCEL AND UDF PERFORMANCE STUFF 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 EXCEL AND UDF PERFORMANCE STUFF ProfVBA has added a Start call at the beginning of the sub and End calls before both the Exit Sub and End Sub statements. This statement in FastExcel ProfVBA inserts a start call after the current line in the body of your code module. 490 oCM.InsertLines lBodyLine + 1, "gfxlProfVBA.ProfVBAProcStart """ & sProcID & """". FINDING VBA BOTTLENECKS WITH VBA PROFILER When you are faced with slow-running or complex VBA projects it can be very helpful to have a tool that allows you to time the individual parts of your code or trace the execution path as event-driven jumps occur. Googling for VBA profiler shows severalSCREENTEST2
←Excel Versions Screen Test (Updated): how fast is Screen Updating? screentest2. By fastexcel | Published December 14, 2016 | Full size is583 × 227 pixels
SCREENTEST3
By fastexcel | Published December 21, 2016 | Full size is 571 × 243 pixels. screentest2. Bookmark the permalink . TASK2 | EXCEL AND UDF PERFORMANCE STUFF Visit the post for more. GURS | EXCEL AND UDF PERFORMANCE STUFF ←Excel Versions Screen Test (Updated): how fast is Screen Updating? gurs. By fastexcel | Published December 14, 2016 | Full size is 722 ×227 pixels
YIGALEDERY_SPEAKER
YigalEdery_Speaker. By fastexcel | Published February 7, 2016 | Full size is 199 × 248 pixels. Speakers. Ben_Rampson_Speaker. Bookmark thepermalink .
SPEAKERS | EXCEL AND UDF PERFORMANCE STUFF Speakers. By fastexcel | Published February 7, 2016 | Full size is 2340 × 297 pixels. YigalEdery_Speaker. Bookmark the permalink .BEN_RAMPSON_SPEAKER
Ben_Rampson_Speaker. By fastexcel | Published February 7, 2016 | Full size is 205 × 288 pixels. YigalEdery_Speaker. Bookmark the permalink.
COMMENTS ON: 2012 MVP SUMMIT So encouraging to see my linkedin group mates here in the photos. With you all in spirit. Make Excel Great. We are following. By: Charles FINDING VBA BOTTLENECKS WITH VBA PROFILER When you are faced with slow-running or complex VBA projects it can be very helpful to have a tool that allows you to time the individual parts of your code or trace the execution path as event-driven jumps occur. Googling for VBA profiler shows several STACKING (APPENDING) ARRAYS/RANGES IN EXCEL: FORMULAS This post is a follow up to my last post Using Constant Arrays and Expressions in Excel Formulas. I will explore how some of the general purpose array-handling functions in FastExcel SpeedTools Extras compare with formulas using native Excel functions. I asked Sam (sgbhide@gmail.com who frequently comments on these blog posts) to collaborate by creating EXCEL WHAT-IF DATA TABLES: FASTER CALCULATION WITH VBA For some reason this year I keep on meeting Excel's What-If Data Tables. These are used (often in real estate and financial valuation models) to simplify doing sensitivity analysis against a range of values for certain inputs such as interest rates. What-If Data Tables are a great tool but they tend to calculate very slowly, FULLCOL | EXCEL AND UDF PERFORMANCE STUFF ←Excel Full Column References and Used Range: Good Idea or Bad Idea? WHATIF2 | EXCEL AND UDF PERFORMANCE STUFF Visit the post for more.WIN64_XL2013_32
←Excel Memory Checking Tool: Using LAA to increase useable Excelmemory
STRUCTREF1 | EXCEL AND UDF PERFORMANCE STUFF ←Why Structured References are slow in Excel 2013 but fast in Excel2016
TASK2 | EXCEL AND UDF PERFORMANCE STUFF Visit the post for more.YIGALEDERY_SPEAKER
Visit the post for more. EXCEL AND UDF PERFORMANCE STUFF 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 FINDING VBA BOTTLENECKS WITH VBA PROFILER When you are faced with slow-running or complex VBA projects it can be very helpful to have a tool that allows you to time the individual parts of your code or trace the execution path as event-driven jumps occur. Googling for VBA profiler shows several STACKING (APPENDING) ARRAYS/RANGES IN EXCEL: FORMULAS This post is a follow up to my last post Using Constant Arrays and Expressions in Excel Formulas. I will explore how some of the general purpose array-handling functions in FastExcel SpeedTools Extras compare with formulas using native Excel functions. I asked Sam (sgbhide@gmail.com who frequently comments on these blog posts) to collaborate by creating EXCEL WHAT-IF DATA TABLES: FASTER CALCULATION WITH VBA For some reason this year I keep on meeting Excel's What-If Data Tables. These are used (often in real estate and financial valuation models) to simplify doing sensitivity analysis against a range of values for certain inputs such as interest rates. What-If Data Tables are a great tool but they tend to calculate very slowly, FULLCOL | EXCEL AND UDF PERFORMANCE STUFF ←Excel Full Column References and Used Range: Good Idea or Bad Idea? WHATIF2 | EXCEL AND UDF PERFORMANCE STUFF Visit the post for more.WIN64_XL2013_32
←Excel Memory Checking Tool: Using LAA to increase useable Excelmemory
STRUCTREF1 | EXCEL AND UDF PERFORMANCE STUFF ←Why Structured References are slow in Excel 2013 but fast in Excel2016
EXCEL WHAT-IF DATA TABLES: FASTER CALCULATION WITH VBA For some reason this year I keep on meeting Excel's What-If Data Tables. These are used (often in real estate and financial valuation models) to simplify doing sensitivity analysis against a range of values for certain inputs such as interest rates. What-If Data Tables are a great tool but they tend to calculate very slowly,SCREENTEST1
←Excel Versions Screen Test (Updated): how fast is Screen Updating? screentest1. By fastexcel | Published December 14, 2016 | Full size is280 × 167 pixels
LASTROWS | EXCEL AND UDF PERFORMANCE STUFF Visit the post for more. STRUCTREF2 | EXCEL AND UDF PERFORMANCE STUFF ←Why Structured References are slow in Excel 2013 but fast in Excel2016
SCREENTEST3
Visit the post for more. TASK2 | EXCEL AND UDF PERFORMANCE STUFF Visit the post for more. WE_HEADER | EXCEL AND UDF PERFORMANCE STUFF ←Excel Summit South: Auckland Sydney Melbourne March 2016 We_Header. By fastexcel | Published November 23, 2015 | Full size is 960 × 260pixels
SPEAKERS | EXCEL AND UDF PERFORMANCE STUFF Visit the post for more. Fill in your details below or click an iconto log in:
BEN_RAMPSON_SPEAKER
Visit the post for more. COMMENTS ON: WRITING EFFICIENT VBA UDFS PART 10 Hi Charles. I've noticed that UDFs recalculate whenever you delete cells. This can cause massive delays when deleting entire columns, because the UDF gets called for each and every cell it STACKING (APPENDING) ARRAYS/RANGES IN EXCEL: FORMULAS This post is a follow up to my last post Using Constant Arrays and Expressions in Excel Formulas. I will explore how some of the general purpose array-handling functions in FastExcel SpeedTools Extras compare with formulas using native Excel functions. I asked Sam (sgbhide@gmail.com who frequently comments on these blog posts) to collaborate by creating EXCEL WHAT-IF DATA TABLES: FASTER CALCULATION WITH VBA For some reason this year I keep on meeting Excel's What-If Data Tables. These are used (often in real estate and financial valuation models) to simplify doing sensitivity analysis against a range of values for certain inputs such as interest rates. What-If Data Tables are a great tool but they tend to calculate very slowly, FULLCOL | EXCEL AND UDF PERFORMANCE STUFF ←Excel Full Column References and Used Range: Good Idea or Bad Idea? WHATIF2 | EXCEL AND UDF PERFORMANCE STUFF Visit the post for more.SCREENTEST3
By fastexcel | Published December 21, 2016 | Full size is 571 × 243 pixels. screentest2. Bookmark the permalink . WHATIF1 | EXCEL AND UDF PERFORMANCE STUFF By fastexcel | Published December 16, 2015 | Full size is 704 × 176 pixels. WhatIf4. Bookmark the permalink .WIN64_XL2013_32
←Excel Memory Checking Tool: Using LAA to increase useable Excelmemory
STRUCTREF2 | EXCEL AND UDF PERFORMANCE STUFF ←Why Structured References are slow in Excel 2013 but fast in Excel2016
YIGALEDERY_SPEAKER
YigalEdery_Speaker. By fastexcel | Published February 7, 2016 | Full size is 199 × 248 pixels. Speakers. Ben_Rampson_Speaker. Bookmark thepermalink .
EXCEL AND UDF PERFORMANCE STUFF 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 STACKING (APPENDING) ARRAYS/RANGES IN EXCEL: FORMULAS This post is a follow up to my last post Using Constant Arrays and Expressions in Excel Formulas. I will explore how some of the general purpose array-handling functions in FastExcel SpeedTools Extras compare with formulas using native Excel functions. I asked Sam (sgbhide@gmail.com who frequently comments on these blog posts) to collaborate by creating EXCEL WHAT-IF DATA TABLES: FASTER CALCULATION WITH VBA For some reason this year I keep on meeting Excel's What-If Data Tables. These are used (often in real estate and financial valuation models) to simplify doing sensitivity analysis against a range of values for certain inputs such as interest rates. What-If Data Tables are a great tool but they tend to calculate very slowly, FULLCOL | EXCEL AND UDF PERFORMANCE STUFF ←Excel Full Column References and Used Range: Good Idea or Bad Idea? WHATIF2 | EXCEL AND UDF PERFORMANCE STUFF Visit the post for more.SCREENTEST3
By fastexcel | Published December 21, 2016 | Full size is 571 × 243 pixels. screentest2. Bookmark the permalink . WHATIF1 | EXCEL AND UDF PERFORMANCE STUFF By fastexcel | Published December 16, 2015 | Full size is 704 × 176 pixels. WhatIf4. Bookmark the permalink .WIN64_XL2013_32
←Excel Memory Checking Tool: Using LAA to increase useable Excelmemory
STRUCTREF2 | EXCEL AND UDF PERFORMANCE STUFF ←Why Structured References are slow in Excel 2013 but fast in Excel2016
YIGALEDERY_SPEAKER
YigalEdery_Speaker. By fastexcel | Published February 7, 2016 | Full size is 199 × 248 pixels. Speakers. Ben_Rampson_Speaker. Bookmark thepermalink .
EXCEL AND UDF PERFORMANCE STUFF 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 EXCEL AND UDF PERFORMANCE STUFF ProfVBA has added a Start call at the beginning of the sub and End calls before both the Exit Sub and End Sub statements. This statement in FastExcel ProfVBA inserts a start call after the current line in the body of your code module. 490 oCM.InsertLines lBodyLine + 1, "gfxlProfVBA.ProfVBAProcStart """ & sProcID & """".SCREENTEST1
←Excel Versions Screen Test (Updated): how fast is Screen Updating? screentest1. By fastexcel | Published December 14, 2016 | Full size is280 × 167 pixels
INDIRECT – EXCEL’S MOST EVIL FUNCTION INDIRECT is an extremely powerful function that is often used to create workbooks that can dynamically adjust to structural changes such as changing the ranges or worksheets or the external workbooks that are being used in formulas. Useful alternatives can be: EXCEL MEMORY CHECKING TOOL: USING LAA TO INCREASE USEABLE 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 increasingSCREENTEST3
By fastexcel | Published December 21, 2016 | Full size is 571 × 243 pixels. screentest2. Bookmark the permalink . LASTROWS | EXCEL AND UDF PERFORMANCE STUFF Visit the post for more. GURS | EXCEL AND UDF PERFORMANCE STUFF ←Excel Versions Screen Test (Updated): how fast is Screen Updating? gurs. By fastexcel | Published December 14, 2016 | Full size is 722 ×227 pixels
STRUCTREF2 | EXCEL AND UDF PERFORMANCE STUFF ←Why Structured References are slow in Excel 2013 but fast in Excel2016
TASK2 | EXCEL AND UDF PERFORMANCE STUFF Visit the post for more.BEN_RAMPSON_SPEAKER
Ben_Rampson_Speaker. By fastexcel | Published February 7, 2016 | Full size is 205 × 288 pixels. YigalEdery_Speaker. Bookmark the permalink.
EXCEL AND UDF PERFORMANCE STUFF Charles Williams on 'Making Excel go Faster'Skip to content
* Home
* About Me
* About this blog
* Making your VBA UDFs Efficient← Older posts
CHECKDA: COMPATIBILITY OF OFFICE 365 DYNAMIC ARRAY FORMULAS Posted on May 20, 2019by fastexcel
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? MY FREE CHECKDA TOOL ALLOWS YOU TO CHECK THAT THE WORKBOOK YOU CREATE USING EXCEL DA WILL NOT CAUSE PROBLEMS WHEN OPENED IN PRIOR EXCELVERSIONS.
The introduction of Dynamic Array formulas in Office 365 Excel (as of July 2019 rolling out to the Office 365 Monthly Channel) 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 manycircumstances.
* 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 good: implicit intersections will be silently converted to the new @syntax.
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).
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 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 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 formulaUSING 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 thepassword 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 onthe ribbon.
CONTROL-SHIFT-J or clicking the Check DA Formulas button shows theCheck DA form.
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
PLEASE LET ME KNOW OF ANY BUGS YOU FIND, AND SEND ME YOUR IMPROVEMENT IDEAS AND EXPERIENCES WITHCHECKDA.Advertisements
Report this ad
Report this ad
Posted in arrays ,
Dynamic Arrays
, Excel
, Uncategorized
, VBA
| Tagged Excel
| 11 Comments
EXCEL SUMMIT SOUTH 2019 Posted on May 13, 2019by fastexcel
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. Posted in Uncategorized| Leave a
comment
FORMULA EXPLORER PRO BETA 4.2 Posted on January 8, 2019by fastexcel
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 underthese 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 NamedFormulas.
* 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. See my previous post on Explorer Profor
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 Excel , ExcelFormulas , Excel
functions , VBA
| 6 Comments
DEVELOP EXCEL CONFERENCE Posted on July 30, 2018by fastexcel
The 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 extensiontechnologies.
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 Addinsand 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 extendedbase.
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 ATDEVELOP EXCEL.
_FREE SPONSORSHIP OFFER – SEE SPONSORS PAGEFOR 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.Posted in .NET ,
arrays , Excel
, JS-API
, UDF
, VBA
, XLL
| 2 Comments
FORMULA EXPLORER PRO Posted on July 19, 2018by
fastexcel
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 FastExcelManager 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 Ihave ever built.
Because it has to work on both Mac and Windows Excel I cannot use anyWindows 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 splitterbars …
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 Posted on January 29, 2018by fastexcel
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 orcomplex 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 in64-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 under32-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 weredeleted.
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 couldbe 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 Excel2010 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 inprevious 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 Posted on September 1, 2017by fastexcel
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 canuse.
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 UsedRange)
* 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 lastrow.
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.1
2
3
4
5
6
7
8
'
' 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 anempty cell.:
1
2
3
4
5
6
7
8
'
' 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. Italso returns 20.
But for Tables End(xlUp) does NOT skip hidden rows!1
2
3
4
5
6
7
8
'
' 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 thetable.
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.1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
'
' 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 20METHODS 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‘
‘ last row in named range current region‘
jLastInRegion =
oSht.Range("NamedRange").CurrentRegion.Rows.CountoSht.RaoSht.Range("NamedRange").Row‘
‘ 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 headerrow)
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 Excel
, VBA
| 9 Comments
EXCEL JAVASCRIPT API PART 5: THE LARGE NUMBERS BUG Posted on April 5, 2017by fastexcel
There is currently (JavaScript Excel API set 1.4) a rather nasty bug when writing large integer numbers (anything larger than int32) backto 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 WORKI ran 5 tests:
* 9876543210 – a large integer * 9876543210.0 – a large double that can convert to an integer * 9876543210.1 – a large non-integer double * ‘9876543210’ – a large number as a string * “‘9876543210” – a large number as a string prefixed with‘
The results were:
* 1286608618 – wrong * 1286608618 – wrong * 9876543210.1 – correct * 9876543210 – gives a number but the string type has been ignoredby Excel
* ‘9876543210 – correct – Excel treats this as string becauseof the ‘
Here is the test code:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
async function run() {try {
await Excel.run(async (context) => { let sheet = context.workbook.worksheets.getActiveWorksheet(); let rng1 = sheet.getRange("A3");rng1.values = ;
let rng2 = sheet.getRange("A5");rng2.values = ;
let rng3 = sheet.getRange("A7");rng3.values = ;
let rng4 = sheet.getRange("A9");rng4.values = ;
let rng5 = sheet.getRange("A11");rng5.values = ;
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
Excel , Javascript
| 4 Comments
EXCEL JAVASCRIPT API PART 4: THE SHALLOW COPY PROBLEM Posted on April 5, 2017by fastexcel
I was trying to read the values from a Range and then write modified versions of the values to two different ranges. Should bestraightforward:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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 = rng2.values + 0.9; rng3.values = rng3.values + 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: WRONGANSWER!
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)1
2
3
4
5
// create deep copy of rng1let 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 assignrng1 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)1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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 rng1let 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 = rng2.values + 0.9; rng3.values = rng3.values + 0.01; await context.sync();});
console.log("Done!");}
catch (error) {
OfficeHelpers.Utilities.log(error);}
}
CONCLUSION
Shallow copies are a wonderful JavaScript “Gotcha” for VBAprogrammers!
Posted in arrays ,
Excel , JS-API
| Tagged Excel
, Javascript
| 1 Comment
EXCEL JAVASCRIPT API PART 3: BENCHMARK OF PROCESSING ARRAYS PERFORMANCE – LOOP VS REDUCE VS COUNTIFS VS VBA Posted on March 16, 2017by fastexcel
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 JavaScriptTHE TEST DATA
I am using the same test data as the previous post: 100000 rows with 2columns.
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 atprocessing arrays.
BENCHMARK TIMINGS
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:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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 === 'X') {
if (arr === '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 OBJECT1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 === 'X') { if (range1.values === '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 ofthe array.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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 === 'X') {
if (arr === '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!1
2
3
4
5
6
7
8
9
10
11
12
13
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 loopingthe array case.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub FindXY3()
Dim vArr As Variant
Dim j As Long
Dim n As Long
Dim dTime As Double
Dim dTime2 As DoubledTime2 = MicroTimer
vArr = Range("a1:B100000").Value2dTime = MicroTimer
For j = LBound(vArr) To UBound(vArr) If vArr(j, 1) = "X" Then If vArr(j, 2) = "Y" Thenn = n + 1
End If
End If
Next j
Debug.Print "Var array " & n & " Get " & (dTime - dTime2) * 1000 & " Find " & (MicroTimer - dTime) * 1000End Sub
CONCLUSIONS
JAVASCRIPT SEEMS FAIRLY EFFICIENT AT PROCESSING ARRAYS, AND IS VERYCOMPARABLE TO VBA.
THE PROBLEM IS THE TIME TAKEN TO TRANSFER DATA FROM EXCEL TOJAVASCRIPT.
OF THE JAVASCRIPT ARRAY METHODS BENCH-MARKED, REDUCE AND DIRECT PROCESSING OF THE PROXY OBJECT WERE SLOWER THAN DIRECT LOOPING ON ANARRAY.
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 Excel
, Javascript
, VBA
| 4 Comments
← Older posts
Older posts
*
SEARCH
Search for:
*
RECENT POSTS
* CheckDA: Compatibility of Office 365 Dynamic Array Formulas * Excel Summit South 2019 * Formula Explorer Pro Beta 4.2 * Develop Excel Conference * Formula Explorer Pro * Excel 2016 Performance Improvements * Strategies for getting the last row in Excel with VBA * Excel JavaScript API Part 5: The large numbers bug * Excel JavaScript API Part 4: The shallow copy problem * Excel JavaScript API Part 3: Benchmark of Processing Arrays Performance – Loop vs Reduce vs COUNTIFS vs VBA*
RECENT COMMENTS
fastexcel on Excel What-If DataTables: Fas…
dugage on Excel What-If Data Tables: Fas… Eddy on UNIQUES and DISTINCTS: explori… fastexcel on UNIQUES and DISTINCTS:explori…
Eddy on UNIQUES and DISTINCTS: explori… Martin Leduc (@Decim… on TEXT vsVALUE vs VALUE2…
fastexcel on TEXT vs VALUE vsVALUE2…
Martin Leduc (@Decim… on TEXT vsVALUE vs VALUE2…
*
POST ARCHIVE
* May 2019 (2)
* January 2019 (1)* July 2018 (2)
* January 2018 (1) * September 2017 (1)* April 2017 (2)
* March 2017 (4)
* February 2017 (1) * December 2016 (1) * November 2016 (3) * October 2016 (2)* July 2016 (1)
* April 2016 (1)
* February 2016 (1) * December 2015 (4) * November 2015 (2) * September 2015 (2)* May 2015 (2)
* February 2015 (1) * January 2015 (2) * December 2014 (1) * November 2014 (2) * October 2014 (3) * September 2014 (2)* August 2014 (2)
* June 2014 (1)
* April 2014 (1)
* February 2014 (1) * January 2014 (3) * December 2013 (2) * November 2013 (4) * October 2013 (6) * September 2013 (4) * February 2013 (3) * January 2013 (3) * December 2012 (4) * November 2012 (2) * October 2012 (2) * September 2012 (1)* July 2012 (6)
* June 2012 (7)
* April 2012 (1)
* March 2012 (2)
* February 2012 (1) * January 2012 (3) * December 2011 (1) * November 2011 (3) * October 2011 (2) * September 2011 (2)* August 2011 (1)
* July 2011 (4)
* June 2011 (8)
* May 2011 (4)
*
CATEGORIES
* .NET
* arrays
* Calculation
* Debug formulas
* Dynamic Arrays
* Excel
* Formatting
* JS-API
* Lookups
* Memory
* Sailing
* UDF
* Uncategorized
* VBA
* Wine
* XLL
*
BLOGROLL
* Andrew's Excel Tips* Andy Pope
* Bob's Blog
* Chip Pearson's site* Contextures
* Daily Dose of Excel * Dick Moffat's Excel & Access Blog* Excel Hero
* J-Walk Spreadsheet Blog * Jan-Karel Pieterse * John Peltier Charting* Methods in Excel
* Microsoft Excel Blog* Newton Excel Bach
* RAD Excel
* Smurf on Spreadsheets*
RSS
* RSS - Posts
* RSS - Comments
*
BLOG STATS
* 1,038,313 hits
*
Advertisements
Report this ad
*
FOLLOW BLOG VIA EMAIL Enter your email address to follow this blog and receive notifications of new posts by email. Join 263 other followersFollow
*
BLOG STATS
* 1,038,313 hits
Excel and UDF Performance Stuff Blog at WordPress.com.Post to
Cancel
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use. To find out more, including how to control cookies, see here: CookiePolicy
* Follow
*
* Excel and UDF Performance Stuff* Customize
* Follow
* Sign up
* Log in
* Report this content * Manage subscriptions* Collapse this bar
Report this ad
Details
Copyright © 2024 ArchiveBay.com. All rights reserved. Terms of Use | Privacy Policy | DMCA | 2021 | Feedback | Advertising | RSS 2.0