无法分配给数组 -vba

can't assign to array -vba

提问by shay

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

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

cant assign to array

不能分配给数组

Where is the error ? Note that if i type Dim arrf() As Variantinstead of Dim arrf(5) As VariantI get error -

错误在哪里？请注意，如果我输入Dim arrf() As Variant而不是Dim arrf(5) As Variant我收到错误 -

type mismatch

类型不匹配

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

thanks ahead to all helpers

提前感谢所有帮助者

回答by Comintern

You have arrfdeclared as a fixed sizearray:

您已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

回答by Whome

If you use a typed array in VBA script always use ReDim..size initialization. You may use a typed array in a dictionary value or everywhere like a regular variable.

如果在 VBA 脚本中使用类型化数组，请始终使用ReDim..大小初始化。您可以在字典值中或在任何地方使用类型化数组，就像常规变量一样。

Public Function readData(ws As Worksheet, arr As Scripting.Dictionary) As Boolean
  Dim iRow as long 
  Dim key as String 
  Dim sVal() As String
  ReDim sVal(0 to 1) as String 

  For iRow=2 to 1000
    key = ws.cells(iRow,1)
    sVal(0) = ws.Cells(iRow, 5)
    sVal(1) = ws.Cells(iRow, 6)
    call arr.Add(key, sVal)
  Next
  readData=true 
End Function

Public Function writeData(ws As Worksheet, arr As Scripting.Dictionary) As Long
  Dim iRow as long 
  Dim key as String 
  Dim sVal() As String
  ReDim sVal(0 to 1) as String 

  For iRow=2 to 1000
    key = ws.cells(iRow,1)
    If arr.Exists(key) then
      sVal = arr.Item(key)
      ws.Cells(iRow, 5) = sVal(0)
      ws.Cells(iRow, 6) = sVal(1)
    End If
  Next
  writeData=true 
End Function

回答by Thomas G

There's an issue in your function calc(): it only returns a value when it goes through the else, and not the if

您的函数中存在一个问题calc()：它仅在通过 时返回一个值else，而不是通过if

It should probably be this:

应该是这样的：

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
  End If
  calc = arr ' <------- THIS
End Function

回答by Thomas G

You need to declare aarfas a regular variant not an array. The VBA will convert it for you.

您需要声明aarf为常规变体而不是数组。VBA 将为您转换它。

Dim arrf As Variant

Dim arrf As Variant