VBA Nested string If statement

Question

I have to create a Dashboard, column C would have different derivative products with their maturities known as the short name. Example IRS July 18.

There are 20 family names, sometimes, column c could have the trade short name in lower or upper characters.

I have created a nested if Statement which in part does work, it only fails if a certain character is in upper or lower case. I therefore have to add this variation to the if statement, for it to work. Secondly, it is not picking up short names where the product is not listed.

Can I do this in such a way that it only looks for the characters and ignores upper and lowercase.

My last else statement, if a shortname is added with a new family, I am keen for it to be picked up. any thoughts how I can update this?

Instead of giving the full list I kept it very small

Example

If DerivativeID Like "*irs *" Then
    DerivativeFamily = "Interest Rate Swap "
ElseIf DerivativeID Like "*cds *" Then
    DerivativeFamily = "credit default swap "
Else
    DerivativeFamily = "Add to List"
End If

Convert the test value to lower case then compare to lower case: if lcase$(DerivativeID) Like "*cds *" Then ... — Alex K.
– Alex K., Commented May 15, 2017 at 18:46

TesseractE · Accepted Answer · 2017-05-15 19:04:31Z

3

Sounds like a job for SELECT ... CASE. Try this:

DerivativeID = UCase(DerivativeID)

Select Case True
     Case DerivativeID like "*IRS*"
       DerivativeFamily = "Interest Rate Swap "
     Case DerivativeID like "*CDS*"
       DerivativeFamily = "Credit Default Swap "
     Case Else
       DerivativeFamily = "Add to List"
End Select

edited May 15, 2017 at 19:04

answered May 15, 2017 at 19:00

TesseractE

4254 silver badges13 bronze badges

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

3 Comments

TesseractE Over a year ago

Fixed both. Thanks! Keep forgetting the subtle differences between VB.NET and VBA. UCase and LIKE should work here instead of .ToUpper and .Contains.

Holger Over a year ago

With your approach, you would modify the original variable.

TesseractE Over a year ago

There was no indication of it being used anywhere else in the OP. I'm sure if the original string had to be used elsewhere in the program with its case intact, a new variable could be used for this bit.

Holger · Accepted Answer · 2017-05-15 19:14:33Z

2

Use just the LCase function:

If LCase$(DerivativeID) Like "*irs *" Then
    DerivativeFamily = "Interest Rate Swap "
ElseIf LCase$(DerivativeID) Like "*cds *" Then
    DerivativeFamily = "credit default swap "
Else
    DerivativeFamily = "Add to List"
End If

edited May 15, 2017 at 19:14

answered May 15, 2017 at 19:06

Holger

3301 silver badge10 bronze badges

Comments

Graham · Accepted Answer · 2017-09-21 18:11:11Z

2

Specify Option Compare Text at the top of the module. The default string comparison mode is Binary, which makes "A" not equal to "a" in string comparisons.

When doing direct comparisons using the = operator, you can also use the StrComp function from the VBA.Strings module, to override the Option Compare setting:

Debug.Print StrComp("A", "A", vbBinaryCompare) 'prints 0
Debug.Print StrComp("A", "a", vbBinaryCompare) 'prints -1

Debug.Print StrComp("A", "A", vbTextCompare) 'prints 0
Debug.Print StrComp("A", "a", vbTextCompare) 'prints 0

As other answers suggest, you could also convert your string's casing. However in doing that you should be aware of a few things:

If comparing very large strings multiple times (e.g. in a loop), performance will be degraded
If using LCase or UCase, you're using a Variant-returning alias, which incurs additional implicit type conversion overhead which can easily be avoided by using their String-returning counterparts, LCase$ and UCase$.

edited Sep 21, 2017 at 18:11

Graham

7,86020 gold badges67 silver badges92 bronze badges

answered May 15, 2017 at 18:56

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

2 Comments

Holger Over a year ago

Good Alternative, but this would changing the comparison behaviour for the whole module, which might be unintended.

Mathieu Guindon Over a year ago

@Holger correct. and in well-designed code, there's not 200 things going on in the same module either.

Roxanne · Accepted Answer · 2017-05-15 18:52:59Z

1

Try the LCase or UCase functions

Something like:

Set DerivativeID = LCase(DerivativeID)

if...Then

answered May 15, 2017 at 18:52

Roxanne

851 silver badge5 bronze badges

3 Comments

Mathieu Guindon Over a year ago

The Set keyword is used for assigning object references; a string isn't an object reference.

user3287522 Over a year ago

These are very good responses, but I want it to ignore the upper and lower cases, but focus more on the characters, as long as it matches the characters it should be successful. As I have created a counter so it loops through each of the cells from column c into the if statement and gives an output regarding the derivative family.

user3287522 Over a year ago

Everyone thank you so much for your comments, I just came across something which has solved my issue, Option Compare Text

Collectives™ on Stack Overflow

VBA Nested string If statement

4 Answers 4

3 Comments

Comments

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related