×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Questions about Using VBA

Questions about Using VBA

Questions about Using VBA

(OP)
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.

RE: Questions about Using VBA

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Questions about Using VBA

(OP)
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?

RE: Questions about Using VBA

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Questions about Using VBA

“how do I access the Excel sort routine?”

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Questions about Using VBA

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

RE: Questions about Using VBA

(OP)
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.

RE: Questions about Using VBA

> 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 … smile

RE: Questions about Using VBA

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?

RE: Questions about Using VBA

@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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close