根据用户定义的功能创建VBA功能 [英] Create VBA function based on user-defined function

问题描述

感谢所有在我的问题上为我提供帮助的朋友如何计算特定单元格在excel中

Thanks to all friends who helped me on my question how to calculate specific cells in excel

现在，我需要帮助来编写VBA中excel功能的代码 函数是:=SUM(IFERROR(VALUE(IF(LEN(H27:Q27)=4,IF(ISNUMBER(SEARCH("b",LEFT(H27:Q27,2))),RIGHT(H27:Q27,1),LEFT(H27:Q27,1)),H27:Q27)),0))

Now, I need help to code that excel function in VBA The function is : =SUM(IFERROR(VALUE(IF(LEN(H27:Q27)=4,IF(ISNUMBER(SEARCH("b",LEFT(H27:Q27,2))),RIGHT(H27:Q27,1),LEFT(H27:Q27,1)),H27:Q27)),0))

预先感谢

推荐答案

在这里:

Public Function GetTotal(rng As Range) As Long
    Dim tot As Long
    Dim celString As String
    Dim t1String As String, t2String As String

    For Each cel In rng
        If IsNumeric(cel) Then
            tot = tot + cel.Value
        ElseIf Len(cel.Value) = 4 Then
            celString = cel.Value
            t1String = Left(celString, 2)
            If InStr(1, t1String, "b") = 0 Then
                t2String = Left(celString, 1)
            Else
                t2String = Right(celString, 1)
            End If
            tot = tot + t2String
        End If
        Debug.Print tot
    Next
    GetTotal = tot
End Function

您必须输入range.

请参见下图:

这篇关于根据用户定义的功能创建VBA功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！