After making different “HelloWorldGUI via VBA\/C#” style minimal working solution examples, I picked Named Pipes for now because it was the easiest solution I found (outside of COM exposed DLL solution below for non-gui applications. .NET 3.5+ required<\/p>\n
Possible solutions included:<\/p>\n
- \n
- Run the GUI from the VBA – No, I want a standalone GUI that can be attached to<\/li>\n
- Use AutoItX3.dll to literally click the buttons and type using virtual keyboard and mouse – No, doable but overly difficult and complicated<\/li>\n
- Use .NET’s UI automation to control UI elements found through Windows SDK inspect tool – Pretty good, but since we have the GUI source code, there should be a more direct solution\\<\/li>\n
- Use UDP port on local machine to link programs<\/li>\n
- Communicate through set memory location on computer – too low level. Something should already exist that does this safely without problems<\/li>\n
- Use WCF (Windows Communication Foundation) Named Pipes to communicate between VBA\/C# and WPF (Windows Presentation Foundation) – Looks the cleanest and easiest way<\/strong><\/li>\n<\/ul>\n
Step-By-Step Guided Example<\/h1>\n
\n- \n
- Step-By-Step Guided Example<\/li>\n
- 1. Create C# Library Class<\/li>\n
- 2. Make Library Class Visible as COM Library<\/li>\n
- 3. Call Public Functions from Excel VBA<\/li>\n
- 4. Deployment\n
- \n
- Unblock the DLL (Windows Security)<\/li>\n
- Registering the Assembly<\/li>\n
- \u00a0Related articles<\/li>\n<\/ul>\n<\/li>\n
- References<\/li>\n<\/ul>\n
1. Create C# Library Class<\/h1>\n
Put this code in a new C# file in a new Visual Studio project. It is bare bones with one simple function that will later be called from Excel vba.<\/p>\n
I named the project and solution both ZZTEST_EXCEL_LIB for easy finding in the excel references list.<\/p>\n
\nSample Code<\/b>\u00a0<\/span>Collapse source<\/span><\/span><\/div>\n\n\n\n\n\n
\n \n 1<\/div>\n2<\/div>\n3<\/div>\n4<\/div>\n5<\/div>\n6<\/div>\n7<\/div>\n8<\/div>\n9<\/div>\n10<\/div>\n11<\/div>\n12<\/div>\n13<\/div>\n<\/td>\n\n \nusing<\/code>System;<\/code><\/div>\nusing<\/code>System.Collections.Generic;<\/code><\/div>\nusing<\/code>System.Text;<\/code><\/div>\nnamespace<\/code>ZZVISIBLE_FROM_EXCEL_LIBRARY<\/code><\/div>\n{<\/code><\/div>\n\u00a0\u00a0\u00a0\u00a0<\/code>public<\/code>class<\/code>ZZVISIBLE_FROM_EXCEL_CLASS<\/code><\/div>\n\u00a0\u00a0\u00a0\u00a0<\/code>{<\/code><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>public<\/code>Double DotNetMethod_SQR(Double input) <\/code>\/\/Return the square of the input<\/code><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>{<\/code><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>return<\/code>input * input;<\/code><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>}<\/code><\/div>\n\u00a0\u00a0\u00a0\u00a0<\/code>}<\/code><\/div>\n}<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n2. Make Library Class Visible as COM Library<\/h1>\n
In the project properties, change the output type to Class Library<\/p>\n
In Assembly Information, check “make assembly COM-Visible”<\/p>\n
![](\"http:\/\/www.maxwellmckinnon.com\/blog\/wp-content\/uploads\/2016\/10\/Untitled_Clipping_100616_011732_PM.jpg\")
<\/p>\nIn the project properties under the build tab, check “Register for COM interop”<\/p>\n
![](\"http:\/\/www.maxwellmckinnon.com\/blog\/wp-content\/uploads\/2016\/10\/Untitled_Clipping_100616_011950_PM.jpg\")
<\/p>\n3. Call Public Functions from Excel VBA<\/h1>\n
In Excel, enable the developer tab and launch visual basic (google this step if unclear)<\/p>\n
From visual basic, select tools and then references. Select the desired library, in this case “ZZTEST_EXCEL_LIB”<\/p>\n
![](\"http:\/\/www.maxwellmckinnon.com\/blog\/wp-content\/uploads\/2016\/10\/Untitled_Clipping_100616_012026_PM.jpg\")
<\/p>\n\nRun the VBA code<\/span><\/span><\/div>\nCollapse source<\/span><\/span><\/div>\n\n\n\n
![](\"http:\/\/www.maxwellmckinnon.com\/blog\/wp-content\/uploads\/2016\/10\/Untitled_Clipping_100616_012114_PM.jpg\")
<\/p>\n![](\"http:\/\/www.maxwellmckinnon.com\/blog\/wp-content\/uploads\/2016\/10\/Untitled_Clipping_100616_012140_PM.jpg\")
<\/p>\n![](\"http:\/\/www.maxwellmckinnon.com\/blog\/wp-content\/uploads\/2016\/10\/Untitled_Clipping_100616_012210_PM.jpg\")
<\/p>\n