CODEWITHSUNDEEP
arraysvbafunction
Mathias Eriksen
Mathias Eriksen

Reputation: 45

Using a sub to call an array within a function

I have a little problem with my sub. This sub is calling to different functions, by using the sub's data. The first function finds one is finding the amount of unique values and the second function finds these values. However, the first function works fine because its output is a scalar value. However, the second function's output is an array. I have tried to search for a solution, but so far I have not succeeded. I have a theory that the issue has something to do with the ByRef A() As Integer. I have written the codes below, both for the sub and the second function.

Sub Test()
Dim A() As Integer
Dim n As Integer
Dim BB As Integer
n = 10
ReDim A(n, 2) '5 unikke

A(1, 1) = 1
A(2, 1) = 7
A(3, 1) = 2
A(4, 1) = 6
A(5, 1) = 3
A(6, 1) = 5
A(7, 1) = 1
A(8, 1) = 1
A(9, 1) = 1
A(10, 1) = 4

A(1, 2) = 1
A(2, 2) = 7
A(3, 2) = 2
A(4, 2) = 6
A(5, 2) = 3
A(6, 2) = 5
A(7, 2) = 1
A(8, 2) = 1
A(9, 2) = 1
A(10, 2) = 4

BB = Unikke(A) 'Unikke is the second function that provides the amount of unique values
Dim FF() As Integer
ReDim FF(BB, 1)

FF = HvilkeUnikke(A) 'the second function, which has the output of an array a.k.a the problem

End Sub

This is the function:

Public Function HvilkeUnikke(ByRef A() As Integer) As Integer

Dim L() As Integer
Dim B As Integer
Dim i As Integer
Dim i2 As Integer
Dim A2() As String
Dim BB As Integer
Dim C() As Integer

BB = Unikke(A)

ReDim C(UBound(A), 2)
ReDim A2(BB, 1)
ReDim L(BB, 1)

For i = 1 To UBound(A)
    C(i, 1) = A(i, 1)
    C(i, 2) = A(i, 2)
Next

For i = 1 To UBound(C)
B = 0
    For i2 = 1 To UBound(C)
        If C(i, 1) = C(i2, 2) Then
            B = B + 1
            If B > 1 Then
                C(i2, 2) = 0
            End If
        End If
    Next i2
Next i

B = 0
For i2 = 1 To UBound(C)
    If C(i2, 2) > 0 Then
        B = B + 1
        L(B, 1) = C(i2, 2)

    End If
Next i2


HvilkeUnikke = L
End Function

The results are as expected, but they should be in a variable inside my sub.

Upvotes: 0

Views: 98

Answers (1)

Mathias Eriksen
Mathias Eriksen

Reputation: 45

(The solution)

Sub test()

Dim FF() As Integer
Dim i As Integer
Dim A() As Integer
Dim n As Integer
Dim BB As Integer
n = 10
ReDim A(n, 2) '7 unikke

A(1, 1) = 1
A(2, 1) = 7
A(3, 1) = 2
A(4, 1) = 6
A(5, 1) = 3
A(6, 1) = 5
A(7, 1) = 1
A(8, 1) = 1
A(9, 1) = 1
A(10, 1) = 4

A(1, 2) = 1
A(2, 2) = 7
A(3, 2) = 2
A(4, 2) = 6
A(5, 2) = 3
A(6, 2) = 5
A(7, 2) = 1
A(8, 2) = 1
A(9, 2) = 1
A(10, 2) = 4


BB = Unikke(A)

ReDim FF(BB)

FF = HvilkeUnikke(A)

'Testing on the worksheet
For i = 1 To BB
    Cells(i, 1) = FF(i)
Next


End Sub

And the function

Public Function HvilkeUnikke(ByRef A() As Integer) As Integer()

Dim L() As Integer
Dim B As Integer
Dim i As Integer
Dim i2 As Integer
Dim A2() As String
Dim BB As Integer
Dim C() As Integer

BB = Unikke(A)

ReDim C(UBound(A), 2)
ReDim A2(BB, 1)
ReDim L(BB)

For i = 1 To UBound(A)
    C(i, 1) = A(i, 1)
    C(i, 2) = A(i, 2)
Next

For i = 1 To UBound(C)
B = 0
    For i2 = 1 To UBound(C)
        If C(i, 1) = C(i2, 2) Then
            B = B + 1
            If B > 1 Then
                C(i2, 2) = 0
            End If
        End If
    Next i2
Next i

B = 0
For i2 = 1 To UBound(C)
    If C(i2, 2) > 0 Then
        B = B + 1
        L(B) = C(i2, 2)
    End If
Next i2


HvilkeUnikke = L

End Function

Upvotes: 1

Related Questions