Questions about Using VBA

[Stevridge]

I have done a fair amount of higher level language programming but am somewhat new to VBA. I've run into some issues I can't figure out.

1. How can I pass multiple values back to a calling sub program? For instance, I have the Cholesky decomposition that will be called by a variety of programs. I can pass an array to the decomposition program but how do I get the decomposition back to the caller?

2. Accessing Excel functions is easy but how do I access the Excel sort routine?

Thanks for any assistance.

 

[SkipVought]

Hi,

In VBA, arguments are default ByRef, which means that values are returned to the calling procedure.
Example...

Sub MAIN()
    Dim x, y
    x = 5
    y = -2
    MakeChange x, y
    Debug.Print "x=", x, "y=", y
    Debug.Print MChange(x, y)
    Debug.Print "x=", x, "y=", y
End Sub

Sub MakeChange(a, b)
    Dim c
    
    c = a
    a = b
    b = c
End Sub

Function MChange(a, b)
    MChange = a
    a = b
    b = MChange
    MChange = a + b
End Function

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Stevridge]

I don't think this will solve my problem. Suppose I have
Sub Main ()
Input a range, myarr
a = myarr
...
Makechange(a)
...

Now consider two scenarios.
1. Makechange makes changes to a and I want the changed a to return to Main. Have the changes affected a in main?
2. As before only now Makechange puts the changes to a in array b. How do I get b back too main?

 

[SkipVought]

Sub MAIN()
    Dim x(2), i, y
    x(0) = 2
    x(1) = 1
    x(2) = 3
    
    MakeChange x, y
    
    For i = 0 To UBound(x)
        Debug.Print x(i), y(i)
    Next
    
End Sub

Sub MakeChange(a, Optional b)
    Dim c, i, j
    For i = 0 To UBound(a) - 1
        For j = i + 1 To UBound(a)
            If a(i) > a(j) Then
                c = a(j)
                a(j) = a(i)
                a(i) = c
            End If
        Next j
    Next i
    b = a
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

“how do I access the Excel sort routine?”

You want to sort on a sheet, rather than using array?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

In the first process you may encapsulate decomposition using function that returns user defined type consisted of two arrays. When working with dynamic arrays a part has to be declared as variant. The idea:

Public Type CholeskyDecompositionMatrix
    LowerTriangularMatrix() As Variant
    ConjugateTransposeMatrix() As Variant
End Type

Sub main()
Dim InputMatrix() As Double
Dim OutputDecomposition As CholeskyDecompositionMatrix
ReDim InputMatrix(1 To 2, 1 To 2)
InputMatrix(1, 1) = 2: InputMatrix(1, 2) = 5
InputMatrix(2, 1) = -5: InputMatrix(2, 2) = 3
OutputDecomposition = CholeskyDecomposition(InputMatrix)
' Now you have access to two matrices:
' (1) OutputDecomposition.LowerTriangularMatrix
' (2) OutputDecomposition.ConjugateTransposeMatrix
End Sub

Public Function CholeskyDecomposition(InputMatrixArg() As Double) As CholeskyDecompositionMatrix
Dim Matrix1() As Variant, Martix2() As Variant
Dim N As Long
N = UBound(InputMatrixArg, 1)
ReDim Matrix1(1 To N, 1 To N)
ReDim Matrix2(1 To N, 1 To N)
' process data
CholeskyDecomposition.LowerTriangularMatrix = Matrix1
CholeskyDecomposition.ConjugateTransposeMatrix = Matrix2
End Function

combo

 

[Stevridge]

Thanks for your help guys (or gals). I'll try out your suggestions.

Skip, I want to sort VBA arrays. Writing a vba program to do it is no problem but why reinvent the wheel if I don't have to.

 

[strongm]

> Writing a vba program to do it is no problem but why reinvent the wheel if I don't have to.

You could always use .Net's ArrayList …

 

[lameid]

I am late to the party but had a thought, maybe not a good one but here it goes...

A question for the resident Excel Expert SkipVought, can the array be put in an Excel Range object to then sort? Or am I making a bad assumption on the applies to list?

 

[SkipVought]

@lameid, sure, which is why I asked the OP, “You want to sort on a sheet, rather than using array?”

To which he gave a vague reply and everyone knows, what happens in vagueness, stays in vagueness.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MajP]

http://www.cpearson.com/excel/vbaarrays.htm