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

Question

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

Everything declared inside a procedure scope comes into existence when the procedure is entered, and vanishes into the void when execution exits the procedure - if you need something to stick around between two procedure calls, you can declare it outside the procedure scope at module level (e.g. have `Private something As Whatever` at the top of the module, just under `Option Explicit`), and then every procedure in that module can access it. — Mathieu Guindon, Aug 15 '20 at 14:57
Or, if you need a procedure to "remember" the state of its local variables between calls, you can use the `Static` keyword (instead of `Dim`) to declare them. — Mathieu Guindon, Aug 15 '20 at 14:59
That said I suspect the `Call addarrays` statement is actually blowing up with a run-time error (or would be, if the `addarrays` parameters were typed), because the parentheses around the arguments `(A)`, `(B)`, `(nrow)`, `(ncol)`, and `(C)` are being actively harmful here. Get rid of these parentheses, I haven't seen your tutorial video but I'm pretty sure it doesn't have them. — Mathieu Guindon, Aug 15 '20 at 15:01
[Arg is forced to be passed by value not by reference](https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/procedures/how-to-force-an-argument-to-be-passed-by-value). That prevents `C `to be used as result. Try `Call addarrays(A, B, nrow, ncol, C)`. — ComputerVersteher, Aug 15 '20 at 15:05
In addition to @MathieuGuindon suggestions read about [Variables](https://riptutorial.com/vba/example/2957/variables) and [arrays](https://riptutorial.com/vba/topic/3064/arrays), also the site contains several other vba related sections with excellent explanations, read them all! — ComputerVersteher, Aug 15 '20 at 15:16
The question title is a bit misleading - it makes it sound like you intend to retain the values between calls to `main`, but if I'm understanding correctly then what you're describing is that the work done in `addarrays` is being discarded and the `MsgBox` isn't showing the expected values, right? — Mathieu Guindon, Aug 15 '20 at 15:51
Rewatching the video it looks like he didn't have parentheses on the combined array which is the array that was passed from the adding sub procedure to the main procedure. The input arrays "A" and "B" had parentheses however. I don't know why that was necessary. (See video at 4:23). — ayy llama, Aug 15 '20 at 16:40

score 2 · Accepted Answer · answered Aug 15 '20 at 15:19

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.

Thanks Mathieu that did the trick. The funny thing is I don't normally wrap arguments with parentheses in my own code when trying to pass values but in the tutorial that is what the author did so I thought there was a reason for that. I appreciate the link as well. — ayy llama, Aug 15 '20 at 16:33

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

1 Answers1