VBA Excel Performing operations on two arrays

Question

I'm having trouble with a for loop in my program. Right now I have two arrays that are populated with arbitrary numbers. These two arrays are the same size. Basically I want the user to specify what sort of operation they want done on the arrays and then I'll perform them (given that the arrays are the same dimensions). Below is the code for me trying to populate an answer array with the difference between the original two arrays:

If (LCase(diffOrPercent = "difference")) Then
    For iRow = 1 To totalRow
        For iCol = 1 To totalCol
            answerArray(iRow, iCol) = s2Array(iRow, iCol) - s3Array(iRow, iCol)
        Next iCol
    Next iRow
End If

The problem that I'm having is that the answerArray is entirely blank when I try to print it out. Does anyone know what's going on?

Note: The same error occurs if the for loops go from 0 to end - 1, when i put the following:

answerArray(iRow, iCol) = s2Array(iRow, iCol).Value2 - s3Array(iRow, iCol).Value2

As well as when I put Value instead of Value2.

Thank you,

Jesse Smothermon

did you verify that there are in fact values in your s2Array and s3Array? What are the array's datatypes? Use the local's window to see what values are being used and assigned to the array. — Fink
– Fink, Commented Mar 10, 2011 at 20:14
yes, i have another for-loop that spits out the answers and both s2Array and s3Array have the correct values in their cells. the datatypes for now are just ints, but I guess there is potential for doubles to get involved. Right now I just declared the three arrays as Dim s2Array() and so on — Jesse Smothermon
– Jesse Smothermon, Commented Mar 10, 2011 at 20:18
Actually, I just did a test where I performed this expression on ActiveCells instead of making an answerArray and the answer came out correctly. I'd still like to know what the problem is here though because it's bugging me. Thanks you — Jesse Smothermon
– Jesse Smothermon, Commented Mar 10, 2011 at 20:21
@David At declaration just Dim s2Array() Dim s3Array() Dim answerArray()... later in body of code s2Array = ActiveSheet.Range("A1").CurrentRegion, same with s3Array and answerArray — Jesse Smothermon
– Jesse Smothermon, Commented Mar 10, 2011 at 20:28

Jesse Smothermon · Accepted Answer · 2011-03-10 20:41:27Z

2

I figured it out, the if statement was written incorrectly so the operation portion of the code wasn't even being hit. I screwed up the parenthesis on LCase, so the correction is below

If (LCase(diffOrPercent) = "difference") Then
    For iRow = 1 To totalRow
        For iCol = 1 To totalCol
            answerArray(iRow, iCol) = s2Array(iRow, iCol) - s3Array(iRow, iCol)
        Next iCol
    Next iRow
End If

This makes it so that the variable diffOrPercent will be all lowercase instead of..... well I don't think anything happens when my original if statement is implemented which makes sense because nothing within the if statement gets hit. Sorry that I completely wasted everyone's time on this one, but I really appreciate all of the quick responses

answered Mar 10, 2011 at 20:41

Jesse Smothermon

1,05110 gold badges25 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Todd Main Over a year ago

Great to hear it is sorted. You can accept your own answer by clicking the hollow checkmark next to the up/down arrows on this answer.

Jesse Smothermon Over a year ago

@Otaku, I will do that once they allow me too. Apparently you can't accept your own answer within the first two days of you posting it. Most likely has something to do with the reputation

mwolfe02 · Accepted Answer · 2011-03-10 20:43:16Z

0

The first thing that jumps out is that you are assuming arrays with a starting index of 1. Unless you have explicitly stated that somewhere (either when declaring the array or with Option Base 1) then the first item in the array will have an index of 0.

If you won't know the array bounds until runtime it is usually safer to use LBound and UBound functions to determine the bounds programmatically:

If (LCase(diffOrPercent) = "difference") Then
    For iRow = LBound(s2Array, 1) To UBound(s2Array, 1)
        For iCol = LBound(s2Array, 2) To UBound(s2Array, 2)
            answerArray(iRow, iCol) = s2Array(iRow, iCol) - s3Array(iRow, iCol)
        Next iCol
    Next iRow
End If

Please note that I am assuming here that s2Array, s3Array, and answerArray all have identical dimensions. Thus I am only checking the bounds of s2Array.

edited Mar 10, 2011 at 20:43

answered Mar 10, 2011 at 20:27

mwolfe02

24.3k10 gold badges102 silver badges174 bronze badges

4 Comments

Jesse Smothermon Over a year ago

It is the correct assumption that they are all the same dimensions. I got the same blank output, so I'm starting to think that my if statement is wrong, which would mean that it's not even hitting the operation part of the code, when I pushed it through the debugger diffOrPercent has the value of "Difference".... but..... that shouldn't mess it up right, because of the LCase?

Jesse Smothermon Over a year ago

Okay.... that was totally the problem, I ran it again with the user inputting "difference" instead of "Difference" and it worked beautifully. But I thought LCase was suppose to make it so the program wouldn't worry about if something was capitalized. Does it work differently than what I'm thinking?

David Heffernan Over a year ago

(LCase(diffOrPercent) = "difference")!!

Jesse Smothermon Over a year ago

@David haha yeah I found that too, had to shuffle through my earlier projects for that one. Thank you for the quick responses

Collectives™ on Stack Overflow

VBA Excel Performing operations on two arrays

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related