自定义函数OFFSETARRAY返回区域错误，求助各位大神。

lookcd

请教各位大神，如何实现OFFSETARRAY返回区域（数组）的vba自定义函数？
offsetarray数组自定义函数 - 副本.rar (15.39 KB, 下载次数: 9)

2024-6-14 10:59 上传

点击文件名下载附件

无关岁月

感觉这个函数比较鸡肋

1. =OFFSET(B2,2,3,4,5)

复制代码

lookcd

无关岁月 发表于 2024-6-14 11:11
感觉这个函数比较鸡肋

不是这个意思，是指初始选定的不是单元格，而是区域

小凡、、、

直接用offset，这个效果没必要用自定义

lookcd

小凡、、、 发表于 2024-6-14 13:54
直接用offset，这个效果没必要用自定义

是想学习一下，自定义的函数为何错误，调用excel内置函数，然后得到正确的结果，如何修改？

niko88819

=niko(B2,2,3,4,5)

1. Function niko(rng As Range, rows As Integer, cols As Integer, Optional height As Integer, Optional width As Integer) As Range
   
2.     Dim resultRange As Range
   
3.     If IsMissing(height) Then
   
4.         height = 1
   
5.     End If
   
6.     If IsMissing(width) Then
   
7.         width = 1
   
8.     End If
   
9.     Set resultRange = rng.Offset(rows, cols).Resize(height, width)
   
10.     Set niko = resultRange
    
11. End Function

复制代码

niko88819

再簡化

1. =OFFSETARRY(B2:P11,2,3,4,5)
   
2. 
   
3. Function OFFSETARRY(rng As Range, rows As Integer, cols As Integer, height As Integer, width As Integer) As Range
   
4.     Set OFFSETARRY = rng.Offset(rows, cols).Resize(height, width)
   
5. End Function

复制代码

lookcd

niko88819 发表于 2024-6-14 20:39
再簡化

感谢大侠的解答，是借用offset的方法，想问一下，怎么借用365新函数实现：

Function OFFSETARRAY数组(rng As Range, rows As Integer, columns As Integer, height As Integer, width As Integer)  As Range
    Dim x1 As Range, x2 As Range
    Application.Volatile
    With Application
    x1 = .TAKE(.Drop(rng, rows), height)
    x2 = .TAKE(.Drop(x1, 0, columns), , width)
    OFFSETARRAY数组 = x2
      End With
End Function

以上的写法为错在哪里？？？

niko88819

请修改你的代码如下，看是否满意
=OFFSETARRY(B2:P11,2,3,4,5)

1. Function OFFSETARRAY(rng As Range, rows As Integer, columns As Integer, height As Integer, width As Integer) As Range
   
2.     Dim x1 As Range, x2 As Range
   
3.     Application.Volatile
   
4.     With Application.WorksheetFunction
   
5.         Set x1 = .Index(rng, rows + 1, 0).Resize(height)
   
6.         Set x2 = .Index(x1, 0, columns + 1).Resize(height, width)
   
7.         Set OFFSETARRAY = x2
   
8.     End With
   
9. End Function

复制代码

lookcd

niko88819 发表于 2024-6-18 10:03
请修改你的代码如下，看是否满意
=OFFSETARRY(B2:P11,2,3,4,5)

大侠回复的没有问题，大侠的方法是直接调用vba函数，
俺想问的是直接调用excel内置函数的写法为何没有成功？调用take和drop函数
Function OFFSETARRAY(rng As Range, rows As Integer, columns As Integer, height As Integer, width As Integer)  As Range
    Dim x1 As Range, x2 As Range
    Application.Volatile
    With Application '可以调用excel内置函数
    set x1 = .TAKE(.Drop(rng, rows), height)
    set x2 = .TAKE(.Drop(x1, 0, columns), , width)
    set OFFSETARRAY = x2
      End With
End Function
还是因为当前365版本内置的vba不支持调用take和drop函数？