can't assign to array -vba
25,156
You have arrf
declared as a fixed size array:
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
Author by
Admin
Updated on June 24, 2020Comments
-
Admin almost 4 years
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 Variant
instead ofDim arrf(5) As Variant
I get error -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