Category Archives: MS Office

Multithreading VBA using C#.NET

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.50 out of 5)
Loading...

Multithreading in VBA in Excel is possible! No wait… that can’t be true right? Almost everyone knows that VBA runs in a single thread. So am I kidding? Not quite! Although this may seems like a workaround there is actually a way to multithread your VBA algorithms. There is only 1 condition… you must be willing to rewrite it to .NET :). Yeah, now you know the catch. But either way this is not as hard as it seems and can prove really useful. You are probably also curious how efficient the native VBA code is vs. called C# dll methods. Below you will find an answer to both questions – how to introduce multithreading to your VBA code and how efficient will multithreaded C# methods be against single threaded VBA algorithms.

Multithreading VBA: Sorting an array of random values using Quicksort

Quicksort is one of the most popular sorting algorithms. It uses the “Divide and Conquer” strategy in order to sort a array of values, partitioning the array of value for sorting and applying the QuickSort algoirhtm to each partition recursively. Quicksort is therefore a natural and simple example for multithreading.

Below you can find a simple example of executing a native VBA QuickSort procedure and both a C# QuickSort sequential and parallel method.

Executing C# QuickSort in VBA

Declare Function CreateTestClass Lib "C:\[PATH TO DLL]\TestLib.dll" () As Object
Sub TestQuickSort()
  '---Variables
  Dim i As Long, ar() As Long, testClass As Object, StartTime as Date, EndTime As Date, stp As Long
  Set testClass = CreateTestClass() 'Load DLL for the first time to reduce 1st execution overhead

  ReDim ar(0 To 100000)
  '---VBA sort---
  Call Randomize(100)
  Call RandArray(ar)
  StartTime = Timer
  Call QuickSort(ar)
  EndTime = Timer
  qTime = Format((EndTime - StartTime), "0.00")
  
  '---C# sequential sort---
  Call Randomize(100)
  Call RandArray(ar)
  StartTime = Timer
  Set testClass = CreateTestClass()
  Call testClass.QuickSortSequential(ar)
  EndTime = Timer
  qTimeC = Format((EndTime - StartTime), "0.00")
  
  
  '---C# parallel sort---
  Call Randomize(100)
  Call RandArray(ar)
  StartTime = Timer
  Set testClass = CreateTestClass()
  Call testClass.QuickSortParallel(ar)
  EndTime = Timer
  pqTimeC = Format((EndTime - StartTime), "0.00")

  '---Print---
  Call MsgBox("VBA time: " & qTime & ", C# sequential time: " & qTimeC & ", C# parallel time: " & pqTimeC)
End Sub

Simple right? If you read my post on this is probably nothing new to you. However, as you can see the only setback is really the requirement to reference the C# dll. You can of course add it to your libraries and reference it in other VBA projects using “Tools->References”.

How are the C# methods implemented?

C# TestClass implementation (Unmanaged Exports)

