Call A C# Console App From VBA

Share this

January 26, 2020

Call A C Console App From VBA


Introduction


VBA is a great introductory language to programming and has given tremendous power to Excel and other Office applications. However, since Microsoft has no intention to update/upgrade the language – Microsoft seems to promote JavaScript as a VBA alternative in the last few years – a lot of people are trying to find alternative ways to enhance its capabilities. One possible way to enhance VBA is to use it as a “caller.” Here, the word “caller” means that the VBA will be used to “call” functionality from other more powerful languages.

In this article, we will learn how to “call” a console app written in C#. What is the C# language? Well, in case you are wondering, according to Wikipedia, “C# is a general-purpose, multi-paradigm programming language encompassing strong typing, lexically scoped, imperative, declarative, functional, generic, object-oriented (class-based), and component-oriented programming disciplines.”

C# is a really powerful language and probably one of my favorite programming languages (see CodeEval articles for more information). However, here we are interested only in the “communication” part with VBA. Therefore, the sample C# program that you will see below is very simplistic. In upcoming tutorials, however, more realistic and meaningful examples will be shown. So, are you ready to learn how to call a C# application from Excel?

 


How to create a C# console application


Unlike VBA, which is already included in Excel and you can switch to its Integrated Development Environment (IDE) by pressing CTRL + F11, C# needs its IDE. For this tutorial, you can download the Visual Studio 2019 Community Edition, which you can download for free from Microsoft’s site. Note that the images below are from an older version of Visual Studio that I use (2015 Enterprise). The steps, however, will be identical.

After you download and install the Visual Studio 2019 Community Edition, open it. Then from the menu, select File → New → Project or press CTRL + SHIFT + N.

New Project

In the New Project form, click on the Visual C# templates, then on the Windows sub-menu, and select the Console Application option on the right side of the form. Finally, give a meaningful name to your project (here, I used the name GetShapeAreas).

New C Console Application

In the new project that will be created, paste the code below. As you will notice, the code calculates a rectangle area or a circle based on the input arguments. If there is only one input parameter, then the circle area is calculated, while if there are two, the rectangle area is computed. VBA will provide these input parameters (or arguments). As was mentioned before, nothing difficult in respect of functionality (that isn’t the purpose here)!

using System;

namespace GetShapeAreas
{
	class Program
	{
		static void Main(string[] args)
		{
			double result;
			try
			{
				switch (args.Length)
				{
					case 1:
						// Circle area.
						double radius = Convert.ToDouble(args[0]);
						result = Math.PI * Math.Pow(radius, 2) / 4;
						break;

					case 2:
						// Rectangle area.
						double width = Convert.ToDouble(args[0]);
						double height = Convert.ToDouble(args[1]);
						result = width * height;
						break;

					default:
						// Any other case (not handled).
						result = 0;
						break;
				}
			}
			catch (Exception)
			{
				// Error case (e.g. invalid casting).
				Console.WriteLine("-1");
				return;
			}
			// Write the result rounded in 3 decimals.
			Console.WriteLine(Math.Round(result, 3));
		}
	}
}
// End of the code. 

Then, switch your solution configuration to Release. Why do you want to do this? By default, Debug includes debugging information in the compiled files (allowing easy debugging), while Release is usually optimized for production/deployment. Therefore, it is the appropriate option when you have completed and tested your application.

Release Option

Finally, click on the Build menu and select the Build Solution option or press F6 on your keyboard.

Build Solution

If you open the Release folder that is created, you will see something like the image below. The GetShapeAreas.exe (the first file) is your application.

Release Folder Contents

Congratulations! You have successfully created and built your first C# application.

 


VBA code


While on the C# side, the code is relatively easy to write and understand, at least on this example, on the VBA side, on the other hand, the code is much more complicated. To make VBA communicate with the C# application, we have to create a pipe first. Then, we should execute the C# console application and force it to send the output and (any) error information to the pipe. Finally, we have to read the pipe contents until there is no output left.

It sounds difficult, isn’t it? Well, the ExecuteAndCapture function below performs exactly the steps described. It relies heavily on API functions, and it was written in a way that will work both in 32bit and 64bit Office versions.

ExecuteAndCapture function

Option Explicit

