Cleaning Up My JoinRange Arguments

I’m trying to make my JoinRange function better and I’m failing miserably. A few years ago I added a “macro” argument because I was making so many HTML and Trac tables. I don’t use Trac anymore and I almost never make HTML tables (because I blog so infrequently, I guess). I got rid of that argument. The reason I join ranges most often is to create a big

clause in SQL. Let’s say I have this list of customer IDs and I want to make an

clause.

38
142
146
175
214
217

I’d use JoinRange like

That’s a freakin’ mess. The second argument is the now-defunct macro argument and is blank. The rest of the arguments are

3rd (delimeter): single quote, comma, single quote
4th (beginning): open paren, single quote
5th (ending): single quote, close paren

and I’d get

which I could paste into my SQL statement and roll. I hate typing those arguments. Worse, I hate reading those arguments. It’s pretty hard to read in this blog, but it’s worse in Excel’s formula bar. I thought if I could get rid of the single quotes, it would be cleaner. I rewrote the code to add a Quote argument that would wrap every entry in whatever quotes I supplied.

Now, my formula looks like this:

I think we can all agree that this is no better than what I had before. I thought the quotes were the problem, but it’s also that I use a comma as the delimiter and it’s the thing that separates the arguments. If I change it to pipe delimited…

Nope. It’s still a headache to read. Based on the number of comments to this post, I’m pretty sure none of you are using predefined names in your book.xlt file. But I do. And If I’m using a workbook that I created, I could use

That’s definitely more readable to me. I guess I need a macro to add those names to any books automatically so I can use them.

I’m not crazy. I swear this all makes sense in my head. Plus, if you’ve read this far, you’re probably crazy too.

Anagrams and Palindromes

More Java homework:

First, I remove all the spaces. Then I make sure the two words are the same length. Then I loop through all the letters in the first word, find them in the second word, and replace them with a plus sign. If the second word is all plus signs at the end, then it’s an anagram. My first thought was to put the letters in an array and sort them, but that’s too much looping.

Nothing too fancy here. Again, I remove all the spaces. Then I compare the first letter to the last letter, the second letter to the penultimate letter, and so on. If there’s every not a match, set the return value to False and quit looking.

MaxMinFair Rewrite

I read Charles William’s MaxMinFair algorithm and I didn’t like his approach. That’s typical. I’ll read somebody’s code and think “They’re making that too hard”. Then I’ll set about rewriting it. In this case, as in most cases, it turns out that it is that hard, but I wasn’t going to convince myself until I tried it. I ended up with a different approach that’s not shorter, not easier to read, and not easier to follow. Oh well, here it is anyway.

In Charles’s implementation, he allocates an equal amount of the supply to each node, then takes back what that node didn’t need and puts it back in the available pool. When I was looking at the results, I was thinking that the smallest n nodes simply get their demand and only when there’s not enough to go around do we need to do something different than allocate the full demand.

In my implementation, I start by giving everyone what they demand. Then I start with the smallest demand, and if I can accommodate that amount for everyone, I just reduce the amount available and move to the second smallest demand. At some point (the sixth smallest demand in Charles’s data) I can’t meet that demand and still give everyone an equal share. At that point, I give anyone who hasn’t had their demand met an equal amount – the amount that’s already been distributed plus an equal share of what’s left.

Rank Demand Incremental Demand Allocated Remaining
        18.30
7 0.70 0.70 4.90 13.40
6 1.00 0.30 1.80 11.60
5 1.30 0.30 1.50 10.10
4 2.00 0.70 2.80 7.30
3 3.50 1.50 4.50 2.80
2 7.40 3.90 7.80 (5.00)
1 10.00 2.60 2.60 (7.60)

In the first iteration, I hand out 0.70 to everyone because I have enough supply to do that. In the second iteration, I had out the differential, 0.30, to everyone who’s left because I have enough supply remaining. When I get to #2, I can’t hand out 3.90 to the remaining two nodes because I don’t have enough supply. I’ve allocated up to 3.5 to anyone who’s demanded it, so the last two get the 3.5 plus half of the 2.8 that remains.

Although I didn’t accomplish anything, it was still a fun exercise.

Multiple Substitute UDF

Have you ever written this formula?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A2),"AND","")," INC","")," LLC","")," LTD","")," DBA","")," ",""),".",""),",",""),"&",""),"-",""),"/",""),"'","")

I just did. It gets the job done, but it stinks. Here’s its replacement.

=subst(UPPER(A3),""," AND "," INC"," LLC"," LTD"," DBA"," CO"," ",".",",","&","-","/","'")

That’s a little better (assuming it works). On a side note, I wish Excel had built-in constants for formulas, so the above formula would look like this.