using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace TestLib
{
    [ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
    public class TestClass
    {
        
        [return: MarshalAs(UnmanagedType.SafeArray)]
        public int[] QuickSortParallel([MarshalAs(UnmanagedType.SafeArray)]ref int[] ar)
        {
            ParallelSort.QuicksortParallel<int>(ar);
            return ar;
        }

        [return: MarshalAs(UnmanagedType.SafeArray)]
        public int[] QuickSortSequential([MarshalAs(UnmanagedType.SafeArray)]ref int[] ar)
        {
            ParallelSort.QuicksortSequential<int>(ar);
            return ar;
        }
    }

    static class UnmanagedExports
    {
        [DllExport]
        [return: MarshalAs(UnmanagedType.IDispatch)]
        static Object CreateTestClass()
        {
            return new TestClass();
        }
    }
}

To learn how to build C# dlls which can be referenced from VBA projects see my blog post here: EXCEL: Using C# in Excel VBA

Below you can find also the popular C# QuickSort implementation (from Google Summer of code 2008):

C# QuickSort implementation

public class ParallelSort 
    { 
        #region Public Static Methods 

        public static void QuicksortSequential<T>(T [] arr) where T : IComparable<T> 
        { 
            QuicksortSequential(arr, 0, arr.Length - 1); 
        } 

        public static void QuicksortParallel<T>(T[] arr) where T : IComparable<T> 
        { 
            QuicksortParallel(arr, 0, arr.Length - 1); 
        } 

        #endregion 

        #region Private Static Methods 

        private static void QuicksortSequential<T>(T[] arr, int left, int right)  
            where T : IComparable<T> 
        { 
            if (right > left) 
            { 
                int pivot = Partition(arr, left, right); 
                QuicksortSequential(arr, left, pivot - 1); 
                QuicksortSequential(arr, pivot + 1, right); 
            } 
        } 

        private static void QuicksortParallel<T>(T[] arr, int left, int right)  
            where T : IComparable<T> 
        { 
            const int SEQUENTIAL_THRESHOLD = 2048; 
            if (right > left) 
            { 
                if (right - left < SEQUENTIAL_THRESHOLD) 
                { 
                    QuicksortSequential(arr, left, right); 
                } 
                else 
                { 
                    int pivot = Partition(arr, left, right); 
                    Parallel.Invoke(new Action[] { delegate {QuicksortParallel(arr, left, pivot - 1);}, 
                                                   delegate {QuicksortParallel(arr, pivot + 1, right);} 
                    }); 
                } 
            } 
        } 

        private static void Swap<T>(T[] arr, int i, int j) 
        { 
            T tmp = arr[i]; 
            arr[i] = arr[j]; 
            arr[j] = tmp; 
        } 

        private static int Partition<T>(T[] arr, int low, int high)  
            where T : IComparable<T> 
        { 
            int pivotPos = (high + low) / 2; 
            T pivot = arr[pivotPos]; 
            Swap(arr, low, pivotPos); 

            int left = low; 
            for (int i = low + 1; i <= high; i++) 
            { 
                if (arr[i].CompareTo(pivot) < 0) 
                { 
                    left++; 
                    Swap(arr, i, left); 
                } 
            } 

            Swap(arr, low, left); 
            return left; 
        } 

        #endregion 
    }

As you will notice the above parallel QuickSort implementation utilizes the C# Parallel.Invoke method which allows you to easily leverage multithreading in C# without the hassle of having to manage thread joining etc. The Parallel C# class facilitates the following methods which make multithreading fairly easy:

  • Parallel.For – for iterating over integers
  • Parallel.ForEach – for iterating over any array of objects
  • Parallel.Invoke – basically invokes any number of actions (threads)

Using the For and ForEach linq methods are very straightforward making it very easy to mulithread exisiting singlethreaded algorithms. See a simple Parallel for example for building a multiplication table below:

Parallel.For(0, maxJ, i =>
        {
            for (int j = 0; j < maxJ; j++)
            {
                result[i, j] = i*j;
            }
        }); 

If you would like to see the VBA implementation of QuickSort see below. This implementation was based on this algorithm: Link

VBA QuickSort implementation

Private Sub QuickSort(ByRef values As Variant, Optional ByVal Left As Long, Optional ByVal Right As Long)
  Dim i As Long
  Dim j As Long
  Dim K As Long
  Dim Item1 As Variant
  Dim Item2 As Variant

  On Error GoTo Catch
  If IsMissing(Left) Or Left = 0 Then Left = LBound(values)
  If IsMissing(Right) Or Right = 0 Then Right = UBound(values)
  i = Left
  j = Right

  Item1 = values((Left + Right)  2)
  Do While i < j
    Do While values(i) < Item1 And i < Right
      i = i + 1
    Loop
    Do While values(j) > Item1 And j > Left
      j = j - 1
    Loop
    If i < j Then
      Call Swap(values, i, j)
    End If
    If i <= j Then
      i = i + 1
      j = j - 1
    End If
  Loop
  If j > Left Then Call QuickSort(values, Left, j)
  If i < Right Then Call QuickSort(values, i, Right)
    Exit Sub
Catch:
  MsgBox Err.Description, vbCritical
End Sub
Private Sub Swap(ByRef values As Variant, ByVal i As Long, ByVal j As Long)
  Dim Temp1 As Double
  Dim Temp2 As Double
  Temp1 = values(i)
  values(i) = values(j)
  values(j) = Temp1
End Sub

QuickSort Performance: Native VBA vs. C# sequential vs. C# multithreading

Now let’s analyze the performance of the various algorithms. The benefits of multithreading will be clearly visible when we’ll compare arrays of several sizes and sort them using the various algorithms:

Disclaimer: The following performance metrics are not precise as they simply measure the time that elapsed during code execution and do not reflect the actual processor time. What is more the QuickSort implementations are also slightly different which might also somewhat influence performance. The statistics can nevertheless be used to show the relative performance between VBA and C# (referenced from within VBA projects).

QuickSort: Native VBA vs. C# sequential vs. C# multithreading
QuickSort: Native VBA vs. C# sequential vs. C# multithreading

While it takes almost 15 seconds to sort 2 mln elements with the VBA singlethreaded QuickSort, the C# singlethreaded algorithm requires only slightly above 1 second and the multithreaded algorithm required approx. half a second! For me it was a real surprise that even without multithreading the C# algorithm was sooo much faster than the native VBA algorithm (over 10-12x). In case of the multithreaded algorithm there are of course several performance parameters to consider like the “SEQUENTIAL_THRESHOLD” parameter.

Conclusions: Multithreading in VBA possible, but not in native VBA

As you can see multithreading in VBA is possible if you export your VBA algorithms to C# and reference them from inside your VBA projects. As shown above you can expect significantly improved performance even in singlethreaded algorithms and leverage additional performance boosts thanks to multithreading!. I know that this may seem like a little unsatisfactory – as it’s not actually native VBA multithreading. On the other hand Excel is not a tool designed and appropriate for implementing processor intensive algorithms. What is more, even if VBA multithreading were possible, I hope you noticed that either way even the singlethreaded imported C# method was still much faster (10-12x) than the one in VBA.

You may rarely experience such situations in which your VBA algorithms will take more than a couple of seconds to execute. However, for those of you working in analytics – hopefully this will be a useful tool.

Let me know what you think!

Be sure to checkout a comparison of all available approaches to VBA multithreading:
EXCEL: Multithreading VBA – VBA vs. VBscript vs. C#.NET.

Here you can download the Excel xlsm file and the C# Project:

Using C# in Excel VBA

1 Star2 Stars3 Stars4 Stars5 Stars (5 votes, average: 4.20 out of 5)
Loading...

Today’s topic concerns using C# from within VBA code(Excel C#). Ever had a C#.NET library you wanted to use in Excel VBA (Excel C#)? Well, as a matter of fact it is possible using the Visual Studio Nuget Unmanaged Exports package.

Now why on earth would you want to use C# in Excel by using dll libaries? Well there are a bunch of reasons to consider. Top of mind:

  • Utilizing many available existing C#.NET libraries in Excel VBA
  • Multi-threading – Excel VBA is limited to 1 thread whereas C#.NET can leverage multithreading
  • Use drivers and connections not available Excel VBA (connect to NoSQL databases and custom systems)
  • Protect your code and algorithms compiling them to dll libraries – VBA project protection is not a safe method of protecting your code!
  • Ease of creating and managing complicated solutions with the support of VS tools (debugging, performance analysis, testing)
  • Portability – make sure you algorithms runs exactly the same in VBA, your C#.NET project and your ASP.NET application

Before you follow this tutorial be sure to check-out my VBA Compiler Add-In for Excel which allows you to easily export and compile your VBA Functions to VB.NET DLL!

This tutorial will show you how to create a COM-visible DLL in C#.NET in just 5 easy steps:

Create New Visual Studio Project

  • Create a new VS project
  • Select Class library from the C# group
  • Go to Project Properties and make your library to be COM-visible
New Class Library project
New Class Library project

Show Package Manager Console

To install the Unmanaged Exports template you will need to use the Nuget Package Manager Console. Enable it as shown below:

Show Package Manager Console
Show Package Manager Console

Install the Unmanaged Exports template to the project

  • Open the Package Manager Console
  • Paste Install-Package UnmanagedExports into the console and hit enter
Install the UnmanagedExports template
Install the UnmanagedExports template

Create a class with some example code

  • Create a sample “Sample.cs” class
  • Insert the code below to the “Sample.cs” file
  • Build the Project
using RGiesecke.DllExport;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;

namespace TestLib
{
    [ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
    public class TestClass
    {
        public string Text
        {
            [return: MarshalAs(UnmanagedType.BStr)]
            get;
            [param: MarshalAs(UnmanagedType.BStr)]
            set;
        }

        public int Numbers
        {
            [return: MarshalAs(UnmanagedType.SysInt)]
            get;
            [param: MarshalAs(UnmanagedType.SysInt)]
            set;
        }

        [return: MarshalAs(UnmanagedType.SysInt)]
        public int GetRandomNumber()
        {
            Random x = new Random();
            return x.Next(100);
        }
    }

    static class UnmanagedExports
    {
        [DllExport]
        [return: MarshalAs(UnmanagedType.IDispatch)]
        static Object CreateTestClass()
        {
            return new TestClass();
        }
    }
}

Use the C# class in Excel VBA (Excel C#)

Just insert the following code into your Excel VBA project and you are good!

Declare Function CreateTestClass Lib "C:\[Path to the DLL]\TestLib.dll" () As Object

Sub TestTheTestClass()
  Dim testClass As Object
  Set testClass = CreateTestClass()'Creates an instance of TestClass
  Debug.Print testClass.GetRandomNumber'Executes the method
  testClass.text = "Some text"'Set the value of the Text property
  testClass.Number = 23'Set the value of the Number property
  Debug.Print testClass.text
  Debug.Print testClass.Number
End Sub

Notice that C# types have to be converted to VBA types using the MarshalAs tag. Read more about the different Umanaged Types in C# here: Link.
Below is a list of some typical types you might want to leverage:

C# Type Unmanaged Type VBA Type Comment
String UnmanagedType.BStr String
int UnmanagedType.SysInt Integer
bool UnmanagedType.VariantBool Boolean
Class UnmanagedType.IDispatch Object Return a class object to VBA
Array e.g. int[] ar UnmanagedType.SafeArray Array e.g. Dim ar() As Long If you are using an array as an argument be sure to use the C# “ref” get by reference option e.g. ref int[] ar

Excel C# troubleshooting

In case you are experiencing issues with interfacing with the DLL please check the following:

  • Did you mark the DLL to be COM-visible in your Project Properties in Visual Studio
  • Have you switched the Platform Target in the class library properties to “x86”
  • Is the filename of the DLL in your VBA code correct
  • Are there any typos in e.g. the class name, methods etc.

Send email from Excel using VBA

1 Star2 Stars3 Stars4 Stars5 Stars (6 votes, average: 4.67 out of 5)
Loading...

Tired of writing tons of similar emails to your recipients? Preparing a personalized newsletter in Excel? Sending an email from Excel is a useful feature in case you need email notifications or you are building a mailing list in Excel. Below find a quickly drafted method for sending emails from your Outlook application. The prerequisite is to have Outlook Exchange installed and configured to some default email account. The procedure will then leverage this account to send emails. Similarly this can be use to send emails from VBscript – just remove any variable data type declarations and you should be good to go (e.g. “Dim outApp” instead of “Dim outApp as Object”).

Send email from Excel

The code below will utilize your Outlook application to created an Mail item, and send the email.

Sub SendEmailFromOutlook(body As String, subject As String, toEmails As String, ccEmails As String, bccEmails As String)
    Dim outApp As Object
    Dim outMail As Object
    Set outApp = CreateObject("Outlook.Application")
    Set outMail = outApp.CreateItem(0)

    With outMail
        .to = toEmails
        .CC = ccEmails
        .BCC = bccEmails
        .subject = subject
        .HTMLBody = body
        .Send 'Send the email
    End With

    Set outMail = Nothing
    Set outApp = Nothing
End Sub

Tweak: Save email in Outlook from Excel

What if you do not want to send the email, but instead just send a draft to finish or send later? Replace Send with Save in the above code snippet as show below:

 With outMail
        .to = toEmails
        .CC = ccEmails
        .BCC = bccEmails
        .subject = subject
        .HTMLBody = body
        .Save 'Instead of send
    End With

Tweak: Display draft instead of sending an email

An additional modification of the procedure could be to simply display the email draft instead of sending/saving it. Replace Send with Display in the above code snippet as show below. You will be presented with the draft email message which you can tinker with and send by yourself.

 With outMail
        .to = toEmails
        .CC = ccEmails
        .BCC = bccEmails
        .subject = subject
        .HTMLBody = body
        .Display 'Instead of send/save
    End With

Adding attachments to email from Excel

Sometimes text/html is not the only content you want to share with your recipients. What about adding some attachments to the email? Just add the code snippet below to the original code snippet above:

Dim filePath as String
filePath = ("C:\someFile.txt")
outMail.Attachments.Add filePath 'Send some text file

'Attach the current Workbook
outMail.Attachments.Add ActiveWorkbook.FullName

Summary

Sending emails from Excel can save you a lot of time. This is a very useful example of Excel automation. I commonly see sending emails from Excel being used in these scenarios:

  • Managing email distribution lists from Excel
  • Sending notifications emails from Excel
  • Creating draft emails

Additional tip: send Excel as PDF

As most of you know Excel is great for preparing reports. Charts, numbers etc. You can of course create a text message with this data. However sometimes that is simply not as convenient as sending an image/pdf and distributing these PDF reports to recipients. Let’s extend our snippet further to send a pdf Excel report with just the output instead of your entire workbook.

Dim ws as Worksheet
Set ws = Activesheet
ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

More examples

Above I tried to focus on the most common examples of sending emails via Excel. There are definitely more scenarios. I think Ron de Bruin did a great job of listing most of them. Check his website here.

Creating a dynamic named range in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

When making complex Excel solutions is often the case that you will need to manage many different lists of certain values e.g. for dropdowns, formulas etc. It is a real pain to manage lists that change very often. The clean solution is to define a dynamic named range which will adapt to the list of parameters within a certain column.

Defining a dynamic named range

Create a list of items

Some Excel Range list
Some Excel Range list
Create your list of items. It is often best to keep your lists on a separate Worksheet and each column topped with a header explaining what each list contains. Watch out for BLANK as they will not be supported by the dynamic named range.

Open the Excel Name Manager

Go to the FORMULAS ribbon and open the Name Manager within the Defined Names group.

Create a New Named Range

Hit the New.. button to create a new Named Range. Next provide the name for your Excel dynamic named range.

Create a new Excel Named Range
Create a new Excel Named Range

Remember that Excel range names cannot contain spaces and need to start with a letter character

Provide the dynamic named range formula

Provide the formula for your dynamic named range. Assuming your worksheet name is NameOfWorksheet the formula should look like this:

=OFFSET(NameOfWorksheet!$A$1;1;0;COUNTA(NameOfWorksheet!$A:$A)-1)

See an example below:

Dynamic Named Range
Dynamic Named Range

How does the dynamic named range formula work?

How does it work? Well the OFFSET function takes 5 arguments:

  1. the reference cell
  2. the offset number of rows to move
  3. the offset number of columns to move
  4. how many rows of data to return (optional)
  5. how many columns of data to return (optional)

See now that what our dynamic named range formula does is:

  • move 1 cell down from the “List of names” cell to the first cell of the list
  • return a range of unempty rows – for as many rows as there are un-empty cells in the entire column minus 1 (minus the first cell of the column)

Therefore to sum up, the formula returns a range of all unempty cells within the given column offset by 1 row (fro the header). The definition of the Named Range is a formula hence will recalculate automatically.

The Named Range formula recalculates based on the calculation settings. If you turn of Automatic Calculation be aware that the Named Range will need to be recalculated manual or else it might show an outdated range if you add/remove rows

Making an Excel dropdown with a dynamic populated list

Let’s now make a common use the list of names which we defined to create an Excel dropdown. This way the dropdown will only be populated with the items defined in the dynamic named range list.

Dynamic Excel Dropdown
Dynamic Excel Dropdown

Good practice to using dynamic named ranges

My personal experience is that lists should be in hidden worksheets (sometimes good to make it even “very” hidden) in which each column will represent a certain list of values which can be reference by a certain dynamic named range. This will make it easy for you to manage your lists and not worry about the number of their items increasing or decreasing.

Export Excel to HTML – convert tables to HTML

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

I received an interesting question today – on how to easily publish an Excel file to a web page. Although there are a ton of ways to approach this problem (ranging from Excel Services in SharePoint to Excel Interop or ClosedXML) let us say we want to restrict to using only Excel and VBA. Printing Excel to HTML is a very useful feature if you want to publish your data/Workbook online.

The concept itself is very simple as HTML files are text files and therefore the problem is only to structure the data correctly in VBA before saving it to a HTML file.

I wanted to explore today 2 options:

  • Generating a HTML file via VBA
  • Generating a HTML file via the Publish feature in Excel

Both options are fairly useful ones – with the first one offering more flexibility and the second one being much easier to use.

Generating HTML via VBA

So lets start with the simple example of generating an Excel file from scratch just with VBA.

We have the following Excel table (ranging from A1 to C3):

Excel table
Excel table

The Code

Sub Test()
    RangeToHtml Range("A1:C3"), "test.html"
End Sub

Sub RangeToHtml(rng As Range, fileName As String)
    Dim resBeg As String
    resBeg = "<html><head></head><body><table>"
    resEnd = "</table></body></html>"
    For i = 1 To rng.Rows.Count
        '---Rows---
        resBeg = resBeg & "<tr>"
        For j = 1 To rng.Columns.Count
            '---Columns---
            resBeg = resBeg & "<td>"
            resBeg = resBeg & rng.Cells(i, j).Value
            resBeg = resBeg & "</td>"
        Next j
        resBeg = resBeg & "</tr>"
    Next i
    Call SaveStringToFile(resBeg & resEnd, fileName)
End Sub

Sub SaveStringToFile(str As String, fileName As String)
    Open fileName For Output As #1
    Print #1, str
    Close #1
End Sub

Excel to HTML: The result

Lets see the result (actual HTML posted to this page):

Col1 Col2 Col3
1 2 3
4 5 6

Nothing extraordinary – just a very simple table without any formatting.
What the code does is traverse through the Excel Range replacing rows with the

tag and columns (or rather cells) with the

tag inserting the cell’s contents within. A very simple example.

Excel Publish to HTML feaure

The Publish to HTML feature is a neat capability that allows you to export your entire Workbook as a HTML web page through which you can easily navigate. You can easily Publish your Excel Workbook from VBA or directly from Excel.

To publish the Excel file go to Save As and select Publish to configure the publish options:

Publish to HTML Excel feature
Publish to HTML Excel feature

Alternatively you can use the VBA code below to achieve the same:

 With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "PublishToHtml.htm", ";Sheet1", "$A$1:$C$4", _
        xlHtmlStatic, "PublishToHtml", "")
        .Publish (True)
 End With

Easy as that! The additional advantage is that the Publish to Excel feature will keep some of your formatting settings e.g. bold, italic fonts etc. Some, however, usually will be omitted e.g. borders.

Conclusions

Without resorting to external solutions there are least 2 easy options of generating html files from Excel. Personally I would prefer to have control over my HTML file and use VBA possibly adding my own css styles and formatting.