×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Using A Boolean in a Calc, and setting a variable = "0"3

## Using A Boolean in a Calc, and setting a variable = "0"

(OP)
I was working through some function code I found to calculate molecular weights, located here: https://stackoverflow.com/questions/27050976/calcu...

The part that has me puzzled is this expression:
Int(sSB) - (Not CBool(Int(sSB)))

I set up a test and it's got me puzzled. How does (0 - True) give a value of 1? If True = 1, wouldn't the result be -1?

#### CODE --> VBA

Sub IntCBool()
Dim A, B, C, D, Check
A = 0
B = Int(A)
C = Not CBool(Int(A))
D = B - C
Debug.Print "A:", A
Debug.Print "B:", B
Debug.Print "C:", C
Debug.Print "D: ", B & " - " & C & " = " & D
End Sub

Results:
A:             0
B:             0
C:            True
D:            0 - True = 1 

The other part that has me curious is why the author initializes the variable sSB = "0", rather than sSB = 0. Obviously, it was declared as a string, but... why? sSB is used to store the number of atoms for a particular element in the compound. Why wouldn't it just be set to an integer? I recognize that there's more than one way to code something like this, and maybe it's just the author's preference.

This bit of code is fascinating to me, how the author did what s/he did. One item that was really nifty was how they determined if a character was upper or lowercase, using the Asc function.

Thanks!!

Matt

### RE: Using A Boolean in a Calc, and setting a variable = "0"

>How does (0 - True) give a value of 1? If True = 1, wouldn't the result be -1?

The internal representation of True in the VB suite of languages is -1

>why the author initializes the variable sSB = "0", rather than sSB = 0. Obviously, it was declared as a string, but... why? sSB is used to store the number of atoms for a particular element

Because the code is building the number up by reading the digits one at a time. The easiest way to do this is by concatenating them in a string, and then evaluating the string once you have completed the concatenation

>I recognize that there's more than one way to code something like this

Indeed!. here, have a Regular expressioin version I quickly knocked together ...

#### CODE

' Requires a reference to to VbScript Regular Expressions
Public Function udf_Molecular_Weight2(sCMPND As String) As Double
Dim result As MatchCollection
Dim element As Match

With New RegExp
.Pattern = "([A-Z]+?[a-z]{0,1})(\d*)"
.Global = True
Set result = .Execute(sCMPND)
End With
For Each element In result
udf_Molecular_Weight2 = udf_Molecular_Weight2 + Application.VLookup(element.SubMatches.Item(0), ThisWorkbook.Names("tblPeriodic").RefersToRange, 4, False) * IIf(element.SubMatches.Item(1) = "", 1, element.SubMatches.Item(1))
Next
End Function 