=subst(UPPER(A3),xlNULLSTRING," AND "," INC"," LLC"," LTD"," DBA"," CO",xlSPACE,".",xlCOMMA,"&","-","/",xlSINGLEQ)

Maybe I’ll create a Sheet template with those names defined. Or is that better in a Book template? Anyway, here’s the code for the poorly named Subst function.

Public Function Subst(text As String, NewText As String, ParamArray OldText() As Variant) As String

Dim vItem As Variant
Dim sReturn As String
Dim vArray As Variant

sReturn = text

vArray = OldText
BubbleSortLen vArray

For Each vItem In vArray
sReturn = Replace(sReturn, vItem, NewText, , , vbTextCompare)
Next vItem

Subst = sReturn

End Function

Public Sub BubbleSortLen(ByRef vArray As Variant)

Dim i As Long, j As Long
Dim sTemp As String

For i = LBound(vArray) To UBound(vArray) - 1
For j = i To UBound(vArray)
If Len(vArray(j)) > Len(vArray(i)) Then
sTemp = vArray(i)
vArray(i) = vArray(j)
vArray(j) = sTemp
End If
Next j
Next i

End Sub

The ParamArray argument takes as many arguments as you want to throw at it. For some reason, I couldn’t pass OldText by reference to the sorting procedure, so I had to copy it to another variable first. I sort the terms by length so that “corporation” gets replace before “corp”. Otherwise, I’ll be left with “oration”, which is just silly.

Once sorted, I simply replace all of the old with the one new, and return the string. It worked well for the one application I’ve used it for and it was a heck of a lot easier to update. Thoughts?

JoinRange Update

I use the heck out the JoinRange function I wrote a few years back. The vast majority of the time I’m using it for two purposes: creating a table in a Trac wiki page or creating an HTML table. I’ve been typing those same delimiters over and over and it has to stop.

I add an optional sMacro argument as the first argument. I can fill this argument with some predefined terms and it will create the necessary delimiters. And as long as I was in there, I change the range looping to array looping. Here’s what it looks like now.

'---------------------------------------------------------------------------------------
' Procedure : JoinRange
' Author : dick
' Date : 3/31/2012
' Purpose : Concatenate cell values with delimiters and line ends
' Args : sMacro - preset delimeters, overrides other arguments
' sDelim - text inserted between cell values
' sLinestart - text inserted before the first cell value
' sLineEnd - text inserted after the last cell value
' sBlank - text used instead of nothing for blank cells
'---------------------------------------------------------------------------------------
'
Public Function JoinRange(rInput As Range, _
Optional sMacro As String = "", _
Optional sDelim As String = "", _
Optional sLineStart As String = "", _
Optional sLineEnd As String = "", _
Optional sBlank As String = "") As String

Dim sReturn As String
Dim vaValues As Variant
Dim i As Long, j As Long

Select Case UCase(sMacro)
Case "HTMLTABLE", "HTML TABLE"
sDelim = " "
sLineStart = "

"
sLineEnd = "

"
Case "TRACTABLE", "TRAC", "TRAC TABLE"
sDelim = "||"
sLineStart = "||"
sLineEnd = "||"
End Select

vaValues = rInput.Value
sReturn = sLineStart

For i = LBound(vaValues, 1) To UBound(vaValues, 1)
For j = LBound(vaValues, 2) To UBound(vaValues, 2)
If Len(vaValues(i, j)) = 0 Then
sReturn = sReturn & sBlank & sDelim
Else
sReturn = sReturn & vaValues(i, j) & sDelim
End If
Next j
Next i

sReturn = Left$(sReturn, Len(sReturn) - Len(sDelim))

sReturn = sReturn & sLineEnd

JoinRange = sReturn

End Function

Abigail Taylor Coral Springs
Bryan Burns Charlotte
Trinity Wallace Clarksville
Arianna Reynolds Elizabeth
Gabriella Roberts Providence
Katherine Foster Miami
Megan Hunt Toledo
Diego Black Garland

Oh, I’m going to save so much typing.

Using HTML5 Fractions in Exported Excel Tables

HTML5 provides for fractional representation of halves, thirds, fourths, fifths, sixths, no sevenths, and eighths. Excel has a fractional number format. This post is about bringing these concepts together for exporting an Excel table into Wiki or HTML designs. The basic representations are:

Name Hex Dec Result
½ U+000BD 189 ½
⅓ U+02153 8531
¼ U+000BC 188 ¼
⅕ U+02155 8533
⅙ U+02159 8537
⅛ U+0215B 8539
⅔ U+02154 8532
⅖ U+02156 8534
¾ U+000BE 190 ¾
⅗ U+02157 8535
⅜ U+0215C 8540
⅘ U+02158 8536
⅚ U+0215A 8538
⅝ U+0215D 8541
⅞ U+0215E 8542

 
The format for the name is &fracnd; where n is the numerator and d is the denominator. Thus ½ is a half, and ⅞ is seven-eighths. The HTML code representations for these are:

