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.
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).
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.
Finally, click on the Build menu and select the Build Solution option or press F6 on your keyboard.
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.
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://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://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://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
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
Page last updated: 10/03/2020
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
Hello Christos
I have a problem running Goggle Api based application through VBA. I have posted the problem on stack overflow. Below is the link.
https://stackoverflow.com/questions/63466286/problem-in-running-c-sharp-google-api-based-console-application-from-vba
Can you help me out?