Mind you, since the original author seems to pride themselves on 'writ[ing] fairly tight code' (although I'd disagree that sticking all your declarations into one line counts as tight code, and just servers to make things a little morte difficult to read), here's a shorter version:

#### CODE

' Requires a reference to to VbScript Regular Expressions
Public Function udf_Molecular_Weight3(sCMPND As String) As Double
Dim element As Match
With New RegExp
.Pattern = "([A-Z]+?[a-z]{0,1})(\d*)"
.Global = True
For Each element In .Execute(sCMPND)
udf_Molecular_Weight3 = udf_Molecular_Weight3 + Application.VLookup(element.SubMatches.Item(0), ThisWorkbook.Names("tblPeriodic").RefersToRange, 4, False) * IIf(element.SubMatches.Item(1) = "", 1, element.SubMatches.Item(1))
Next
End With
End Function 

### RE: Using A Boolean in a Calc, and setting a variable = "0"

(OP)
That's astounding that you cooked that up so quickly. I am nearly speechless lol. Wish I had that kind of skill! Thank you for explaining what's going on with the boolean and the sSB initialization. For what it's worth, I set it to sSB=0 and it didn't seem to affect the results at all but I haven't tested it extensively.

I had the hardest time using that Application.VLookup part of the code. I copied the periodic table from Wikipedia and the element name was in the 2nd column which wouldn't work with VLookup and the table I named "tblPeriodic".

I guess this should be in a separate thread, but I simply couldn't find any code that would allow me to give a value from any named column, or a known column even, and then send me back the value from another named column.

Like, thinking out loud, it would be something like:

#### CODE --> Matts-Fake-VBA

RowNumber = Find("Ar",Range("tblPeriodic[#Elements]")
MolWt = Range("tblPeriodic[#Molwts]",RowNumber] 

I got very used to doing Index/Match inside a spreadsheet and I didn't think to try it with this bit of code. Perhaps that would have been a better idea?

You should see what I have in my spreadsheet now tho, soooooo many comments, debug.print statements, nearly something for each line that describes what is happening, heh. Like you said, his "tight code" just made it hard for a n00b like me to understand.

Thanks!!

Matt

### RE: Using A Boolean in a Calc, and setting a variable = "0"

>I set it to sSB=0 and it didn't seem to affect the results at all

That's because VB is holding your hand, and doing a bunch of implicit type conversion behind your back.

Taking the line in question: sSB = sSB & Int(Left(sTMP, 1))

So, calculation happens before assignment. And in your case you have (presumably) defined sSB to be Long or an Integer. We'll assume Long for this expose ...

So VB, working left to right of teh assignment operator (somewhat of a simplification of what actually happens):

sSB - uh, OK that's a long, no problem.
& - Ah, that's a string concatenation operator. Uh oh. I'd better convert sSB into a string.
Int(Left(sTMP, 1)) - Ooh, that's a numeric as well. But we are doing string concatenation, so better convert it into a string

Ok, now let's assign the string result to sSB. Huh ... sSB is a Long, so I'd better convert the result into a Long so that I can do that assignment

So you see, lots of conversions going on in the background, including one completely unnecessary one - the Int in Int(Left(sTMP, 1)) is superfluous (and I suspect a remnant of an earlier draft of the code), it would be better simply left as Left(sTMP, 1)

### RE: Using A Boolean in a Calc, and setting a variable = "0"

As you stated, "I got very used to doing Index/Match"...

=INDEX([tblPeriodic[#Molwts]],MATCH("Ar",[tblPeriodic[#Elements]],0),1)

I'd also suggest using Data > Validation > LIST to Select the Element and reference that in your MATCH lookup value.

Expressing kudos to the always insightful, impressive and regular guy, strongm, for truely tight code.

Skip,

Just traded in my OLD subtlety...
for a NUance!

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

### RE: Using A Boolean in a Calc, and setting a variable = "0"

(OP)
Hey Skip, before this discussion on the periodic table, I was unaware of the 'WorksheetFunction' capability of VBA. So finally following up on your post, I threw this together after a bit of trial and error:

#### CODE --> VBA

Public Sub Testing123()

Dim Var1, Var2, Var3
Var1 = WorksheetFunction.Match("Ar", [tblPeriodic[Symbol]], 0)
Var2 = WorksheetFunction.Index([tblPeriodic[At.wt]], Var1, 1)

Debug.Print Var2

End Sub 

And of course, it works perfectly.

In your expert opinion, is using 'WorksheetFunction' lazy programming in some/most instances? It definitely achieves what I would need in this case, but is there a "better" or "more accepted" way of accomplishing an Index/Match from within VBA? Just curious what your thoughts are.

Thanks!!

Matt

### RE: Using A Boolean in a Calc, and setting a variable = "0"

I look at is as whatever works.

In most cases, I'm not gonna export the code to another application other than Excel, so it doesn't matter to me.

You can use Find. If you have a Row then you also can get the Intersection of the Riw and the Column to return a value. Always like using Named Ranges and Structured Table references.

Skip,

Just traded in my OLD subtlety...
for a NUance!

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!