'Declaring the necessary API functions and types based on Excel version.
#If Win64 Then
    
    'For 64 bit Excel.
    'Creates an anonymous pipe, and returns handles to the read and write ends of the pipe.
    Public Declare PtrSafe Function CreatePipe Lib "kernel32" (phReadPipe As LongPtr, _
                                                                phWritePipe As LongPtr, _
                                                                lpPipeAttributes As Any, _
                                                                ByVal nSize As Long) As Long
    
    'Reads data from the specified file or input/output (I/O) device. Reads occur at the position specified by the file pointer if supported by the device.
    Public Declare PtrSafe Function ReadFile Lib "kernel32" (ByVal hFile As LongPtr, _
                                                             lpBuffer As Any, _
                                                             ByVal nNumberOfBytesToRead As Long, _
                                                             lpNumberOfBytesRead As Long, _
                                                             lpOverlapped As Any) As Long
    
    'Creates a new process and its primary thread. The new process runs in the security context of the calling process.
    Public Declare PtrSafe Function CreateProcess Lib "kernel32" Alias "CreateProcessA" (ByVal lpApplicationName As String, _
                                                                                         ByVal lpCommandLine As String, _
                                                                                         lpProcessAttributes As Any, _
                                                                                         lpThreadAttributes As Any, _
                                                                                         ByVal bInheritHandles As Long, _
                                                                                         ByVal dwCreationFlags As Long, _
                                                                                         lpEnvironment As Any, _
                                                                                         ByVal lpCurrentDriectory As String, _
                                                                                         lpStartupInfo As STARTUPINFO, _
                                                                                         lpProcessInformation As PROCESS_INFORMATION) As Long
    
    'Closes an open object handle.
    Public Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As LongPtr) As Long
    
    'Contains the security descriptor for an object and specifies whether the handle retrieved by specifying this structure is inheritable.
    Public Type SECURITY_ATTRIBUTES
        nLength                 As Long
        lpSecurityDescriptor    As LongPtr
        bInheritHandle          As Long
    End Type
    
    'Specifies the window station, desktop, standard handles, and appearance of the main window for a process at creation time.
    Public Type STARTUPINFO
        cb                  As Long
        lpReserved          As String
        lpDesktop           As String
        lpTitle             As String
        dwX                 As Long
        dwY                 As Long
        dwXSize             As Long
        dwYSize             As Long
        dwXCountChars       As Long
        dwYCountChars       As Long
        dwFillAttribute     As Long
        dwFlags             As Long
        wShowWindow         As Integer
        cbReserved2         As Integer
        lpReserved2         As LongPtr
        hStdInput           As LongPtr
        hStdOutput          As LongPtr
        hStdError           As LongPtr
    End Type
    
    'Contains information about a newly created process and its primary thread.
    Public Type PROCESS_INFORMATION
        hProcess        As LongPtr
        hThread         As LongPtr
        dwProcessId     As Long
        dwThreadId      As Long
    End Type
                                    
#Else

    'For 32 bit Excel.
    Public Declare Function CreatePipe Lib "kernel32" (phReadPipe As Long, _
                                                       phWritePipe As Long, _
                                                       lpPipeAttributes As Any, _
                                                       ByVal nSize As Long) As Long
                                                        
    Public Declare Function ReadFile Lib "kernel32" (ByVal hFile As Long, _
                                                     lpBuffer As Any, _
                                                     ByVal nNumberOfBytesToRead As Long, _
                                                     lpNumberOfBytesRead As Long, _
                                                     lpOverlapped As Any) As Long
                                                      
    Public Declare Function CreateProcess Lib "kernel32" Alias "CreateProcessA" (ByVal lpApplicationName As String, _
                                                                                 ByVal lpCommandLine As String, _
                                                                                 lpProcessAttributes As Any, _
                                                                                 lpThreadAttributes As Any, _
                                                                                 ByVal bInheritHandles As Long, _
                                                                                 ByVal dwCreationFlags As Long, _
                                                                                 lpEnvironment As Any, _
                                                                                 ByVal lpCurrentDriectory As String, _
                                                                                 lpStartupInfo As STARTUPINFO, _
                                                                                 lpProcessInformation As PROCESS_INFORMATION) As Long
                                                                                  
    Public Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
    
    'Types.
    Public Type SECURITY_ATTRIBUTES
        nLength                                As Long
        lpSecurityDescriptor                   As Long
        bInheritHandle                         As Long
    End Type

    Public Type STARTUPINFO
        cb                                     As Long
        lpReserved                             As String
        lpDesktop                              As String
        lpTitle                                As String
        dwX                                    As Long
        dwY                                    As Long
        dwXSize                                As Long
        dwYSize                                As Long
        dwXCountChars                          As Long
        dwYCountChars                          As Long
        dwFillAttribute                        As Long
        dwFlags                                As Long
        wShowWindow                            As Integer
        cbReserved2                            As Integer
        lpReserved2                            As Long
        hStdInput                              As Long
        hStdOutput                             As Long
        hStdError                              As Long
    End Type
    
    Public Type PROCESS_INFORMATION
        hProcess                               As Long
        hThread                                As Long
        dwProcessId                            As Long
        dwThreadId                             As Long
    End Type
    
