带下拉列表的VBA自定义Excel函数 [英] VBA Custom Excel Function with dropdown list

问题描述

我在Excel(VBA)中有我的自定义函数，但是我正在尝试在CELL函数中执行类似该列表的操作:

I have my custom function in Excel (VBA), but I'm trying to do something like that list within the CELL function:

在Excel中尝试:

= CELL(info_type [，然后出现下拉列表]

=CELL(info_type [ and then here appears the dropdown list ]

所以，我想要:

= MyFunction(my_variable [和我的自定义下拉列表在这里]

=MyFunction(my_variable [ and my custom dropdown list here ]

该列表应显示在Excel中，而不是在VBA窗口中.

The list should appear in Excel, not in the VBA window.

有人知道该怎么做吗?

对不起，我无法发布图片.

Sorry, I can't post a picture.

推荐答案

这是一种可能可行的笨拙方法.想法是使用Worksheet_Change事件捕获实例，在这些实例中您输入功能的不完整版本，在此阶段，单元格上将显示实时数据验证下拉列表，为您提供了完成功能的可能方法.然后，再次使用Worksheet_Change，检测完成的版本，将其转换为公式，并删除数据验证.

Here is a clunky approach that might work. The idea is to use the Worksheet_Change event to capture instances where you enter an incomplete version of your function at which stage an on-the-fly data validation drop down list is displayed on the cell giving you possible ways to complete the function. Then, again using Worksheet_Change, the completed version is detected, turned into a formula, and the data validation is removed.

作为概念证明-我编写了sin版本，该版本允许用户选择度"或弧度":

As a proof of concept -- I wrote a version of sin which allows the user to select "Degrees" or "Radians":

Function Sine(ParamArray args() As Variant) As Variant
    On Error GoTo err_handler
    If args(1) = "Degrees" Then
        Sine = Sin(args(0) * Application.WorksheetFunction.Pi() / 180)
    Else
        Sine = Sin(args(0))
    End If
    Exit Function
err_handler:
    Sine = "=Sine(" & args(0) & ","
End Function

如果用户至少不给一个角度，这将引发未捕获的错误.

This throws an uncaught error if the user doesn't at least give an angle.

然后，在我使用的Worksheet_Change中:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim choices As String, angle As String, tval As String

    On Error GoTo err_handler
    tval = Target.Value
    If tval Like "=Sine(*," Then
        angle = Mid(tval, 7)
        angle = Left(angle, Len(angle) - 1) 'get rid of comma
        choices = angle & " Degrees, " & angle & " Radians"
        Target.Validation.Add xlValidateList, Formula1:=choices
        Target.Select
    ElseIf tval Like "* Degrees" Then
        Target.Validation.Delete
        Target.Formula = "=Sine(" & Val(tval) & ", ""Degrees"")"
        Target.Offset(1).Select
    ElseIf tval Like "* Radians" Then
        Target.Validation.Delete
        Target.Formula = "=Sine(" & Val(tval) & ", ""Radians"")"
        Target.Offset(1).Select
    End If
err_handler:
End Sub

它的工作原理是这样的.在A1中(例如)键入=Sine(45，然后按Enter.您将看到以下内容(单击下拉箭头后):

It works like this. In A1 (say) type =Sine(45 and hit enter. You will see the following (after clicking on the drop down arrow):

然后，在选择例如A1中的公式变为"45度"

Then, after selecting e.g. "45 Degrees" the formula in A1 becomes

=sine(45, "Degrees")

并显示值(0.707107)，并且数据验证已删除.

and the value (0.707107) is displayed, and the data validation has been removed.

一种更灵活的想法是通过单元显示用户表单，而不是依赖于此数据验证黑客.

A variation of the idea that might be more flexible is to display a userform by the cell rather than rely on this data-validation hack.

这篇关于带下拉列表的VBA自定义Excel函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！