What is the difference between "macros and user-defined functions," in Excel?

All,

Thank you for your input, but the book I was reading says otherwise: User defined functions can be written in JavaScript and VBA.

Well, unless you have asked the question wrong, that book is wrong.  What is the book and in what context does it say you can create Excel UDF's in JavaScript ?

JavaScript is Microsoft's next generation automation language that is compatible across platforms and in browsers. While it remains a partial feature set of the VBA language as implemented back in the 90's, it continues to grow each release. The advantage to working with JavaScript or Apps is that, unlike VBA, the JavaScript model can run on all versions of Excel while VBA only runs on Mac and Windows clients.

Kevin

Additionally, all a UDF or User Defined Function is is a function versus a sub that returns a value. That's it. There is nothing special about a UDF - there is no place where the coder says "this here chunk of code is a UDF and that over there is not." I have thousands of routines in my VBA libraries that I have used in various projects over the years. All routines are implemented as either subs, functions, or properties. None were designed to be UDFs. And yet I can call any public function in any of my general code modules from a worksheet formula. Does that make them UDFs? Only if that's what you want to call them.

Kevin

I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Distinguished Expert in Excel 2018
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
              Experts Exchange Top Expert VBA 2018 to 2020

One small issue with Kevin's excellent discussion: if a UDF is called by a macro (Sub) that was launched by clicking a button (or other macro triggering activity)--it can do anything that a Sub can do, including changing the worksheet user interface. The limitations Kevin described only apply to UDF called by worksheet formulas.

Well, unless you have asked the question wrong, that book is wrong.  What is the book and in what context does it say you can create Excel UDF's in JavaScript ?

Office-js user defined functions are a fairly new feature in Excel. Given the fact that very, very few people use Office-js, it's not surprising that @Bill Prew wasn't aware of the feature. https://docs.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-overview 

Bill,

The book is called Microsoft Excel 2019 inside out. To provide more context, it says user defined functions will be created using JavaScript for Excel online, since VBA does not work on Excel online.

All,

Thank you for your input. I am still in chapter one of the book, but based upon what is written and JavaScript is something new being introduced.

Brad stated "[I]f a UDF is called by a macro (Sub) that was launched by clicking a button (or other macro triggering activity)--it can do anything that a Sub can do, including changing the worksheet user interface."

There is really no such thing as a UDF or User Defined Function. There never was and, I suspect, there never will be. It's a concept that Microsoft introduced to help us think of a function written in VBA - and now JavaScript - as a routine that can be called or referenced from an Excel cell formula. Put another way, anything you want to call a UDF is also just a regular public function that returns a result. Calling it a UDF doesn't really mean anything. I suppose all we can really say is that if you call a public function from a cell formula than it is, for the moment, a UDF.

Stating that you can call a UDF from a sub and it can do anything a sub or any other code can do is misleading. A more appropriate statement would be that any code invoked via a cell formula cannot do certain things like change the user interface. In fact, we can, from that public function invoked via a cell formula, call any other function or sub, private or public, call any worksheet/workbook module function, make Windows API calls, and instantiate a class and call it's properties and methods. I can even display a user form although I would not recommend doing so. But, if any of that code tries to change the worksheet appearance - the code is immediately terminated. However, with a global task queue, I can trick Excel into letting me do anything to the worksheet appearance from a supposed UDF by taking advantage of the fact that all cell formulas are calculated (UDFs called) before the worksheet/workbook Calculate event is generated: the UDF is called, it pushes an entry on the task queue and exits, Excel eventually generates a worksheet/workbook event, and that code picks up where the UDF left off and can do anything it wants to the worksheet/workbook.

So now a question a reader might have is: Now that I know that all I need to do to access a bit of VBA logic from a cell formula is to encapsulate it in a public function and return a result, how can I tell if my code was, in fact, invoked from a cell formula and not some other sub or function? The answer is to look at the Application.Caller property:

    If TypeName(Application.Caller) = "Range" Then
        MsgBox "Caller is a cell formula"
    Else
        MsgBox "Caller is not a cell formula"
    End If

Another interesting property is the Application.Volatile property which, when included in the function code, tells Excel to call that function or UDF whenever the worksheet/workbook is calculated regardless of whether any upstream cells have changed.

Other interesting tidbits include the fact that Microsoft's new calculation engine tends to be a little over zealous with the calls to functions referenced in cell formulas. The current engine calls every function a minimum of three times per calculation cycle in order to determine if additional calls are necessary (whether it observes any changes in the result from call to call.) This means that doing the UDF thing is a bit more expensive these days in terms of time to calculate a workbook.

Finally, the new formula evaluation architecture is more array oriented. See this article about Formula versus Formula2 and things like the at sign (@) that started automatically appearing in front of cell formulas with references to UDFs or public functions:

https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2

What this means for our UDF AKA public functions is that we can return a two dimensional array and Excel will, without entering the formula as a multiple cell array formula, "spill" the values into neighboring cells. If there are any pre-existing values in the way then then the formula cell displays the new #SPILL! error. Additional awesomeness is achieved if said UDF that returns a two dimensional array is included in a more complex formula - that whole formula is turned into an array formula.

For example, if Foo(Parameter) returns a one row, five column result of numbers, then this formula will produce five numbers multiplied by 2 and "spilled" into the formula cell and the four cells to the right:

    =Foo(Parameter)*2

Kevin

Interesting thread here...

I was aware that MS has been working on JS with office apps, but did not realize that it was now available for the online 365 version of Excel.  I'm a retired person these days, so don't have Office 365, rather use the desktop based Office 2019 version of the products.  Right now that was the more cost effective alternative for me.

What does this mean for the Office 365 desktop version of Excel, does it or will it support JS code now or at some point, or are the two platforms diverging a bit?

---

»bp

There are basically two paths to automation: VBA and JavaScript Apps. VBA is relatively stagnant and only available on the Windows and Mac clients. JavaScript Apps are Microsoft's answer to Google Sheets and work on all platforms - Windows, Mac, Android, and browsers. If anything, Except for VBA, Microsoft is trying to bring all platforms together.

Kevin

Thank you Kevin.