VBA Windows 7风格按钮 [英] VBA Windows 7 style buttons

查看：249
本文介绍了VBA Windows 7风格按钮的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我很确定这个问题已经在网路上被问到很多，我在几个论坛上都读过很多问题和他们的答案，但我从来没有看到一个明确的答案，所以我想要知道：



可以使用Windows 7风格按钮





在Excel VBA中，或者我必须使用这些灰色的东西，就像他们来自





？



我 strong>想使用图像，我的意思是导入这些ActiveX控件，我认为这是他们的名字。

解决方案





首先，创建一个新的C＃（或VB.NET ..无论如何摇滚你的船）类库，并添加一个新的WPF UserControl，并设计你的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 =dd：DataContext =ViewModel1
d：DesignHeight =200 d：DesignWidth =300> 
 
< Grid Background =White> 
 
< Grid.RowDefinitions> 
< RowDefinition Height =*/> 
< RowDefinition Height =32/> 
< /Grid.RowDefinitions> 
 
< TextBlock Text =此处的实际内容Foreground =DimGrayHorizo​​ntalAlignment =CenterVerticalAlignment =Center/> 
 
< StackPanel Grid.Row =1Orientation =Horizo​​ntalHorizo​​ntalAlignment =RightMargin =2> 
< Button Width =128Command ={Binding Command1}> 
< TextBlock Text =Button1/> 
< / Button> 
< Button Width =128Command ={Binding Command2}> 
< TextBlock Text =Button2/> 
< / Button> 
< / StackPanel> 
 
< / Grid> 
< / UserControl>

构建项目。



然后，添加一个新窗体，停止一个WPF Interop ElementHost 控件，你应该可以添加你的WPF UserControl1 WPF控件使用数据绑定来连接 Command1 和 Command2 （和其他一切，真的 - 在Model-View-ViewModel模式下阅读），所以你需要一个类来实现托管代码部分。如果你的逻辑是所有的VBA，那么这应该是非常苗条的：

  public class ViewModel1 
 {
 public ViewModel1（）
 {
 _command1 = new DelegateCommand（ExecuteCommand1）; 
 _command2 = new DelegateCommand（ExecuteCommand2）; 
} 
 
私有readonly ICommand _command1; 
 public ICommand Command1 {get {return _command1; }} 
 
 public event EventHandler ExecutingCommand1; 
 private void ExecuteCommand1（object parameter）
 {
 ExecuteHandler（ExecutingCommand1）; 
} 
 
私有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 是一个非常好的小东西，这一切都是Stack Overflow，所以如果有任何问题，请不要犹豫：

  public class DelegateCommand：ICommand 
 {
 private readonly Action< object> _执行; 
 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 - 公开一个方法：

  public partial class Form1：Form 
 {
 public Form1（）
 {
 InitializeComponent（）; 
} 
 
 public void SetDataContext（ViewModel1 viewModel）
 {
 hostedWPFControl.DataContext = viewModel; 
} 
}

除此之外，不应该在这里的任何代码。





WPF喜欢MVVM模式，WinForms喜欢MVP（lookup 模型 - 视图 - 演示）。 WPF部分托管在WinForms中，我们将使一个演示者 - 这是VBA代码将使用的对象：

  [ComVisible（true）] 
 public interface IPresenter1 
 {
 void Show（）; 
}

是的，这只是一个界面。继续，我们需要另一个：

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

IPresenterEvents 是您的事件接收器接口，VBA代码将需要实现，但我会得到它。首先我们需要隐藏实际的演示者：

  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]选项卡中，选择启动操作启动外部程序，并在您的计算机上找到EXCEL.EXE可执行文件：按F5时，Visual Studio将启动Excel并附加调试器，那么你可以打开VBE（Alt + F11），添加一个你刚构建的.tlb（类型库）的引用（你可以在.net项目目录下找到它，位于 \ bin \debug\theprojectname.tlb ，假设调试版本），应该这样做。



有一些问题在这里，我稍后再来解决：




  • Dispose（）方法不是暴露的，并且不会在任何时候被明确地或隐含地调用，这是...脏的。

  • 虽然一切似乎都是从C＃调试器的角度查看，我不能得到darn的VBA处理程序运行。如果您打算在VBA中实现逻辑，而不仅仅是介绍UI，那可能是一个大问题。 OTOH可以访问.net代码，也可以在C＃/ VB.NET中实现演示者本身的演示者逻辑，然后您不需要使这些事件处理程序正常工作。



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

  Option Explicit 
 Private WithEvents view As ComVisibleUI.Presenter1 
 
 Public Sub DoSomething（）
设置view = New ComVisibleUI.Presenter1 
 view.Show 
 End Sub 
 
 Private Sub view_ExecuteCommand1（ByVal message As Variant）
 MsgBox消息
 End Sub 
 
私有子视图_ExecuteCommand2（）
 MsgBox你好WPF！ 
 End Sub

当我运行 ThisWorkbook.DoSomething 从直接窗口（Ctrl + G），我得到这个：





理论上（至少根据 MSDN ），这就是你需要做的。正如我所说，这些事件处理程序不是因为某些原因而被呼叫，而是嘿，你会得到你闪亮的按钮！ ...和WPF的所有力量设计您的UI现在：）


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:

Is is possible, to use Windows 7 style buttons

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

?

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

解决方案

Buckle up, you're in for a ride.

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>

Build the project.

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.

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

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

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

Other than that, there shouldn't be any code in here.

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

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

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 \bin\debug\theprojectname.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:

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

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

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

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

查看全文
相关文章
Office最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