CODEWITHSUNDEEP
arraysexcelvba
ayy llama
ayy llama

Reputation: 1

How do I prevent arrays from resetting values between sub procedures in VBA

I was trying to practice passing array values when calling sub procedures or functions when I kept running into the problem of the array values from resetting. I found a tutorial that was showcasing exactly what I was trying to do here: https://www.youtube.com/watch?v=1UUpu1WGKXI

I used the same exact code however, when I run it my combined array which is represented as "C" in the procedure would reset to zero even though that doesn't happen to the author of the tutorial.

See code below (also seen in tutorial link above):

Option Explicit
Option Base 1

Sub main()
'CPPMechEng example

Const nrow As Integer = 2, ncol As Integer = 2
Dim i As Integer, j As Integer
Dim A(nrow, ncol) As Double, B(nrow, ncol) As Double
Dim C(nrow, ncol) As Double

A(1, 1) = 2: A(1, 2) = 3: A(2, 1) = 10: A(2, 2) = 20
B(1, 1) = 3: B(1, 2) = 1: B(2, 1) = -2: B(2, 2) = -100

Call addarrays((A), (B), (nrow), (ncol), (C))
MsgBox (C(1, 1) & "" & C(2, 2))

End Sub

Sub addarrays(W, X, nr, nc, Y)
Dim i As Integer, j As Integer

For i = 1 To nr 'nr = 2 so refers to dimension 2 of array
    For j = 1 To nc 'nc = 2
        Y(i, j) = W(i, j) + X(i, j)
    Next j
Next i

End Sub

Update: Based on the answer below, and after rewatching the tutorial and running the code with some alteration I realized that the main issue is that the combined array referenced as "C" shouldn't have parentheses on it. Array "C" is what is passed from the sub procedure "addarrays" to the main procedure. Apparently it doesn't matter if the input arrays "A" and "B" have parentheses, or at least it still results in the correct answer of (5 -80).

Upvotes: 0

Views: 258

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Call addarrays((A), (B), (nrow), (ncol), (C))

That statement is not doing what you think it does. It should look like this:

Call addarrays(A, B, nrow, ncol, C)

Or simpler even, like this:

addarrays A, B, nrow, ncol, C

Same with your MsgBox call:

MsgBox (C(1, 1) & "" & C(2, 2))

Should look like this:

MsgBox C(1, 1) & "" & C(2, 2)

This answer explains what's going on, but briefly what's happening is that by wrapping arguments with parentheses you are syntactically turning these arguments into expressions that VBA needs to evaluate before it can pass their result as arguments to the procedure being invoked. Often (e.g. when intrinsic data types are involved) it won't make a difference until you start experimenting with ByRef vs ByVal and then notice your ByRef parameter is behaving exactly as if it were defined ByVal, and the reason for that is none other than the extraneous parentheses at the call site.

Remove the extraneous parentheses, it should remove the problem.

Upvotes: 2

Related Questions