Daily Dose of Excel

[...] (77) = "seventy-seven" Debug.Assert NumbersToWords(99) = "ninety-nine" End Sub Again, I’m testing the edges and few in between. Now that I have two test procedures, I’ll need [...]

[...] .Offset(0, 1).Value         End If     Next Cell     End Sub Not exactly a barn burner, I’m sure you’ll agree. I hated every minute of it. I [...]

[...]   End If         End If     Next i         End Sub First, I read in the 50 Excel most recently used files. Then I read in the 1,000 most recently [...]

[...] ;Else          MsgBox sMSGNODYMO, vbOKOnly      End If End Sub Instead of using the SetField method (like in the previous example), I call a new procedure [...]

[...] %eachctr%{Up}For Each %eachctr%{Space} } } ;if the next word is one of these, you're opening a text file else if (counter = "Append" or counter = "Binary" or counter = "Input& [...]

[...] much nonstop for an hour. At 3:30, I hadn’t touched my mouse once. I had actually opened a text file to record all the times that I was tempted to use my mouse and the keyboard method I used [...]

[...] sends me an attachment with no file extension or some bullshit file extension. I get a text file with a .success extension from a website telling me my upload worked. I’m not sure if they’ [...]

[...] know. We’ll see how the performance holds up. I’ve been using it for three days and my text file is only up to 58 files – the 50 Excel stores plus eight additional. I guess it will [...]

[...] Set dicPF_All = Nothing Set dicVisible = Nothing End Function Stormtrooper: Let me see your sample file. Obi-Wan: [with a small wave of his hand] You don’t need to see his sample file. Stormtrooper: [...]

[...] that does it’s *utmost* to let you know which PivotFilter was changed, if any. Here’s a sample file with it all good to go: WhichPivotfieldChanged_20140709 Sure, it will still not capture [...]

[...] If End Sub Yep, I think this remake is much, much better looking than the original: Here’s the sample file: PivotChange_20140802 [...]

[...] and Max values of the entire block of data, and point your Today series at that: Here’s a sample file with both approaches: Todays Date on ScatterPlot Maybe this stuff is all included in Dick [...]

[...] ninety-nine" End Sub A haphazard selection of numbers including the edge cases. Function NumbersToWords(ByVal dNumbers As Double) As String Dim vaSingles As Variant Dim vaTens As Variant Dim [...]

[...] If block. Of course I’ll be using exponents so I don’t have to type all those zeros. Function NumbersToWords(ByVal dNumbers As Double) As String Dim sReturn As String Dim dRemainder As Double If [...]

[...] the English words of the argument. The procedure header should look like this: Function NumbersToWords(ByVal dNumbers As Double) As String First, write a test. Sub TEST_Singles() Debug. [...]

[...] I just need to divide my number into triplets, process them, and concatenate the answers. Function NumbersToWords(ByVal dNumbers As Double) As String Dim sReturn As String Dim dRemainder As Double If [...]

[...] rCell.FormatConditions On Error Resume Next colFormats.Add .Item(i), CFSignature(.Item(i)) On Error GoTo 0 End With Next i Next rCell ReDim aOutput(1 To colFormats.Count + 1, 1 To 5) Set wsOutput = [...]

[...] ;" or push Cancel to delete it.", _ Default:=Selection.Address, _ Type:=8) On Error GoTo 0 If Not rngNew Is Nothing Then nm.RefersTo = "='" & ActiveSheet.Name & [...]

[...] strNew = Fix_Name(strNew) On Error Resume Next Set nmExists = ActiveWorkbook.Names(strNew) On Error GoTo 0 If nmExists Is Nothing Then nm.Name:=strNew Else: MsgBox "That name already exists. [...]

[...] With If Err.Number > 0 Then 'SlicerCache already existed. Work out what it's index is On Error GoTo 0 For Each sc In ActiveWorkbook.SlicerCaches For Each ptOther In sc.PivotTables If ptOther = pt [...]

[...] Did you know that International Keyboard Shortcut Day is the first Wednesday in November? Considering I just made that up, I’ll bet you [...]

[...] In honor of International Keyboard Shortcut Day, here’s three easy to remember shortcuts for formatting text. Ctrl+B: Makes the [...]

[...] In honor of International Keyboard Shortcut Day, here are some tips for navigating around and selecting ranges via the keyboard. [...]

[...] International Keyboard Shortcut Day was a huge success. I expected that I would be the only one participating and I [...]