Compare String Arrays VBA

Question

I have a 2 dimensional string array. In the string array are some strings and some integer numbers. I want to compare two positions of the array together.

Example:

If StringArray(1, 5) > StringArray(1, 6) Then
    MsgBox "Is first number is bigger"   
End if

My Debugger says:

StringArray(1,5) = 150
StringArray(1,6) = 3 
StringArray(1, 5) > StringArray(1, 6) = False

Does someone have an idea, why the Result is false? StringArray(1, 5) is definitely bigger. What could be the problem? Does it have something to do with the array type and how can i change that without creating a new integer array just for 2 Values?

The problem could be that 150 and 3 are being treated as strings. Therefor 3 > 1. So just cast them to Integer before comparing. — Paul Karam
– Paul Karam, Commented Apr 18, 2018 at 8:46
@PaulKaram Thanks for the answer. For everybody who doesnt know that: Val() function is for change a string Value to Integer Val(StringArray(1,5)) — Patrick Schöpfer
– Patrick Schöpfer, Commented Apr 18, 2018 at 8:50

Paul Karam · Accepted Answer · 2018-04-18 10:59:22Z

2

As you stated, your array type is String.
When you're comparing StringArray(1,5) to StringArray(1,6), you are actually comparing strings.

In your case, you are comparing 150 to 3 as strings. Hence the result is false, because "3" is > "1" and not vice verca.

In order to have it compare them as integers, you need to cast them to integers before. Like this:

If Val(StringArray(1, 5)) > Val(StringArray(1, 6))

There's other ways such as: CInt.

edited Apr 18, 2018 at 10:59

answered Apr 18, 2018 at 8:49

Paul Karam

4,2908 gold badges36 silver badges59 bronze badges

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

4 Comments

Patrick Schöpfer Over a year ago

Thanks. Val() does the same as i know

Paul Karam Over a year ago

@PatrickSchöpfer Yeah it does the same and more. I included three links of other ways as well.

Vityata Over a year ago

Convert.toInt32 and the "many other ways" link are non-VBA related. This is .Net

Paul Karam Over a year ago

@Vityata Good point. I usually don't follow VBA so thing question must have been tagged with VB.NET before being edited. Anyways, I fixed that.

Collectives™ on Stack Overflow

Compare String Arrays VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related