Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Questions about Using VBA

Status
Not open for further replies.

Stevridge

Programmer
Sep 8, 2010
7
CA
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.
 
Hi,

In VBA, arguments are default ByRef, which means that values are returned to the calling procedure.
Example...
Code:
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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?
 
Code:
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
“how do I access the Excel sort routine?”

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

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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:
Code:
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
 
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.
 
> 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 … :)
 
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?
 
@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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top