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

[MattGreer]

I was working through some function code I found to calculate molecular weights, located here:

https://stackoverflow.com/questions/27050976/calculating-molecular-weight-using-excel

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?

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.

Thank you for your help!

Thanks!!


Matt

 

[strongm]

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

[blue][COLOR=green]' Requires a reference to to VbScript Regular Expressions[/color]
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[/blue]

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:

[blue][COLOR=green]' Requires a reference to to VbScript Regular Expressions[/color]
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[/blue]

 

[MattGreer]

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:

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

 

[strongm]

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

[tt]sSB[/tt] - uh, OK that's a long, no problem.
[tt]&[/tt] - Ah, that's a string concatenation operator. Uh oh. I'd better convert [tt]sSB[/tt] into a string.
[tt]Int(Left(sTMP, 1))[/tt] - 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 [tt]sSB[/tt]. Huh ... [tt]sSB[/tt] 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 [tt]Int[/tt] in [tt]Int(Left(sTMP, 1))[/tt] is superfluous (and I suspect a remnant of an earlier draft of the code), it would be better simply left as [tt]Left(sTMP, 1)[/tt]

 

[SkipVought]

As you stated, "I got very used to doing Index/Match"...
[tt]
=INDEX([tblPeriodic[#Molwts]],MATCH("Ar",[tblPeriodic[#Elements]],0),1)
[/tt]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

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:

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.

Thank you for your feedback!

Thanks!!


Matt

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein