How to create a simple UDF without using VBA?
I am really good at vba, but many times I wish I had a few other tools in my MS Office tool-belt. (Maybe like dot Net?) So today's question is how to create a very simple Excel function is some language other than VBA.
The other day I was googling an unrelated topic when I ran across the following code snippet that is a perfect starting point for my quest.
That post said:
What language is post using, and how hard would it be for me to implement that exact function and nothing else in that same language?
I am presuming I might need to buy some software, but that is of no importance. But, if there are 20 steps that will take me 5 hours to perform, will probably not bother.
The other day I was googling an unrelated topic when I ran across the following code snippet that is a perfect starting point for my quest.
That post said:
First, create the following excel function.
[ExcelFunction]
public static object IsMultipleOfThree([ExcelArgument(All owReferenc e =true)] object input)
{
if (input is ExcelReference er &&
er.GetValue() is double d)
{
return d % 3 == 0;
}
return false;
}
.... other text from that post is of no interest to today's question.
What language is post using, and how hard would it be for me to implement that exact function and nothing else in that same language?
I am presuming I might need to buy some software, but that is of no importance. But, if there are 20 steps that will take me 5 hours to perform, will probably not bother.
Looks like Excel-DNA to me.
It is C#, not VBA.
Additional functionality for Excel coded in a programming language other than VBA is usually the domain of the VBA IDE (Integrated Development Environment) References. (Not to be confused with Excel workbook external references.)
Add the developer menu tab (if you haven't already) and go to the "Tools" menu and click on the "References..." menu item. Function libraries written in other code can be linked to here and used by Excel VBA. You can make you own if you want....
Add the developer menu tab (if you haven't already) and go to the "Tools" menu and click on the "References..." menu item. Function libraries written in other code can be linked to here and used by Excel VBA. You can make you own if you want....
ASKER
Mark: I am already very good at vba, so already know how to install references. My goal is to CREATE one of those references.
Aikimark: I think you are correct that the language being used is C#.
Norie: So far your answer seems the best. and is consistent with Aikimark's response. I followed your link and a few others. I found that:
1. NuGet Package Manager 4.x (PM UI and PM Console) are included with Visual Studio 2017
2. Excel-DNA is an independent project to integrate .NET into Excel. With Excel-DNA you can make native (.xll) add-ins for Excel using C#, Visual Basic.NET or F#, providing high-performance user-defined functions (UDFs), custom ribbon interfaces and more
It appears NuGet requires .NET development which I presume means Visual Studio.
So, I am downloading and installing Visual Studio 2017 Universal Windows Platform development workload. The install looks like it will take at least an hour, so I won't be playing with it this morning. I will report back if I make progress.
Aikimark: I think you are correct that the language being used is C#.
Norie: So far your answer seems the best. and is consistent with Aikimark's response. I followed your link and a few others. I found that:
1. NuGet Package Manager 4.x (PM UI and PM Console) are included with Visual Studio 2017
2. Excel-DNA is an independent project to integrate .NET into Excel. With Excel-DNA you can make native (.xll) add-ins for Excel using C#, Visual Basic.NET or F#, providing high-performance user-defined functions (UDFs), custom ribbon interfaces and more
It appears NuGet requires .NET development which I presume means Visual Studio.
So, I am downloading and installing Visual Studio 2017 Universal Windows Platform development workload. The install looks like it will take at least an hour, so I won't be playing with it this morning. I will report back if I make progress.
I'm not sure that's the right download.
What you probably want is the Community Edition of Visual Studio.
What you probably want is the Community Edition of Visual Studio.
ASKER
I am using this newGet quickstart page which recommend UWP
I first installed Community Edition of Visual Studio. It then gave me about 20 workload choices depending on category. There were 3 Windows workloads. I chose Universal Windows Platform development workload and Ignored .NET desktop development Desktop; development with C++).
But, I am having trouble after I perform the Quick start instruction that says:
The next step is to use f5 to compile/run but I get a compile error.
Error
I then restarted from the beginning being very careful and f5 still would not compile.
I then restarted a third time, but did not make any code changes so f5 compiled fine. But, after I install the using Newtonsoft.json; line, the compile error reappears.
Hope someone here can help me get past this. If not I will post in the visual studio topic.
ee-shot.jpg
I first installed Community Edition of Visual Studio. It then gave me about 20 workload choices depending on category. There were 3 Windows workloads. I chose Universal Windows Platform development workload and Ignored .NET desktop development Desktop; development with C++).
But, I am having trouble after I perform the Quick start instruction that says:
3. Even though you added the Newtonsoft.Json package to the project, red squiggles appears under JsonConvert because you need a using statement at the top of the code file:
The next step is to use f5 to compile/run but I get a compile error.
Error
CS0234 The type or namespace name 'json' does not exist in the namespace 'Newtonsoft' (are you missing an assembly reference?) App3 C:\Users\Bob\source\repos\
I then restarted from the beginning being very careful and f5 still would not compile.
I then restarted a third time, but did not make any code changes so f5 compiled fine. But, after I install the using Newtonsoft.json; line, the compile error reappears.
Hope someone here can help me get past this. If not I will post in the visual studio topic.
ee-shot.jpg
I thought you wanted to create a UDF for Excel?
If you do then I'm pretty sure it's not UWP you want.
PS I'm not saying you don't need UWP, think it's a requirement for NuGet.
If you do then I'm pretty sure it's not UWP you want.
PS I'm not saying you don't need UWP, think it's a requirement for NuGet.
ASKER
You are 100% correct, a UDF is my ultimate goal.
If someone can point me to simple instructions that will take me directly to such UDF, I would be thrilled.
But since no one has done that, I am sort of flailing around trying to "get my feet wet" with visual studio.
Unfortunately, nothing has panned out because of the compile error.
If someone can point me to simple instructions that will take me directly to such UDF, I would be thrilled.
But since no one has done that, I am sort of flailing around trying to "get my feet wet" with visual studio.
Unfortunately, nothing has panned out because of the compile error.
Since this is C#, I suspect you might be in a VSTO run time environment. Is this true?
What sort of VBA UDF are you trying to create?
What sort of VBA UDF are you trying to create?
Do you have the VS Community Edition installed?
ASKER
The UDF in my first post is a perfect starting point.
Yes, I still have vs community 2017 installed. It is a full IDE environment that does not use the run time environment.
One of its components is Visual Studio Installer which lets me pick from the following workloads.
I originally had the uwp workload, but never got the quickstart to work.
I am now uninstalling that workload and installing the .net desktop development.
Windows (3)
==> .NET desktop development Build WPF, Windows Forms, and console applications using C#, Visual Basic, and F#.
==> Desktop development with C++ Build Windows desktop applications using the Microsoft C++ toolset, ATL, or MFC.
==> Universal Windows Platform development Create applications for the Universal Windows Platform with C#, VB, JavaScript, or optionally C++.
Web & Cloud (7)
==> ASP.NET and web development Build web applications using ASP.NET, ASP.NET Core, HTML/JavaScript, and Containers including Docker support.
==> Azure development Azure SDKs, tools, and projects for developing cloud apps, creating resources, and building Containers including Docker support.
==> Python development Editing, debugging, interactive development and source control for Python.
==> Node.js development Build scalable network applications using Node.js, an asynchronous event-driven JavaScript runtime.
==> Data storage and processing Connect, develop, and test data solutions with SQL Server, Azure Data Lake, or Hadoop.
==> Data science and analytical applications Languages and tooling for creating data science applications, including Python, R and F#.
==> Office/SharePoint development Create Office and SharePoint add-ins, SharePoint solutions, and VSTO add-ins using C#, VB, and JavaScript.
Mobile & Gaming (5)
==> Mobile development with .NET Build cross-platform applications for iOS, Android or Windows using Xamarin.
==> Game development with Unity Create 2D and 3D games with Unity, a powerful cross-platform development environment.
==> Mobile development with JavaScript Build Android, iOS and UWP apps using Tools for Apache Cordova.
==> Mobile development with C++ Build cross-platform applications for iOS, Android or Windows using C++.
==> Game development with C++ Use the full power of C++ to build professional games powered by DirectX, Unreal, or Cocos2d.
Other Toolsets (3)
==> Visual Studio extension development Create add-ons and extensions for Visual Studio, including new commands, code analyzers and tool windows.
==> Linux development with C++ Create and debug applications running in a Linux environment.
==> .NET Core cross-platform development Build cross-platform applications using .NET Core, ASP.NET Core, HTML/JavaScript, and Containers including Docker support.
Yes, I still have vs community 2017 installed. It is a full IDE environment that does not use the run time environment.
One of its components is Visual Studio Installer which lets me pick from the following workloads.
I originally had the uwp workload, but never got the quickstart to work.
I am now uninstalling that workload and installing the .net desktop development.
Windows (3)
==> .NET desktop development Build WPF, Windows Forms, and console applications using C#, Visual Basic, and F#.
==> Desktop development with C++ Build Windows desktop applications using the Microsoft C++ toolset, ATL, or MFC.
==> Universal Windows Platform development Create applications for the Universal Windows Platform with C#, VB, JavaScript, or optionally C++.
Web & Cloud (7)
==> ASP.NET and web development Build web applications using ASP.NET, ASP.NET Core, HTML/JavaScript, and Containers including Docker support.
==> Azure development Azure SDKs, tools, and projects for developing cloud apps, creating resources, and building Containers including Docker support.
==> Python development Editing, debugging, interactive development and source control for Python.
==> Node.js development Build scalable network applications using Node.js, an asynchronous event-driven JavaScript runtime.
==> Data storage and processing Connect, develop, and test data solutions with SQL Server, Azure Data Lake, or Hadoop.
==> Data science and analytical applications Languages and tooling for creating data science applications, including Python, R and F#.
==> Office/SharePoint development Create Office and SharePoint add-ins, SharePoint solutions, and VSTO add-ins using C#, VB, and JavaScript.
Mobile & Gaming (5)
==> Mobile development with .NET Build cross-platform applications for iOS, Android or Windows using Xamarin.
==> Game development with Unity Create 2D and 3D games with Unity, a powerful cross-platform development environment.
==> Mobile development with JavaScript Build Android, iOS and UWP apps using Tools for Apache Cordova.
==> Mobile development with C++ Build cross-platform applications for iOS, Android or Windows using C++.
==> Game development with C++ Use the full power of C++ to build professional games powered by DirectX, Unreal, or Cocos2d.
Other Toolsets (3)
==> Visual Studio extension development Create add-ons and extensions for Visual Studio, including new commands, code analyzers and tool windows.
==> Linux development with C++ Create and debug applications running in a Linux environment.
==> .NET Core cross-platform development Build cross-platform applications using .NET Core, ASP.NET Core, HTML/JavaScript, and Containers including Docker support.
ASKER
That was way more painful than I wanted.
When I get more time I will post a "cookbook" which show step by step how to do everything.
For right now, this link was my Rosetta Stone to a =HeloWorld() UDF. (The link misspells Hello, but I think it was intentional.)
http://www.codelabplugins.com/2018/01/31/hello-world-excel-your-first-user-defined-function-udf-in-microsoft-excel/
After i followed the link's steps VS opens Excel and I can put =HeloWorld() into any cell.
Vba can only reference the function by using s = Application.Evaluate("=hel
I then saved the workbook, then close Excel and VS.
Unfortunately, the next time I open the workbook, the helo world function stops working.
I expect that a little more work will reveal a way to permanently put heloWorld into a workbook .
And maybe I'll figure out a way that vba can say s = application.worksheetfunct
But for now the project has given me a headache. I will close this thread in a few days when I have the energy to revisit it.
When I get more time I will post a "cookbook" which show step by step how to do everything.
For right now, this link was my Rosetta Stone to a =HeloWorld() UDF. (The link misspells Hello, but I think it was intentional.)
http://www.codelabplugins.com/2018/01/31/hello-world-excel-your-first-user-defined-function-udf-in-microsoft-excel/
After i followed the link's steps VS opens Excel and I can put =HeloWorld() into any cell.
Vba can only reference the function by using s = Application.Evaluate("=hel
I then saved the workbook, then close Excel and VS.
Unfortunately, the next time I open the workbook, the helo world function stops working.
I expect that a little more work will reveal a way to permanently put heloWorld into a workbook .
And maybe I'll figure out a way that vba can say s = application.worksheetfunct
But for now the project has given me a headache. I will close this thread in a few days when I have the energy to revisit it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to all for their help