User Defined Functions (UDF)

Created On
bySerap bas
You are here:

Microsoft Excel has numerous built-in functions; however, some functionalities that may be critical to your application may not be included in the generic libraries offered by Excel’s VBA or sometimes these functionalities exist but require an excessive series of nested formulas to accomplish, sacrificing the overall maintainability of your application.

Advanced users will often create their own functions to simplify workbooks, add modularity, and improve calculation performance. This can be accomplished by coding User Defined Functions (UDF). Users can effectively create custom formulas that can be executed within the context of the workbook.

Visual Basic (VBA) is Microsoft’s custom coding solution for Microsoft Office products, allowing complex mathematical operations to be shortened and custom capabilities to be introduced.

What are User Defined Functions?
  • UDFs are external functions (i.e. not defined as a built-in function) that the user can create in order to programmatically execute some series of operations on input to produce some form of output.
  • UDFs, unlike macros, cannot alter the structure or format of the workbook (e.g. workbook name, target), other cells (e.g. insert, edit), or fonts.
  • The output of a UDF is typically a literal value that is either displayed within a cell or used in conjunction with other functions. They do not support execution of operations upon other cells, and in that sense they differ from macros. (See examples at the bottom for more information.)
  • UDFs cannot change environment options or set properties.

With version 4.7, SpreadsheetWEB has introduced the ability for advanced users to create user defined functions in C# that can be utilized from within Excel-based models and translated into your custom web page applications.

Benefits of coding User Defined Functions in .NET languages (C# currently supported) over VBA:
  • VBA is Microsoft Office proprietary. Therefore, the file is susceptible to security issues just like any unprotected document.
  • VBA is a workbook-oriented solution and cannot run natively on a server (except within the context of Microsoft Office), whereas managed code can be executed in any environment that supports the .NET Framework Runtime.
  • In most cases, VBA scripts will execute slower than compiled, managed code.
  • Users can have access to any of the libraries made available with the .NET Framework distribution on the server from within their user defined functions.
Detriments of Creating User Defined Functions in .NET languages:
  • Requires at least basic familiarity with C# or VB.NET to create the functions.
  • May require dual-maintenance if the Excel functions are not also coded and executed in Excel using a .NET compiled assembly (via XLL or automation add-ins).
How to submit User Defined Functions with your web page applications

Edit your web application and click on Add/Edit button in User-Defined Functions box.

Fill the areas in the pop up and click Save to add your UDF.

Below are the explanations for the UDF feature menu:

Version Name A unique identifier for the version name. This can be an internal flag for version control. Note: SpreadsheetWEB imposes a restriction on version name that requires this identifier to be unique, meaning that subsequent uploads of UDF functions cannot share a version name with any prior uploaded versions. If you utilize an existing version, then the Control Panel will clearly warn you to modify the version name.
Directives A semicolon-delimited list of all imports that the code will require. In C#, this is the equivalent of the using statements at the top of your class file (e.g. if you have using System; using System.Xml; at the top of your class, the valid entry for this field would be System; System.Xml).
Assemblies The names of the .NET framework assemblies. This is the equivalent of the references required by a project in order for your code to compile. For example, all .NET framework code will likely require a reference to mscorlib. If you also required a reference to the System, System.Net, and System.Linq namespaces, the valid entry would be mscorlib; System; System.Net; System.Core.
Namespace This string will define the namespace, so any valid namespace (e.g. no spaces or special characters) is appropriate entry. Using a unique name that signifies this particular segment of code will allow easier identification of any runtime errors that appear in the log.
Class Name This string will define the class name, so any valid class name (e.g. no spaces or special characters) is appropriate entry. Using a unique name that signifies this particular segment of code will allow easier identification of any runtime errors that appear in the log.
Code User Defined Functions are entered here.

Important Note: At compile-time, your code will be used to generate static classes. This means that any functions submitted in the Code section should be static functions (i.e. no access to instance members or non-static functions). Overloads and optional parameters are supported. Simply copy your code into this field.
Results Upon hitting the Save button, the system will attempt to compile your code. Any compilation errors will be displayed in the Results section, along with a compiler message and the offending line and character position.
Additional Notes
  • SpreadsheetWEB’s user defined functions support references to any .NET Framework assemblies (by including the name in the Assemblies section).
  • SpreadsheetWEB’s user defined functions support C# 6.0 syntax.
  • User defined functions can make use of overloads and optional parameters.
  • All submitted user defined functions must be static (compiler will warn the user if they are not). If the intention is to access them directly from the Excel workbook, then they must also be public.
    • For example, if cell A1 contains contents =DoSomething(A2, A3), then there must be a user defined function with a signature that looks something like:
      • public static object DoSomething(object x, object y)
      • Note: If A2 and A3 are numeric or string entry fields then you may use double or string types instead of object, respectively.
    • If DoSomething, in turn, calls function DoSomethingElse but the workbook does not ever call DoSomethingElse directly, then function DoSomethingElse may have a different scope (e.g. private, protected, etc.).

 

Here are some simple examples of VBA functions converted into valid C# functions that can be utilized with SpreadsheetWeb’s UDF support:

VBA Code
C# Code
Simple Rectangle Area Function

Function Area(x As Double, y As Double) As Double
   
    Area = x * y

End Function

Get File Size

Function GetFileSize(fileName As String) As Long
    
     GetFileSize = FileLen(fileName)

End Function

 

Get Name of Day

Function DayName(InputDate As Date) As String 
    
    Dim DayNumber As Integer
    
    DayNumber = Weekday(InputDate, vbSunday)
    
    Select Case DayNumber
        
        Case 1
            DayName = "Sunday"
        
        Case 2
            DayName = "Monday"
        
        Case 3
            DayName = "Tuesday"
        
        Case 4
            DayName = "Wednesday"
        
        Case 5
            DayName = "Thursday"
        
        Case 6
            DayName = "Friday"
        
        Case 7
            DayName = "Saturday"
    
    End Select
    
End Function
Simple Rectangle Area Function

public static double Area(double x, double y) {
   
    return (x * y);
}

Get File Size

public static long GetFileSize(string fileName) {
    
    return new System.IO.FileInfo(fileName).Length;
}

Get Name of Day

public static string DayName(string inputDate)

{
    
    DateTime outputDate;
    
    if (DateTime.TryParse(inputDate, outputDate))
    {
        
         return outputDate.DayOfWeek.ToString();
    
    }
    
    return "You entered an invalid date!";

}