Result Named
Code
Hex
Code
Dec
Code
½ ½ ½ ½
⅓ ⅓ ⅓
¼ ¼ ¼ ¼
⅕ ⅕ ⅕
⅙ ⅙ ⅙
⅛ ⅛ ⅛
⅔ ⅔ ⅔
⅖ ⅖ ⅖
¾ ¾ ¾ ¾
⅗ ⅗ ⅗
⅜ ⅜ ⅜
⅘ ⅘ ⅘
⅚ ⅚ ⅚
⅝ ⅝ ⅝
⅞ ⅞ ⅞

 
In theory (more on “in practice” later) every representation in a row is equivalent. This is our test table to export to Wiki or HTML format:

D E F G H I J K L
1 1/1 1/2 1/3 1/4 1/5 1/6 1/7 1/8 1/80
2 2/1 2/2 2/3 2/4 2/5 2/6 2/7 2/8 2/8.
3 3/1 3/2 3/3 3/4 3/5 3/6 3/7 3/8 3/8A
4 4/1 4/2 4/3 4/4 4/5 4/6 4/7 4/8 6 4/8
5 5/1 5/2 5/3 5/4 5/5 5/6 5/7 5/8 7 5/8
6 6/1 6/2 6/3 6/4 6/5 6/6 6/7 6/8 8 6/8
7 7/1 7/2 7/3 7/4 7/5 7/6 7/7 7/8 9 7/8
8 8/1 8/2 8/3 8/4 8/5 8/6 8/7 8/8 10 8/8

 
Cell D1: =CHAR(32)&ROW()&”/”&COLUMN()-3, then fill down and right. The right hand column has a few test cases. I used that formulaic construction to keep Excel from doing the divisions. The Excel fractional format is “# ?/?” for single digit denominators. The intermediary space is important. It indicates that a fraction may follow, just as a forward slash indicates a fraction may be present. And a format of /?? is a fraction not translatable into HTML5. Turning these patterns into VBA, this is my MakeFracs() function. It checks that there is a slash, then that there is not a slash–digit–digit pattern, and finally that there is a “space–digits 1 through 7–slash–digits 2, 3, 4, 5, 6, 8” pattern to screen out sevenths and ninths. If all of those pass, it substitutes in the &fracnd; formulation for the fraction.

Function MakeFracs(Arg As String) As String
Dim sIN As String
Dim sOUT As String
Dim i As Long, j As Long
Dim n As Long, d As Long
Dim Fracs(1 To 15, 1 To 3) As String

Fracs(1, 1) = "½": Fracs(1, 2) = "½": Fracs(1, 3) = "½"
Fracs(2, 1) = "⅓": Fracs(2, 2) = "⅓": Fracs(2, 3) = "⅓"
Fracs(3, 1) = "¼": Fracs(3, 2) = "¼": Fracs(3, 3) = "¼"
Fracs(4, 1) = "⅕": Fracs(4, 2) = "⅕": Fracs(4, 3) = "⅕"
Fracs(5, 1) = "⅙": Fracs(5, 2) = "⅙": Fracs(5, 3) = "⅙"
Fracs(6, 1) = "⅛": Fracs(6, 2) = "⅛": Fracs(6, 3) = "⅛"
Fracs(7, 1) = "⅔": Fracs(7, 2) = "⅔": Fracs(7, 3) = "⅔"
Fracs(8, 1) = "⅖": Fracs(8, 2) = "⅖": Fracs(8, 3) = "⅖"
Fracs(9, 1) = "¾": Fracs(9, 2) = "¾": Fracs(9, 3) = "¾"
Fracs(10, 1) = "⅗": Fracs(10, 2) = "⅗": Fracs(10, 3) = "⅗"
Fracs(11, 1) = "⅜": Fracs(11, 2) = "⅜": Fracs(11, 3) = "⅜"
Fracs(12, 1) = "⅘": Fracs(12, 2) = "⅘": Fracs(12, 3) = "⅘"
Fracs(13, 1) = "⅚": Fracs(13, 2) = "⅚": Fracs(13, 3) = "⅚"
Fracs(14, 1) = "⅝": Fracs(14, 2) = "⅝": Fracs(14, 3) = "⅝"
Fracs(15, 1) = "⅞": Fracs(15, 2) = "⅞": Fracs(15, 3) = "⅞"

