I’m writing some code to turn the contents of class modules into an XML file for Affordable Care Act compliance purposes. The XML file spec says that my flag for whether the dependent is a spouse is “Y” or “N”. In my class, I have a Relation property that can be “Son”, “Daughter”, or “Spouse”. I made a new property to return the “Y” or “N”.
|
1 2 3 4 5 6 7 8 9 |
Public Property Get IsSpouseXML() As String If Me.Relation = "Spouse" Then IsSpouseXML = "Y" Else IsSpouseXML = "N" End If End Property |
I hate writing all those lines to convert a Boolean into something else. I know it’s not that big of a deal, but it just bugs me. So I fixed it.
|
1 2 3 4 5 |
Public Property Get IsSpouseXML() As String IsSpouseXML = Split("N Y")(Abs(Me.Relation = "Spouse")) End Property |
Now that’s fancy. The comparison is made and the True or False is converted to a Long via the Abs() function (to turn True to 1 instead of -1) and the proper element of the array is selected. It’s still not good enough.
|
1 2 3 4 5 6 7 8 9 10 11 |
Public Property Get IsSpouse() As Boolean IsSpouse = Me.Relation = "Spouse" End Property Public Property Get IsSpouseXML() As String IsSpouseXML = Split("N Y")(Abs(Me.IsSpouse)) End Property |
Yeah, that’s better. But it’s so specific to spouses. Spouse is a dependent that gets special attention, so I don’t mind having a dedicated property to it. It’s appropriate for the domain, I think. But if I wanted to really generalize the hell out of it, I might make an IsRelation property and then take my conversion property into a function.
|
1 2 3 4 5 6 7 8 9 10 11 |
Public Property Get IsRelation(ByVal sRelation As String) As Boolean IsRelation = Me.Relation = sRelation End Property Public Function ConvertBool(bValue As Boolean, vArr As Variant) As String ConvertBool = vArr(Abs(bValue)) End Function |
Now I can have complete customization of the return string.
|
1 2 3 4 5 6 7 8 9 |
Public Sub TEST_IsSpouse() Dim clsDep As CDependent For Each clsDep In gclsEmployees.Employee(4).Dependents Debug.Print ConvertBool(clsDep.IsRelation("Spouse"), Array("Not so much", "Of course")), clsDep.Relation Next clsDep End Sub |
Improving the readability of the code is what matters for me. I’m sorry, but these one-liners code are awful to read and maintain, never alone if more people work on the development of the project.
I think that snb and Rick Rothstein are big fans of over complicated (at least for me) one line codes. I never use them in real world.
I seem to recall there was some issue about Iif() but I can’t see any harm in using it here:
IsSpouseXML = IIf(Me.Relation = “Spouse”, “Y”, “N”)
…or if we do still have that issue (what the heck was it? Was it something about always evaluating the true and false outcomes?) then maybe we could use Choose()?
IsSpouseXML = Choose(CInt(Me.Relation = “Spouse”) + 2, “Y”, “N”)
VBA is your friend:
Readability is dependent of reading habits/abilities.
Every mathamatician is used to read from top to bottom, from left to right, from right to left and from inside to outside.
The last one has to be practised.
For (mine) readability’s sake I therefor prefer oneliners.
alternatively:
snb: The format trick is neat, but Windows regional dependent. For me it returns Ja, Nee rather than Yes or No. Not necesarily a bad thing, but one to keep in mind when developing stuff that perhaps leaves your front door :-)
Not all coders are mathematicians. I dare to say the majority probably isn’t. Especially VBA coders, which have all sorts of backgrounds as these are often information workers trying to automate the tedious and repetitive parts of the real-life jobs. They benefit greatly from straightforward and easy to understand code.
That’s why VBA coders shouldn’t improve only their coding skills but also their reading skills.
=REPT(“No”,A1″Spouse”)&REPT(“Yes”,A1=”Spouse”)
We could translate this to VBA in several ways like using EVALUATE
In Excel:
=MID(“NY”;1+(A20=”Spouse”);1)
in VBA:
I agree with Mike, IIF is ideal for this – one-liner, easy to understand for all types of coders, and certainly would perform faster than all the other alternatives
“and certainly would perform faster than all the other alternatives”
Maybe. We have to keep in mind that IIf evaluates both true and false arguments, wheter the logical test results.
In snb’s VBA code… is A20 supposed to be a cell reference like it was in the Excel formula? If so, it is not referenced correctly. It can be written this way though…
MsgBox Mid(“NY”,1-[A20=”Spouse”],1)
Note that I dumped the Abs function call… since the only two values are -1 or 0, simply negating the result works. I guess since we are evoking the shortcut notation for the Evaluate function, and since MID is both a VBA and Excel function, we can extend that notation to cover the entire calculation…
MsgBox [MID(“NY”,1+(A20=”Spouse”),1)]
Another way to write it in VBA is like this…
MsgBox Split(“N Y”)(-[A20=”spouse”])
And as for the earlier comments about one-liner readability, tell me, what is so hard about reading this version of the above{grin}, and yes, it works…
MsgBox Split(“N Y”)(Split(Trim(Partition(-[A20=”spouse”],0,1,1)), “:”)(0))