Fasten your seat belt, you drive.
First, create a new C # class library (or VB.NET .. any stones of your boat) and add a new UserControl WPF and create your interface:
<UserControl x:Class="ComVisibleUI.UserControl1" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:d="http://schemas.microsoft.com/expression/blend/2008" mc:Ignorable="d" d:DataContext="ViewModel1" d:DesignHeight="200" d:DesignWidth="300"> <Grid Background="White"> <Grid.RowDefinitions> <RowDefinition Height="*" /> <RowDefinition Height="32" /> </Grid.RowDefinitions> <TextBlock Text="actual content here" Foreground="DimGray" HorizontalAlignment="Center" VerticalAlignment="Center" /> <StackPanel Grid.Row="1" Orientation="Horizontal" HorizontalAlignment="Right" Margin="2"> <Button Width="128" Command="{Binding Command1}"> <TextBlock Text="Button1" /> </Button> <Button Width="128" Command="{Binding Command2}"> <TextBlock Text="Button2" /> </Button> </StackPanel> </Grid> </UserControl>
Create a project.
Then add a new form, add a WPF Interop ElementHost control, and you can add your WPF UserControl1 (no matter what you name it) as a hosted WPF control.
The WPF control uses data bindings to connect Command1 and Command2 (and everything else, really, is read in the Model-View-ViewModel template), so you need a class to implement the managed code part. If your logic is all VBA, this should be pretty subtle:
public class ViewModel1 { public ViewModel1() { _command1 = new DelegateCommand(ExecuteCommand1); _command2 = new DelegateCommand(ExecuteCommand2); } private readonly ICommand _command1; public ICommand Command1 { get { return _command1; } } public event EventHandler ExecutingCommand1; private void ExecuteCommand1(object parameter) { ExecuteHandler(ExecutingCommand1); } private readonly ICommand _command2; public ICommand Command2 { get { return _command2; } } public event EventHandler ExecutingCommand2; private void ExecuteCommand2(object parameter) { ExecuteHandler(ExecutingCommand2); } private void ExecuteHandler(EventHandler eventHandler) { var handler = eventHandler; if (handler != null) { handler.Invoke(this, EventArgs.Empty); } } }
A DelegateCommand is a very nice thing that is crowded with Stack Overflow, so feel free to look if you have questions:
public class DelegateCommand : ICommand { private readonly Action<object> _execute; private readonly Func<object, bool> _canExecute; public DelegateCommand(Action<object> execute, Func<object,bool> canExecute = null) { _execute = execute; _canExecute = canExecute; } public event EventHandler CanExecuteChanged; public bool CanExecute(object parameter) { return _canExecute == null ? true : _canExecute.Invoke(parameter); } public void Execute(object parameter) { _execute.Invoke(parameter); } }
The WinForms form will have to assign a WPF DataContext control - set a method for this:
public partial class Form1 : Form { public Form1() { InitializeComponent(); } public void SetDataContext(ViewModel1 viewModel) { hostedWPFControl.DataContext = viewModel; } }
Other than that, there should be no code here.
WPF likes the MVVM pattern; WinForms likes the MVP (lookup Model-View-Presenter). We’ll make the WPF part hosted in WinForms a presenter - an object that will use VBA code:
[ComVisible(true)] public interface IPresenter1 { void Show(); }
Yes, this is just an interface. Wait, we need something else:
[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)] [Guid("18F3B8A8-EC60-4BCE-970A-6C0ABA145705")] [ComVisible(true)] public interface IPresenterEvents { void ExecuteCommand1(object message); void ExecuteCommand2(); }
The IPresenterEvents interface is your event sink interface, which should implement VBA code, but I will get to it. First we need to implement the actual speaker:
public delegate void Command1Delegate(string message); public delegate void Command2Delegate(); [ComSourceInterfaces(typeof(IPresenterEvents))] [ClassInterface(ClassInterfaceType.None)] [ComVisible(true)] [Guid("FAF36F86-7CB3-4E0C-A016-D8C84F6B07D7")] public class Presenter1 : IPresenter1, IDisposable { private readonly Form _view; public Presenter1() { var view = new Form1(); var viewModel = new ViewModel1(); viewModel.ExecutingCommand1 += viewModel_ExecutingCommand1; viewModel.ExecutingCommand2 += viewModel_ExecutingCommand2; view.SetDataContext(viewModel); _view = view; } public event Command1Delegate ExecuteCommand1; private void viewModel_ExecutingCommand1(object sender, EventArgs e) { var handler = ExecuteCommand1; if (handler != null) { handler.Invoke("Hello from Command1!"); } } public event Command2Delegate ExecuteCommand2; private void viewModel_ExecutingCommand2(object sender, EventArgs e) { var handler = ExecuteCommand2; if (handler != null) { handler.Invoke(); } } public void Show() { _view.ShowDialog(); } public void Dispose() { _view.Dispose(); } }
Now go to the project properties and check the box "Registration for COM-interaction", then create a project; on the [Debugging] tab, select the "Run external program" action and find the EXCEL.EXE executable on your computer: when you press F5, Visual Studio will start Excel with the debugger attached, and then you can open VBE (Alt + F11), add a link to the library .tlb (the type of library) you just created (you will find it in the .net project directory, under \bin\debug\theprojectname.tlb , provided it is built for debugging), and that should do it.
There are several problems here, and I will come back later:
- The
Dispose() method is not displayed and will not be explicitly or implicitly called at any point that is ... dirty. - While everything seems to work from the point of view of the C # debugger, I could not get the VBA darn handlers to work. This is probably a big problem if you intend to implement the logic in VBA, and not just raise the user interface. OTOH, you have access to the .net code, it can also implement the presenter logic in the leading one, in C # / VB.NET, and then you will not need to process these event handlers.
Anyway, I added this code to ThisWorkbook :
Option Explicit Private WithEvents view As ComVisibleUI.Presenter1 Public Sub DoSomething() Set view = New ComVisibleUI.Presenter1 view.Show End Sub Private Sub view_ExecuteCommand1(ByVal message As Variant) MsgBox message End Sub Private Sub view_ExecuteCommand2() MsgBox "Hello from WPF!" End Sub
And when I start ThisWorkbook.DoSomething from the direct window (Ctrl + G), I get the following:
In theory (at least according to MSDN ), all you have to do. As I said, these event handlers are not called for some reason, but hey, you get your shiny buttons! ... and all the power of WPF to develop your user interface now :)