i = VBA.InStr(1, Arg, "/", vbTextCompare)
If i = 0 Then 'there's no fraction
MakeFracs = Arg
ElseIf Mid$(Arg, i, 3) Like "/##" Then 'not HTML5
MakeFracs = Arg
ElseIf Mid$(Arg, i - 2, 4) Like " [1-7]/[234568]" Then
sOUT = Mid$(Arg, i - 1, 3)
n = VBA.Val(Left$(sOUT, 1)) 'numerator
d = VBA.Val(Right$(sOUT, 1)) 'denominator
If n < d Then If d Mod n = 0 Then d = d / n n = 1 ElseIf d Mod 2 = 0 And n Mod 2 = 0 Then d = d / 2 n = n / 2 End If sIN = "&frac" & n & d & ";" For j = 1 To 15 If Fracs(j, 1) = sIN Then sIN = Fracs(j, 2) '<-or Fracs(j, 3) for HEX Exit For End If Next j MakeFracs = VBA.Replace(Arg, sOUT, sIN) Else MakeFracs = Arg End If Else MakeFracs = Arg End If End Function


 
At least that's all I wanted it to do. In practice, Wikipedia and WordPress seem to be not fully onboard with HTML5 and do not handle all fifteen &fracnd; formats (I confirmed Firefox does). That added "j-loop" in the middle translates the &fracnd;'s into Dec code. This works fine, though it's a step back from HTML5. Option is given to use Hex if desired. Your Excel table then looks like this:

N O P Q R S T U V
1 1/1 &#189; &#8531; &#188; &#8533; &#8537; 1/7 &#8539; 1/80
2 2/1 2/2 &#8532; &#189; &#8534; &#8531; 2/7 &#188; &#188;.
3 3/1 3/2 3/3 &#190; &#8535; &#189; 3/7 &#8540; &#8540;A
4 4/1 4/2 4/3 4/4 &#8536; &#8532; 4/7 &#189; 6 &#189;
5 5/1 5/2 5/3 5/4 5/5 &#8538; 5/7 &#8541; 7 &#8541;
6 6/1 6/2 6/3 6/4 6/5 6/6 6/7 &#190; 8 &#190;
7 7/1 7/2 7/3 7/4 7/5 7/6 7/7 &#8542; 9 &#8542;
8 8/1 8/2 8/3 8/4 8/5 8/6 8/7 8/8 10 8/8

 
Where N1: =MakeFracs(D1) filled down and right. Arranged that way you can see the HTML5 design thoughts. The very ugly website would look like this:

1/1 ½ ¼ 1/7 1/80
2/1 2/2 ½ 2/7 ¼ ¼.
3/1 3/2 3/3 ¾ ½ 3/7 ⅜A
4/1 4/2 4/3 4/4 4/7 ½ 6 ½
5/1 5/2 5/3 5/4 5/5 5/7 7 ⅝
6/1 6/2 6/3 6/4 6/5 6/6 6/7 ¾ 8 ¾
7/1 7/2 7/3 7/4 7/5 7/6 7/7 9 ⅞
8/1 8/2 8/3 8/4 8/5 8/6 8/7 8/8 10 8/8

 
Frankly, I'm not sure that this is an improvement. You'll come across Wikipedia editors, however, who are convinced of it. I thought about adding a trailing space, as in MakeFracs = VBA.Replace(Arg, sOUT, sIN & Chr(32)), but for every time I wanted to, I thought of an example where I didn't, and the logic got very convoluted. Better I decided to put the space in the table where wanted and not in the function. My HTML tablemaker is here, but it's being overcome by the hard steady march of technology. Wiki and CSS tablemakers are coming up. I used MakeFracs() in the above. No fractions were harmed in the making of this post.

…mrt
©¿©¬

UDF for Cumulative Sum

Back in my newsgroup days, about once a month someone would ask how to have a formula that accumulated entries from another cell. For example, put a formula in C3 that refers to A3. Every time a value is entered in A3, have the value in C3 change by that amount. Impossible right?

The standard answer is that you can’t and that you should use a Worksheet_Change event to monitor A3. When it changes, change the value in C3. Then I read Charles Williams’ comments about the Text property

When used inside a UDF you can use .Text to get the formatted value of the calling cell as it was before the UDF was called (you can’t do this with .Value or .Value 2 without causing a circular reference).

I had no idea that was true. Now I can write a UDF like this:

Function UpDown(dChange As Double) As Double

Dim sOld As String

Application.Volatile True

sOld = Application.Caller.Text

UpDown = Val(sOld) + dChange

End Function

In C3, I put =updown(A3). Whenever I change A3, C3 changes by that amount. I’m sure there are all kinds of problems with this – dates and errors to name two – but I thought it was interesting nonetheless. Apologies if the entire Excel universe knows this already, but it’s new to me.