时间戳

首页 > 解决方案 > VBA Excel 2016 创建调用用户定义函数的宏

excel - VBA Excel 2016 创建调用用户定义函数的宏

问题描述

Excel 2016 我使用用户定义的函数来组合一长串数字并用逗号分隔它们。使用我们的一些旧软件可以节省大量时间。我一直在使用的功能在这里。

Function ConcatDelim(ConcatRange As Variant) As String

    Dim Test As Boolean
    Test = True
    
    For Each i In ConcatRange
        If Test Then
            ConcatDelim = i
            Test = False
        Else
            ConcatDelim = ConcatDelim & ", " & i
        End If
    Next i
    
End Function

我想创建一个宏，在当前选定的单元格上使用此函数，在工作表顶部创建一个新行并将结果输出到 A1 中。

这是我为宏编写的内容。

Sub ConcatDelimMacro()
    ' Keyboard Shortcut: Ctrl+Shift+G
    'Concat Delim Function
    Dim SelectionRange As Range
    Dim varCellCount As Integer
    Dim ConcatString As String
    Set SelectionRange = Selection
    Set varCellCount = Count(SelectionRange)
    If varCellCount > 99 Then
        MsgBox "You have selected too many cells, please select 99 or fewer at a time"
    ElseIf varCellCount <= 99 Then
        Worksheets.Rows(1).Insert
        Concat = ConcatDelim(SelectionRange)
        Range(A1) = Concat
End Sub

我要么没有结果，要么编译错误“未定义子或函数”。我试过让函数和宏是同一个模块并且是分开的。我很抱歉我对 VBA 不是很有经验。

任何意见，将不胜感激。

标签： excelvbaexcel-2016

解决方案

一些建议：

Function ConcatDelim(ConcatRange As Variant) As String
    Dim sep As String, c As Range, v
    For Each c In ConcatRange.Cells
        v = Trim(c.Value)     'remove any extra spaces
        If Len(v) > 0 Then
            ConcatDelim = ConcatDelim & sep & v
            sep = ","         'add delimiter after first non-blank value
        End If
    Next c
End Function


Sub ConcatDelimMacro()
    Dim ConcatString As String
    
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please first select up to 99 cells"
        Exit Sub
    End If
    
    If Selection.Cells.Count > 99 Then
        MsgBox "You have selected too many cells, please select 99 or fewer at a time"
    Else
        ConcatString = ConcatDelim(Selection)
        With Selection.Parent 'parent = the worksheet where range is selected
            .Rows(1).Insert
            .Range("A1").Value = ConcatString
        End With
    End If
End Sub

推荐阅读