VBA Windows 7 样式按钮 [英] VBA Windows 7 style buttons

问题描述

我很确定这个问题已经在网络上被问了很多，我已经在几个论坛上阅读了很多问题及其答案"，但我从来没有看到一个明确的答案，所以我想要知道:

I'm pretty sure this question has been asked a lot around the web and I've read a lot of the questions and their "answers" on several forums but I've never seen a clear answer so I'd like to know:

可以，使用 Windows 7 风格的按钮

Is is possible, to use Windows 7 style buttons

在 Excel VBA 中还是我必须使用这些看起来像是来自的灰色东西

in Excel VBA or do I have to use these grey things looking like they come from

?

我不想使用图像，我的意思是导入这些ActiveX 控件"，我想这就是它们的名字.

I dont want to use images, I mean importing these "ActiveX Controls", I think thats their name.

推荐答案

系好安全带，你要兜风了.

首先，创建一个新的 C#(或 VB.NET.. 随便你的船)类库，并添加一个新的 WPF UserControl，然后设计你的 UI:

First, create a new C# (or VB.NET.. whatever rocks your boat) class library, and add a new WPF UserControl, and design your UI:

<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>

构建项目.

然后，添加一个新的表单，停靠一个 WPF Interop ElementHost 控件，您应该能够将您的 WPF UserControl1(无论您叫什么)添加为托管WPF 控件.

Then, add a new Form, dock a WPF Interop ElementHost control, and you should be able to add your WPF UserControl1 (whatever you called it) as the hosted WPF control.

WPF 控件使用数据绑定来连接 Command1 和 Command2(以及其他所有内容，真的 - 阅读 Model-View-ViewModel 模式)，因此您需要一个类来实现 托管代码 部分.如果您的逻辑全是 VBA，那么这应该很苗条:

The WPF control uses data bindings to hook up Command1 and Command2 (and everything else, really - read up on the Model-View-ViewModel pattern), so you'll need a class to implement the managed code part. If your logic is all VBA then this should be pretty slim:

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);
        }
    }
}

DelegateCommand 是一个非常棒的小东西，遍布 Stack Overflow，所以如果您有任何问题，请不要犹豫搜索:

A DelegateCommand is a very nice little thing that's all over Stack Overflow, so don't hesitate to search if you have any 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);
    }
}

WinForms 表单需要分配 WPF 控件的 DataContext - 公开一个方法来做到这一点:

The WinForms form will need to assign the WPF control's DataContext - expose a method to do that:

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    public void SetDataContext(ViewModel1 viewModel)
    {
        hostedWPFControl.DataContext = viewModel;
    }
}

除此之外，这里不应该有任何代码.

WPF 喜欢 MVVM 模式，WinForms 喜欢 MVP(查找 Model-View-Presenter).WPF 部分托管在 WinForms 中，我们将创建一个 presenter - 这是 VBA 代码将使用的对象:

WPF likes the MVVM pattern, WinForms likes MVP (lookup Model-View-Presenter). The WPF part being hosted in WinForms, we'll make a presenter - that's the object the VBA code will use:

[ComVisible(true)]
public interface IPresenter1
{
    void Show();
}

是的，这只是一个界面.等等，我们需要另一个:

Yes, that's just an interface. Hold on, we need another:

[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
[Guid("18F3B8A8-EC60-4BCE-970A-6C0ABA145705")]
[ComVisible(true)]
public interface IPresenterEvents
{
    void ExecuteCommand1(object message);
    void ExecuteCommand2();
}

IPresenterEvents 接口是您的事件接收器"接口，VBA 代码需要实现它，但我会讲到它.首先我们需要实现实际的presenter:

The IPresenterEvents interface is your "event sink" interface, that the VBA code will need to implement, but I'll get to it. First we need to implment the actual presenter:

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();
    }
}

现在，转到项目的属性，并选中注册 COM 互操作"复选框，然后构建项目；在 [Debug] 选项卡中，选择 start action Start external program"，然后在您的机器上找到 EXCEL.EXE 可执行文件:当您按 F5 时，Visual Studio 将启动带有调试器的 Excel，然后然后您可以打开 VBE (Alt+F11)，添加对刚刚构建的 .tlb(类型库)的引用(您可以在 .net 项目目录中的 indebug 下找到它theprojectname.tlb，假设是调试版本)，应该这样做.

Now, go to the project's properties, and check that "Register for COM interop" checkbox, then build the project; in the [Debug] tab, select start action "Start external program", and locate the EXCEL.EXE executable on your machine: when you press F5, Visual Studio will launch Excel with the debugger attached, and then you can open up the VBE (Alt+F11), add a reference to the .tlb (type library) that you just built (you'll find it in your .net project directory, under indebug heprojectname.tlb, assuming a debug build), and that should do it.

这里有很多问题，我稍后会回来解决:

There are a number of issues here, that I'll come back to fix later:

• Dispose() 方法没有公开，也不会在任何时候显式或隐式调用，这很脏.
• 虽然从 C# 调试器的角度来看，一切似乎都在工作，但我无法让该死的 VBA 处理程序运行.如果您打算在 VBA 中实现逻辑，而不仅仅是调出 UI，这可能是一个大问题.OTOH，您可以访问 .net 代码，还不如在演示器本身中实现演示器逻辑，在 C#/VB.NET 中，然后您不需要让这些事件处理程序工作.

• The Dispose() method isn't exposed, and won't be explicitly or implicitly called at any point, which is... dirty.
• While everything seems like it's working from the C# debugger's point of view, I couldn't get the darn VBA handlers to run. That's probably a big problem if you intend to implement the logic in VBA, not just bring up the UI. OTOH you have access to .net code, might as well implement the presenter logic in the presenter itself, in C#/VB.NET, and then you don't need to get these event handlers to work.

无论如何，我已将此代码添加到 ThisWorkbook:

Anyway, I've 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

当我从 立即窗口 (Ctrl+G) 运行 ThisWorkbook.DoSomething 时，我得到了:

And when I run ThisWorkbook.DoSomething from the immediate window (Ctrl+G), I get this:

理论上(至少根据 MSDN)，这就是你需要做的.正如我所说，这些事件处理程序由于某种原因没有被调用，但是嘿，你得到了闪亮的按钮！...以及 WPF 的所有功能现在可以设计您的 UI :)

In theory (at least according to MSDN), that's all you need to do. As I said these event handlers aren't getting called for some reason, but hey, you get your shiny buttons! ...and all the power of WPF to design your UI now :)

这篇关于VBA Windows 7 样式按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！