VBA 自定义函数大全.doc

VBA自定义函数大全 龙族联盟论坛shcnmartin收集整理 ################################################################ 1.函数作用：返回 Column 英文字 ################################################################ Function ColLetter(ColNumber As Integer) As String On Error GoTo Errorhandler ColLetter = Left(Cells(1, ColNumber).Address(0, 0), 1 - (ColNumber 26)) Exit Function Errorhandler: MsgBox Error encountered, please re-enter End Function ################################################################ 2.函数作用：查询某一值第num次出现的值 参数说明：Value1：查询引用的数值; Range1：查询区域; num：指定查询第几次出现; Col：返回值, 相对引用区域, 相对引用列的右数第Col列 ################################################################ Function MyFind(Value1, ByVal Range1 As Range, ByVal num As Integer, ByVal Col As Integer) If Value1 = Then Exit Function If Range1.Columns.Count 1 Then Exit Function For Each D In Range1 If D.Value = Value1 Then c = c + 1 If c = num Then v1 = D(1, Col) Exit For End If ElseIf IsEmpty(D) Then Exit For End If Next If v1 = Then v1 = not MyFind = v1 End Function ################################################################ 3.函数作用：返回当个人工资薪金所得为2000元(起征点为850元)时的应纳个人所得税税额 语 法：Grsds(bsc, mysala) 参数说明：bsc: 必选项，为起征点，包括税法规定的工资基数800元加上允许税前扣除的合理费用; mysala: 必选项，为人个工资薪金所得。 示 例：Grsds(850, 20000) = ################################################################ Function Grsds(bsc As Double, mysala As Double) As Double bsc为起征点加上允许税前扣除的合理费用,mysala为工资薪金所得 On Error GoTo Grsds_err Select Case mysala Case Is = bsc Grsds = 0 Case Is = bsc + 500 Grsds = Application.WorksheetFunction.Round((mysala - bsc) * 0.05, 2) Case Is = bsc + 2000 Grsds = Application.WorksheetFunction.Round((mysala - bsc) * 0.1 - 25, 2) Case Is = bsc + 5000 Grsds = Application.Workshe