#End If

'Contants.
Public Const STARTF_USESHOWWINDOW  As Long = &H1
Public Const STARTF_USESTDHANDLES  As Long = &H100
Public Const SW_HIDE               As Integer = 0
Public Const BUFSIZE               As Long = 1024 * 10

Public Function ExecuteAndCapture(ByVal CommandLine As String, Optional ByVal StartInFolder As String = vbNullString) As String

    '------------------------------------------------------------------------------------
    'Runs a console application (with a hidden window) and returns its output as string.
    'First it creates a pipe and executes the console application, telling it
    'to send the output and (any) error information to the pipe.
    'Then, it reads from the pipe until there is no output left to read.
        
    'Written By:    Christos Samaras
    'Date:          17/09/2017
    'Last Updated:  26/01/2020
    'E-mail:        [email protected]
    'Site:          https://www.myengineeringworld.net
    '------------------------------------------------------------------------------------

    'Declaring the necessary variables (different for 32 or 64 bit Excel).
    #If Win64 Then
        Dim hPipeRead       As LongPtr
        Dim hPipeWrite      As LongPtr
    #Else
        Dim hPipeRead       As Long
        Dim hPipeWrite      As Long
    #End If
    
    'Declaring the rest variables.
    Dim sa                  As SECURITY_ATTRIBUTES
    Dim si                  As STARTUPINFO
    Dim pi                  As PROCESS_INFORMATION
    Dim baOutput(BUFSIZE)   As Byte
    Dim sOutput             As String
    Dim sTemp               As String
    Dim lBytesRead          As Long
    
    'Set the security attributes.
    With sa
        .nLength = Len(sa)
        .bInheritHandle = 1
    End With
    
    'Create the pipe.
    If CreatePipe(hPipeRead, hPipeWrite, sa, 0) = 0 Then
        Exit Function
    End If
    
    'Set the startup information.
    With si
        .cb = Len(si)
        .dwFlags = STARTF_USESHOWWINDOW Or STARTF_USESTDHANDLES
        .wShowWindow = SW_HIDE
        .hStdOutput = hPipeWrite
        .hStdError = hPipeWrite
    End With
    
    'Create the process and run the console application.
    If CreateProcess(vbNullString, CommandLine, ByVal 0&, ByVal 0&, 1, 0&, ByVal 0&, StartInFolder, si, pi) Then
        
        Call CloseHandle(hPipeWrite)
        Call CloseHandle(pi.hThread)
        hPipeWrite = 0
        
        Do
            'Wait.
            DoEvents
            
            'If all the information is read from the pipe, then exit.
            If ReadFile(hPipeRead, baOutput(0), BUFSIZE, lBytesRead, ByVal 0&) = 0 Then
                Exit Do
            End If
            
            'Pass the inforrmation to a variable.
            sOutput = Left$(StrConv(baOutput(), vbUnicode), lBytesRead)
            If sOutput <> vbNullString Then sTemp = sTemp & sOutput
            
        Loop
        
        'Close the handle to the process.
        Call CloseHandle(pi.hProcess)
        
    End If
    
    'Close the handle to the pipe.
    Call CloseHandle(hPipeRead)
    Call CloseHandle(hPipeWrite)
    
    'Return the output.
    ExecuteAndCapture = sTemp
    
End Function
 
Public Function FileExists(FilePath As String) As Boolean
   
    '--------------------------------------------------
    'Checks if a file exists (using the Dir function).
    '--------------------------------------------------

    On Error Resume Next
    If Len(FilePath) > 0 Then
        If Not Dir(FilePath, vbDirectory) = vbNullString Then FileExists = True
    End If
    On Error GoTo 0
   
End Function 

Sample usage

OK, we have a function to read the C# console output, but how can we use it from Excel? The macros that follow, which are included in the sample workbook, show you how to call the ExecuteAndCapture to calculate the circle or the rectangle area.

