Type Mismatch: array or user defined type expected

E

ExcelMonkey

I have an array that I am trying to sort. I keep getting an this compile
error:
"Type Mismatch: array or user defined type expected "
on the line Call Sort(NumberOfRuns - 1, EBITDAArray). It highlights the
EBITDAArray.

Why is this? Thanks

Sub Main ()
Dim EBITDAArray as Variant
Dim X as Double
Dim NumberOfRuns as Double

For X = NumberOfRuns - 1
'Populate Array with code
Next

Call Sort(NumberOfRuns - 1, EBITDAArray)
End Sub

Sub Sort(n As Double, arr() As Variant)

Dim Temp As Double
Dim i As Long
Dim j As Long

For j = 2 To n
Temp = arr(j)
For i = j - 1 To 1 Step -1
If (arr(i) <= Temp) Then GoTo 10
arr(i + 1) = arr(i)
Next i
i = 0
10 arr(i + 1) = Temp
Next j

End Sub
 
T

Tom Ogilvy

EBITDAArray isn't a variant array - it is a variant that contains an array.
So change the declaration in the sort Sub.

Sub Main()
Dim EBITDAArray As Variant
Dim X As Double
Dim NumberOfRuns As Double
NumberOfRuns = 20
ReDim EBITDAArray(1 To NumberOfRuns - 1)
For X = 1 To NumberOfRuns - 1
EBITDAArray(X) = Int(Rnd() * 1000 + 1)
'Populate Array with code
Next

Call Sort(NumberOfRuns - 1, EBITDAArray)
For X = 1 To NumberOfRuns - 1
Debug.Print EBITDAArray(X)
Next
End Sub

Sub Sort(n As Double, arr As Variant)

Dim Temp As Double
Dim i As Long
Dim j As Long

For j = 2 To n
Temp = arr(j)
For i = j - 1 To 1 Step -1
If (arr(i) <= Temp) Then GoTo 10
arr(i + 1) = arr(i)
Next i
i = 0
10 arr(i + 1) = Temp
Next j

End Sub
 
E

ExcelMonkey

Thansk Tom. Just realised the sort sub is was used assuming Option Base 1.
If I am using Option Base 0 then I am assuming I have to adjust certain items
in the sub. Which ones need adjsuting? Is j = 1?
 
T

Tom Ogilvy

this would be my guess:

Sub Main()
Dim EBITDAArray As Variant
Dim X As Double
Dim NumberOfRuns As Double
NumberOfRuns = 5
ReDim EBITDAArray(NumberOfRuns)
For X = LBound(EBITDAArray) To NumberOfRuns
EBITDAArray(X) = Int(Rnd() * 1000 + 1)
'Populate Array with code
Next

Call Sort(NumberOfRuns, EBITDAArray)
For X = LBound(EBITDAArray) To NumberOfRuns
Debug.Print X, EBITDAArray(X)
Next
End Sub

Sub Sort(n As Double, arr As Variant)

Dim Temp As Double
Dim i As Long
Dim j As Long

For j = LBound(arr) + 1 To n
Temp = arr(j)
For i = j - 1 To LBound(arr) Step -1
If (arr(i) <= Temp) Then GoTo 10
arr(i + 1) = arr(i)
Next i
i = LBound(arr) - 1
10 arr(i + 1) = Temp
Next j

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top