Hi there. Hope you and yours are well in these difficult times!
This is just a very short announcement that I’ll be doing an on-line version of my Excel VBA Masterclass.
The training is scheduled for May 18, 20, 26, 28, June 2, 4 and I’ll be using Microsoft Teams to deliver it to your homes!
Regards,
Jan Karel Pieterse
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
|
1 |
In |
clause in SQL. Let’s say I have this list of customer IDs and I want to make an
|
1 |
In |
clause.
| 38 |
| 142 |
| 146 |
| 175 |
| 214 |
| 217 |
I’d use JoinRange like
|
1 |
=JoinRange(A2:A7,,"','","('","')") |
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
|
1 |
('38','142','146','175','214','217') |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Public Function JoinRange(rInput As Range, _ Optional sDelim As String = vbNullString, _ Optional sLineStart As String = vbNullString, _ Optional sLineEnd As String = vbNullString, _ Optional sBlank As String = vbNullString, _ Optional sQuotes As String = vbNullString) As String Dim vaCells As Variant Dim i As Long, j As Long Dim lCnt As Long Dim aReturn() As String vaCells = rInput.Value ReDim aReturn(1 To rInput.Cells.Count) For i = LBound(vaCells, 1) To UBound(vaCells, 1) For j = LBound(vaCells, 2) To UBound(vaCells, 2) lCnt = lCnt + 1 If Len(vaCells(i, j)) = 0 Then aReturn(lCnt) = sQuotes & sBlank & sQuotes Else aReturn(lCnt) = sQuotes & vaCells(i, j) & sQuotes End If Next j Next i JoinRange = sLineStart & Join(aReturn, sDelim) & sLineEnd End Function |
Now, my formula looks like this:
|
1 |
=JoinRange(A2:A7,",","(",")",,"'") |
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…
|
1 |
=JoinRange(A2:A7,"|","(",")",,"'") |
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
|
1 |
=JoinRange(A2:A7,xlCOMMA,"(",")",xlSINGLE) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
Public Sub AddConstantNames() ActiveWorkbook.Names.Add "xlCOMMA", "="",""" ActiveWorkbook.Names.Add "xlSPACE", "="" """ ActiveWorkbook.Names.Add "xlDOUBLE", "=""""" ActiveWorkbook.Names.Add "xlSINGLE", "=""'""" ActiveWorkbook.Names.Add "xlPARENO", "=""(""" ActiveWorkbook.Names.Add "xlPARENC", "="")""" ActiveWorkbook.Names.Add "xlPIPE", "=""|""" End Sub |
|
1 |
=JoinRange(A2:A7,xlCOMMA,xlPARENO,xlPARENC,xlSINGLE) |
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.
More Java homework:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Public Function IsAnagram(ByVal sWordOne As String, ByVal sWordTwo As String) As Boolean Dim vaOne As Variant Dim vaTwo As Variant Dim bReturn As Boolean Dim i As Long Const sRPLC As String = "+" sWordOne = Replace(sWordOne, Space(1), vbNullString) sWordTwo = Replace(sWordTwo, Space(1), vbNullString) If Len(sWordOne) = Len(sWordTwo) Then For i = 1 To Len(sWordOne) sWordTwo = Replace(sWordTwo, Mid$(sWordOne, i, 1), sRPLC, 1, 1, vbTextCompare) Next i bReturn = sWordTwo = String(Len(sWordOne), sRPLC) Else bReturn = False End If IsAnagram = bReturn End Function |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Public Function IsPalindrome(ByVal sPhrase As String) Dim i As Long Dim bReturn As Boolean bReturn = True sPhrase = Replace(sPhrase, Space(1), vbNullString) For i = 1 To Len(sPhrase) If LCase(Mid$(sPhrase, i, 1)) <> LCase(Mid$(sPhrase, Len(sPhrase) + 1 - i, 1)) Then bReturn = False Exit For End If Next i IsPalindrome = bReturn End Function |
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
Function MaxMinFairDK(Supply As Double, Demands As Variant) As Variant Dim dPrior As Double Dim vaReturn As Variant Dim dAvailable As Double Dim i As Long, j As Long Dim dTemp As Double Dim wf As WorksheetFunction On Error GoTo ErrHandler Set wf = Application.WorksheetFunction If IsObject(Demands) Then Demands = Demands.Value2 'make range array dAvailable = Abs(Supply) 'ignore negative supplies If Not IsArray(Demands) Then 'One demand = min of supply or demand MaxMinFairDK = Array(dAvailable, Demands)(Abs(dAvailable > Demands)) Else 'Excel returns NA when you use too many columns If UBound(Demands, 2) > 1 Then Err.Raise xlErrNA 'Assume everybody gets everything they want ReDim vaReturn(LBound(Demands, 1) To UBound(Demands, 1), 1 To 1) vaReturn = Demands For i = UBound(Demands, 1) To LBound(Demands, 1) Step -1 'If there's enough, do nothing except reduce what's available If dAvailable / i > (wf.Large(Demands, i) - dPrior) Then dAvailable = dAvailable - ((wf.Large(Demands, i) - dPrior) * i) dPrior = wf.Large(Demands, i) Else 'Once there's not enough, everyone splits what's left For j = LBound(Demands, 1) To UBound(Demands, 1) If Demands(j, 1) > dPrior Then vaReturn(j, 1) = dPrior + (dAvailable / i) End If Next j Exit For End If Next i MaxMinFairDK = vaReturn End If ErrExit: Exit Function ErrHandler: MaxMinFairDK = CVErr(Err.Number) Resume ErrExit End Function |
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
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?
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 = "
"
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.
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 | ½ | ⅓ | ¼ | ⅕ | ⅙ | 1/7 | ⅛ | 1/80 |
| 2 | 2/1 | 2/2 | ⅔ | ½ | ⅖ | ⅓ | 2/7 | ¼ | ¼. |
| 3 | 3/1 | 3/2 | 3/3 | ¾ | ⅗ | ½ | 3/7 | ⅜ | ⅜A |
| 4 | 4/1 | 4/2 | 4/3 | 4/4 | ⅘ | ⅔ | 4/7 | ½ | 6 ½ |
| 5 | 5/1 | 5/2 | 5/3 | 5/4 | 5/5 | ⅚ | 5/7 | ⅝ | 7 ⅝ |
| 6 | 6/1 | 6/2 | 6/3 | 6/4 | 6/5 | 6/6 | 6/7 | ¾ | 8 ¾ |
| 7 | 7/1 | 7/2 | 7/3 | 7/4 | 7/5 | 7/6 | 7/7 | ⅞ | 9 ⅞ |
| 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
©¿©¬
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.