无法分配给数组-vba [英] can't assign to array -vba

问题描述

我正在尝试实现下一个代码并得到错误-

I'm trying implement the next code and get the error -

不能分配给数组

错误在哪里?请注意，如果我键入 Dim arrf()作为变体，而不是 Dim arrf(5)作为变体，则会出现错误-

Where is the error ? Note that if i type Dim arrf() As Variant instead of Dim arrf(5) As Variant I get error -

类型不匹配

Public Function calc(ByVal value As Integer, ByVal num As Integer) As Variant()

Dim arr(5) As Variant
Dim x As Double

If value >= num Then
    x = value - Application.RoundDown(value / num, 0) * num
    arr(0) = x
    arr(1) = num - arr(0)
    arr(2) = Application.RoundUp(value / num, 0)
    arr(3) = 1
    arr(4) = Application.RoundDown(value / num, 0)
    arr(5) = 1
Else
    x = num - Application.RoundDown(num / value, 0) * value
    arr(0) = x
    arr(1) = value - arr(0)
    arr(2) = Application.RoundUp(num / value, 0)
    arr(3) = 1
    arr(4) = Application.RoundDown(num / value, 0)
    arr(5) = 1
    calc = arr
End If

End Function


Sub cellsfunc()

With Application
    .DisplayAlerts = False
    .EnableEvents = False
    .ScreenUpdating = False
End With

Dim lastrow As Integer
Dim counter As Integer

Dim arrf(5) As Variant

lastrow = Cells(Rows.Count, 2).End(xlUp).Row
For counter = 2 To lastrow Step 2
    arrf = calc(Cells(4, counter), Cells(4, counter + 1))
Next counter

With Application
    .DisplayAlerts = True
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Sub

感谢所有帮助者

推荐答案

您已将 arrf 声明为固定大小数组:

Dim arrf(5) As Variant

数组返回函数不能返回固定大小的数组-只能返回动态数组.您只需要将其声明为动态数组即可:

An array returning function can't return a fixed size array - only a dynamic one. You just need to declare it as a dynamic array:

Dim arrf() As Variant

这篇关于无法分配给数组-vba的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！