时间戳

首页 > 解决方案 > 新模块中的 ByRef 参数类型不匹配错误

excel - 新模块中的 ByRef 参数类型不匹配错误

问题描述

下面的代码在一个项目中运行良好，但只要我把它放在包含我所有代码的项目中，我就会得到一个“ ByRef argument type mismatch error”。我不明白为什么会这样。

它打破了以下行：If Left(midText, 2) = "|-" Then

我没有在我的项目中的任何其他代码中引用 midText。

Function ColumnHelper(midText As String, inBold As Boolean, _
                        inColor As Integer, inStrikeThru As Boolean)
    Selection.NumberFormat = "General"
    Selection.HorizontalAlignment = xlGeneral
    If Selection.Count > 1 Then
    'this performs a "softcenter"
        Selection.HorizontalAlignment = xlCenterAcrossSelection
    Else
        Selection.HorizontalAlignment = xlCenter
    End If

    Dim dashCount As Integer
    Dim tempText As String
    If Left(midText, 2) = "|-" Then
        On Error Resume Next 'count dashes before space
        For i = 2 To Len(midText) - 1
            If Mid(midText, i, 1) = "-" Then
                dashCount = dashCount + 1
            Else
                Exit For
            End If
        Next i
        tempText = Mid(midText, dashCount + 3, Len(midText) _
                                      - (2 * (dashCount + 2)))
        If Err = 0 Then
            midText = tempText
        End If
    End If

    Dim selWidth As Integer 'Get the width of the selection
    For Each cell In Selection.Rows(1)
        selWidth = selWidth + cell.Width
    Next cell

    Dim dashes As String 'Create a string containing dashes
    dashes = "-"

    For i = 0 To CInt(selWidth / 6) - (0.9 * Len(midText)) - 5
        dashes = dashes + "-"
    Next i
    Dim dashLen As Integer
    dashLen = Len(dashes)

    Selection.Item(1).FormulaR1C1 = "|" + dashes + " " + midText + _
                " " + dashes + "|" 'Input text into cell
    With Selection.Item(1).Font
        .Name = "System"
        .FontStyle = "Bold"
        .Size = 8
        .ColorIndex = inColor
        .Strikethrough = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlNone
    End With
    With Selection.Item(1).Characters(Start:=Len(dashes) + 2, _
                                        Length:=Len(midText) + 2).Font
        .Name = "Arial"
        If inBold Then
            .FontStyle = "Bold"
        Else
            .FontStyle = "Regular"
        End If
        .Strikethrough = inStrikeThru
    End With
End Function

标签： excelvba

解决方案

一个示例调用会有所帮助，但这里有一些观察结果：

1）这是一个子而不是一个功能。它做事但不返回任何东西。考虑你是否真的需要返回任何东西（在这种情况下是一个函数，你需要一个赋值并且签名应该指明返回类型）。

2）它修改了的值，midText因此您可以显式传递ByRef。

3）您有未声明的变量（例如i, Cell)，这意味着您没有Option Explicit在模块顶部使用，您应该这样做。

4）你有一个未封闭的On Error Resume Next掩盖任何潜在的错误。

5) 使用有意义的变量名和明确的变量名，例如不要Cell用作变量名。

6) 使用Long不会Integer降低潜在的溢出风险。

7）您可以使用更有效的类型函数，例如Mid$

8）你不做任何事情dashLen。它可能会被删除。

9）你能把它分解成处理单个任务的更小的子/功能吗？

其中一些建议：

Option Explicit
Public Sub test()

    ColumnHelper "HELLO", True, 2, True

End Sub

Public Sub ColumnHelper(ByRef midText As String, ByVal inBold As Boolean, ByVal inColor As Long, ByVal inStrikeThru As Boolean)
    Selection.NumberFormat = "General"
    Selection.HorizontalAlignment = xlGeneral
    If Selection.Count > 1 Then
        Selection.HorizontalAlignment = xlCenterAcrossSelection
    Else
        Selection.HorizontalAlignment = xlCenter
    End If

    Dim dashCount As Long, tempText As String, i As Long
    If Left$(midText, 2) = "|-" Then

        For i = 2 To Len(midText) - 1
            If Mid$(midText, i, 1) = "-" Then
                dashCount = dashCount + 1
            Else
                Exit For
            End If
        Next i
        tempText = Mid$(midText, dashCount + 3, Len(midText) - (2 * (dashCount + 2)))
        If Err = 0 Then
            midText = tempText
        End If
    End If

    Dim selWidth As Long, iCell As Range
    For Each iCell In Selection.Rows(1)
        selWidth = selWidth + iCell.Width
    Next iCell

    Dim dashes As String
    dashes = "-"
    For i = 0 To CInt(selWidth / 6) - (0.9 * Len(midText)) - 5
        dashes = dashes + "-"
    Next i

    Dim dashLen As Long
    dashLen = Len(dashes)

    Selection.Item(1).FormulaR1C1 = "|" + dashes + " " + midText + _
                                    " " + dashes + "|"
    With Selection.Item(1).Font
        .Name = "System"
        .FontStyle = "Bold"
        .Size = 8
        .ColorIndex = inColor
        .Strikethrough = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlNone
    End With
    With Selection.Item(1).Characters(Start:=Len(dashes) + 2, Length:=Len(midText) + 2).Font
        .Name = "Arial"
        If inBold Then
            .FontStyle = "Bold"
        Else
            .FontStyle = "Regular"
        End If
        .Strikethrough = inStrikeThru
    End With
End Sub

推荐阅读