VBA. "Type mismatch: array or user-defined type expected"

Question

I am completely new to VBA. I need to write a program, which will generate an array of integer and will find an index of the minimal element. I got this error "Type mismatch: array or user-defined type expected." I looked into many similar questions, but couldn't figure out what is wrong.

Function random_integer(Min As Integer, Max As Integer)
random_integer = Int((Max - Min + 1) * Rnd + Min)
End Function

Function generate_array(Size As Integer)
Dim Arr(Size)
For i = 0 To UBound(Arr)
    Arr(i) = random_integer(i - 10, i + 10)
Next
generate_array = Arr
End Function

Function find_index_of_min_elem(ByRef Arr() As Variant)
Dim Min As Integer
Min = Arr(0)
Dim MinIndex As Integer
MinIndex = 0
For i = 1 To UBound(Arr)
    If Arr(i) < Min Then
        Min = Arr(i)
        MinIndex = i
    End If
Next
find_index_of_min_elem = MinIndex
End Function

Sub task6()
A = generate_array(20)
IndexOfMinElemInA = find_index_of_min_elem(A)
MsgBox IndexOfMinElemInA
End Sub

You'll want to drop the snake_case and adopt PascalCase for member names. In VBA the underscore has a special meaning; your functions can't be refactored into an interface named like that. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 28, 2017 at 18:58

Siddharth Rout · Accepted Answer · 2017-06-28 18:57:58Z

7

The problem is the function Function find_index_of_min_elem(ByRef Arr() As Integer) is expecting an Integer as a parameter and you are passing a as a Variant

a = generate_array(20)
IndexOfMinElemInA = find_index_of_min_elem(a)

The next error that you will get is on Dim Arr(Size) As Integer. You cannot dimension an array like that.

I would recommend reading up on arrays.

There may be other errors but I have not checked those.

edited Jun 28, 2017 at 18:57

answered Jun 28, 2017 at 18:55

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

user4039065 Over a year ago

Congrats on reaching that 100K! Have another!

Mathieu Guindon Over a year ago

I seriously think so =) - "Assuming you have a valid email address on your profile and you are opted in to receiving emails from us, you should get an automated email within a week of hitting the reputation threshold. If not, check your spam folder and make sure it didn't end up there."

user4039065 Over a year ago

News Alert! SO Mug up on eBay!

Mathieu Guindon Over a year ago

Congrats again - next stop 250K!

A.S.H Over a year ago

Late into the party! congratulations :)

|

Tony M · Accepted Answer · 2017-07-02 05:56:47Z

0

What about this? Make a second column of index numbers (in sequence from small to large) and then order the two rows by your original column. I've included the example below to illustrate: A = the original column of numbers, B = the column of index numbers D & E are the result of sorting A & B by column A The answer is then: the lowest number "0" was at index 7

answered Jul 2, 2017 at 5:56

Tony M

1,7583 gold badges21 silver badges38 bronze badges

Collectives™ on Stack Overflow

VBA. "Type mismatch: array or user-defined type expected"

2 Answers 2

15 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

15 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related