MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel JavaScript UDFs Straight to the Point


June 2018

Excel JavaScript UDFs Straight to the Point

After twenty-five comfortable years of writing VBA in Excel, the writing is on the wall: A new language is coming to Excel developers. Starting in May 2018, User-Defined-Functions written in JavaScript will run cross-platform. What you write for Excel for Windows will also work in Excel Online. JavaScript is a new thing for those of us comfortable with VBA. This book will get you up to speed on writing and deploying JavaScript UDFs.

Bill Jelen, MrExcel

category: JavaScript UDFs
covers: Excel 2016 Developer Preview

Updates

Please see the recent updates at the bottom of this page.

Product Details
  • eBook:  41 pages
  • Publisher: Holy Macro! Books
  • Print ISBN: N/A
  • PDF ISBN: 978-1-61547-247-5
  • ePub ISBN: 978-1-61547-368-7
  • Mobi (Kindle) ISBN: 978-1-61547-146-1

Excel users are used to build custom functions—called user-defined functions (UDFs)—in Windows and Mac versions of the application. UDFs can be simply written in Visual Basic Editor (VBE) using the Visual Basic for Applications (VBA) language.

UDFs are not portable to Excel Online and Excel for iOS due to the missing VBA capability on these platforms. Therefore, Microsoft has improved the JavaScript API that was already being used for Office add-in development to allow developers to create custom functions attached to the add-ins. These custom functions can be used like any other native functions or UDFs in Excel. While Microsoft calls these functions JavaScript custom functions, they can also be called JavaScript UDFs, which is the name used in this book.

This book shows the process of creating JavaScript UDFs in Excel in Developer Preview for Office Insider program subscribers.

  • The Office Insider Program
    • Joining the Office Insider Program

      • Downloading and Installing Office Insider
      • Signing Up for the Office Insider Program
  • Getting Familiar
    • Add-in Source Files and File Structures

      • The Loader HTML File—udfs.loader.html
      • The JSON Metadata File—udfs.metadata.js
      • The JavaScript Functions File—udfs.functions.js
      • The Add-in Manifest File—udfs.manifest.xml
  • Warming Up
    • Setting Up the Environment

      • Sharing the Catalog Folder on the Network
      • Adding the Trusted Add-in Catalog in Excel
    • Installing and Testing the JavaScript Add-in

      • Inserting the Excel JavaScript UDFs Add-in in Excel
      • Testing the Add-in
      • About Caching the Source Files
    • Extending the Add-in

      • Creating a New Function to Accept a Range as a Parameter
  • Getting Confident
    • Fetching Data from Web Services

      • Using a Web Service for Stock Prices
      • Adding an Asynchronous Function
      • Fetching Multiple Symbol Prices with a Single Web Service Call
    • Adding a Final Touch

      • Creating a Streamed Function
  • Wrapping Up
    • Final Words

      • What's Next?
      • The Final Function Code
  • Appendix
    • Hosting Source Files Locally

      • Installing IIS on Windows 7
      • Adding MIME Type for .json Extension
      • Copying Sources Files to the Local Server
      • Updating the Manifest File

About The 'Straight to the Point' Series

Books in this series are designed to thoroughly cover one targeted aspect of Excel.

Updates

  • 2018-09-25: Office JavaScript library and method change.
    1. Custom Functions developers at Microsoft switched to a dedicated JavaScript library.

      udfs.loader.html file, line 9:

      Changed from:

      <script src="https://unpkg.com/@microsoft/office-js@1.1.7-adhoc.3/dist/office.js" type="text/javascript"></script>

      to:

      <script src="https://officedev.github.io/custom-functions/lib/custom-functions-runtime.js" type="text/javascript"></script>
    2. Instead of OfficeExtension.Promise method, Promise object is used alone.

      udfs.functions.js file, line 26 and 58:

      Changed from:

      return new OfficeExtension.Promise(function(setResult, setError){

      to:

      return new Promise(function(setResult, setError){

Where to Buy

MrExcel Store

PDF from MrExcel.com Store:

e-Book from MrExcel »