VBA功能中的可选范围 [英] Optional ranges in vba function

问题描述

我试图返回一个范围内的列数，有时我需要一个范围，但是有时我需要多个范围.

I am trying to return the columns count on a range, sometimes I need one range, but, sometimes I need more than one range.

我设置了可选范围，因此我可以选择多个范围.如果我引用了电子表格中未提供的函数原型中的某个范围，则会得到#Value！错误.

I have put in optional ranges so I can choose multiple ranges. If I reference a range in the function prototype that I have not supplied in the spreadsheet I get the #Value! error.

我需要一种方法来检查可选范围是否为null，是否为空等，因此我不必引用该范围.

I need a way to check if the optional ranges are null, void empty etc. so I don't have to reference the range.

这是VBA函数原型:-

This is the VBA Function Prototype:-

Function GetColoumnCount(ARange1 As Range, Optional ARange2 As Range, Optional ARange3 As Range, Optional ARange4 As Range) As Integer

    Dim Result As Integer
    Result = 0

    Result = ARange1.Columns.Count ' This works
    Result = ARange1.Columns.Count + ARange2.Columns.Count ' This doesn't work

    GetColoumnCount = Result    
End Function

在我的电子表格中，我必须在一个单元格中输入此内容才能使用该功能.
=GetColoumnCount(BC34:BK34, BC35:BD35, BE35:BF35, BG35:BH35)
这违反了使用可选参数的目的.

In my spreadsheet I have to enter this in a cell for the function to work.
=GetColoumnCount(BC34:BK34, BC35:BD35, BE35:BF35, BG35:BH35)
this defeats the purpose of having optional arguments.

推荐答案

像这样尝试

Function GetColoumnCount(ARange1 As Range, Optional ARange2 As Range, Optional ARange3 As Range, Optional ARange4 As Range) As Long  
    Dim Result As Long
    Result = 0

    Result = ARange1.Columns.Count ' This works
    If Not ARange2 Is Nothing Then
        Result = Result + ARange2.Columns.Count
    End If

    GetColoumnCount = Result    
End Function

这篇关于VBA功能中的可选范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！