Option Explicit

Public Sub GetCSharpCircleArea()

    '--------------------------------------------------
    'Calculates the circle area given its radius
    'using an external C# console application.
        
    'Written By:    Christos Samaras
    'Date:          26/01/2020
    'E-mail:        [email protected]
    'Site:          https://www.myengineeringworld.net
    '--------------------------------------------------
    
   'Declaring the necessary variables.
    Dim appPath As String
    Dim cmd     As String
    Dim result  As String
    
    'Get the C# app path (relative to the workbook).
    appPath = ThisWorkbook.Path & "\C# App\GetShapeAreas.exe"
    
    'Check if the C# app exist.
    If FileExists(appPath) = False Then
        MsgBox "The C# app " & vbNewLine & appPath & vbNewLine & "doesn't exist!", vbExclamation, "Error"
        Exit Sub
    End If
    
    'Create the command that will call the executable based on the input provided.
    cmd = Chr(34) & appPath & Chr(34) & " " & ThisWorkbook.Sheets("Examples").Range("C5")
    
    'Execute and read the (hidden console) results.
    result = ExecuteAndCapture(cmd)
    
    'Write the result to the sheet.
    ThisWorkbook.Sheets("Examples").Range("C7") = CDbl(result)
    
End Sub

Public Sub GetCSharpRectangleArea()

    '---------------------------------------------------------
    'Calculates the rectangle area given its width and height
    'using an external C# console application.
        
    'Written By:    Christos Samaras
    'Date:          26/01/2020
    'E-mail:        [email protected]
    'Site:          https://www.myengineeringworld.net
    '---------------------------------------------------------
        
    'Declaring the necessary variables.
    Dim appPath As String
    Dim cmd     As String
    Dim result  As String
    
    'Get the C# app path (relative to the workbook).
    appPath = ThisWorkbook.Path & "\C# App\GetShapeAreas.exe"
    
    'Check if the C# app exist.
    If FileExists(appPath) = False Then
        MsgBox "The C# app " & vbNewLine & appPath & vbNewLine & "doesn't exist!", vbExclamation, "Error"
        Exit Sub
    End If
    
    'Create the command that will call the executable based on the inputs provided.
    'Note the space between the parameters (" ").
    cmd = Chr(34) & appPath & Chr(34) & " " & ThisWorkbook.Sheets("Examples").Range("C13").Value _
                                      & " " & ThisWorkbook.Sheets("Examples").Range("C14").Value
    
    'Execute and read the (hidden console) results.
    result = ExecuteAndCapture(cmd)
    
    'Write the result to the sheet.
    ThisWorkbook.Sheets("Examples").Range("C16").Value = CDbl(result)

End Sub

Public Sub ClearSheet()
    
    '-----------------------
    'Clears the sheet data.
    '-----------------------
    
    With ThisWorkbook.Sheets("Examples")
       .Range("C5:C7").Value = vbNullString
       .Range("C13:C16").Value = vbNullString
       .Range("C5").Select
    End With
    
End Sub 

 


Downloads


Download

The zip file contains an executable that was created using the procedure described above and a sample workbook containing the VBA code for calling that executable. The workbook can be opened with Excel 2007 or newer. Please enable macros before using it.

 


Final thoughts


In this article, we learned how to create a simple C# application and call it directly from Excel using VBA. I know that the VBA code is relatively difficult, but, unfortunately, that is the price to pay if you need to enhance the functionality of VBA. And that is probably the biggest drawback as the years are passing by. Since the VBA language is not updated, the complexity required to “enhance” it will gradually increase. Probably, after a certain extent and effort, this might not worth it at all since there will probably be better solutions.

Finally, I would also like to remind you that this article was written for demonstration purposes only. Nobody will create an external C# application to calculate the area of a circle or a rectangle! However, in upcoming tutorials, we will see how to use this technique to perform more meaningful actions, at least on the C# side.

 


Read also


Face Detection In Excel

Page last updated: 10/03/2020

Page last modified: March 18, 2021

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

Christos E. Samaras

  • Hi, Jagandeep,

    I didn’t have time to go through your code, however, I quickly created a console application using this sample code:
    https://developers.google.com/drive/api/v3/quickstart/dotnet

    After providing the credentials, I was able to fetch the data from my Google Drive in a sample spreadsheet.

    So, it seems that it is possible.
    Maybe there is something in your code that causes the trouble.

    Best Regards,
    Christos

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    Add Content Block
    >