Excel Summit South 2016

Posted on February 11, 2016 by Dick Kusleika

Hey Daily Dosers. I had knee replacement surgery about three weeks ago and haven’t looked at Excel very much since then. I’m taking a break from my drug-induced naps and Netflix binge watching to remind you that Excel Summit South 2016 is rapidly approaching. By ‘South’, they mean

Auckland 28 February
Sydney 1 March
Melbourne 6 March

Here’s what’s on tap:

Learn from six of the world’s leading Excel MVP’s as they discuss the Excel topics most useful to you.
Hear industry leading speakers from around the world give you the latest views on Financial Modelling best practices, standards and spreadsheet risk.
Shape the future of Excel: Interact with members of the Microsoft Excel Dev Team as you explore with them the future of Excel.
Choose the sessions that best suit your needs from 23 masterclass sessions over two days of twin tracks for modellers and analysts.

There are lots of familiar names on the presenter’s list. Go to https://excelsummitsouth.wordpress.com/ to learn more. If you’re on the Twitters, use hashtag #XLSOUTH and if you have questions, contact Charles@DecisionModels.com.

Anagrams and Palindromes

Posted on January 14, 2016 by Dick Kusleika

More Java homework:

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

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.

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

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.

Hangman

Posted on January 13, 2016 by Dick Kusleika

The Boy (but not this The Boy) is taking a Java class. I was watching him code up a hangman game last night. There’s a lot to like about Java, I think. It’s frustrating working with a new language when you don’t know all the debugging tricks. I couldn’t figure out how to step through code or use the console to inspect variables (like an Immediate Window) in Eclipse. I’m sure it can be done, I just don’t know how.

I decided to replicate his game in VBA. It’s pretty rough. It doesn’t tell you what you’ve already guessed, for one thing. Also, I don’t use the grid because he was using ASCII art in the Eclipse console and I wanted to do the same. Clearly shapes on the grid would be cooler. VBA doesn’t have a “scan” feature to get input, so I had to use InputBox and MsgBox to get user input.

As I was coding it, I was thinking that I should have use test-first development as an exercise, but I didn’t. For what it’s worth:

Public Sub PlayHangman()
    
    Dim bPlaying As Boolean
    Dim sInput As String
    Dim lTries As Long
    Dim vaWords As Variant
    Dim sTheWord As String
    Dim aDispWord() As String
    Dim i As Long
    
    Const sTERMCHAR As String = "-"
    Const lMAXTRIES As Long = 6
    
    bPlaying = True
    'I'll pick one of these words at random
    vaWords = Split("jazz zigzag colour favourite doughnut rabbit")
    sTheWord = vaWords(Int(Rnd * (UBound(vaWords) - LBound(vaWords) + 1) + LBound(vaWords)))
    
    'Fill the displayed word w "emtpy space"
    ReDim aDispWord(1 To Len(sTheWord))
    For i = LBound(aDispWord) To UBound(aDispWord)
        aDispWord(i) = "_"
    Next i
        
    'Start the game
    Do While bPlaying
        Debug.Print "Welcome to hangman"
        
        'This is the guessing loop
        Do
            'Print the hangman and whatever was guessed
            PrintHangman lTries
            Debug.Print Join(aDispWord, Space(1))
            
            sInput = Left$(InputBox("Enter your guess", "Hangman"), 1)
            
            'Type a dash to get abort early
            If sInput = sTERMCHAR Then
                bPlaying = False
                Exit Do
            End If
            
            'Correct guesses get filled in the displayed word and incorrect
            'guesses increments lTries
            If InStr(1, sTheWord, sInput) > 0 Then
                For i = 1 To Len(sTheWord)
                    If Mid$(sTheWord, i, 1) = sInput Then
                        aDispWord(i) = sInput
                    End If
                Next i
            Else
                lTries = lTries + 1
            End If
        Loop Until lTries >= lMAXTRIES Or Join(aDispWord, vbNullString) = sTheWord
        
        If Join(aDispWord, vbNullString) = sTheWord Then
            Debug.Print "You win"
        Else
            Debug.Print "You lose. The word was " & sTheWord
        End If
        
        bPlaying = MsgBox("Play again?", vbYesNo) = vbYes
    Loop
    
End Sub

Public Sub PrintHangman(ByVal lTries As Long)
    
    Dim aMan(1 To 5, 1 To 7) As String
    Dim i As Long, j As Long
    Dim sPrint As String
    
    aMan(1, 1) = "_": aMan(1, 2) = "_": aMan(1, 3) = "_": aMan(1, 4) = "_": aMan(1, 5) = "_": aMan(1, 6) = "_"
    For i = 2 To UBound(aMan)
        aMan(i, 1) = "|"
        For j = 2 To UBound(aMan, 2)
            aMan(i, j) = Space(1)
        Next j
    Next i
    aMan(2, 6) = "|"
        
    If lTries >= 1 Then aMan(3, 6) = "o"
    If lTries >= 2 Then aMan(4, 6) = "|"
    If lTries >= 3 Then aMan(4, 5) = "/"
    If lTries >= 4 Then aMan(4, 7) = "\"
    If lTries >= 5 Then aMan(5, 5) = "/"
    If lTries >= 6 Then aMan(5, 7) = "\"
    
    For i = LBound(aMan, 1) To UBound(aMan, 1)
        For j = LBound(aMan, 2) To UBound(aMan, 2)
            sPrint = sPrint & aMan(i, j)
        Next j
        Debug.Print sPrint
        sPrint = vbNullString
    Next i
    
End Sub

Public Sub PlayHangman()

Dim bPlaying As Boolean

Dim sInput As String

Dim lTries As Long

Dim vaWords As Variant

Dim sTheWord As String

Dim aDispWord() As String

Dim i As Long

Const sTERMCHAR As String = "-"

Const lMAXTRIES As Long = 6

bPlaying = True

'I'll pick one of these words at random

vaWords = Split("jazz zigzag colour favourite doughnut rabbit")

sTheWord = vaWords(Int(Rnd * (UBound(vaWords) - LBound(vaWords) + 1) + LBound(vaWords)))

'Fill the displayed word w "emtpy space"

ReDim aDispWord(1 To Len(sTheWord))

For i = LBound(aDispWord) To UBound(aDispWord)

aDispWord(i) = "_"

Next i

'Start the game

Do While bPlaying

Debug.Print "Welcome to hangman"

'This is the guessing loop

'Print the hangman and whatever was guessed

PrintHangman lTries

Debug.Print Join(aDispWord, Space(1))

sInput = Left$(InputBox("Enter your guess", "Hangman"), 1)

'Type a dash to get abort early

If sInput = sTERMCHAR Then

bPlaying = False

Exit Do

End If

'Correct guesses get filled in the displayed word and incorrect

'guesses increments lTries

If InStr(1, sTheWord, sInput) > 0 Then

For i = 1 To Len(sTheWord)

If Mid$(sTheWord, i, 1) = sInput Then

aDispWord(i) = sInput

End If

Next i

Else

lTries = lTries + 1

End If

Loop Until lTries >= lMAXTRIES Or Join(aDispWord, vbNullString) = sTheWord

If Join(aDispWord, vbNullString) = sTheWord Then

Debug.Print "You win"

Else

Debug.Print "You lose. The word was " & sTheWord

End If

bPlaying = MsgBox("Play again?", vbYesNo) = vbYes

Loop

End Sub

Public Sub PrintHangman(ByVal lTries As Long)

Dim aMan(1 To 5, 1 To 7) As String

Dim i As Long, j As Long

Dim sPrint As String

aMan(1, 1) = "_": aMan(1, 2) = "_": aMan(1, 3) = "_": aMan(1, 4) = "_": aMan(1, 5) = "_": aMan(1, 6) = "_"

For i = 2 To UBound(aMan)

aMan(i, 1) = "|"

For j = 2 To UBound(aMan, 2)

aMan(i, j) = Space(1)

Next j

Next i

aMan(2, 6) = "|"

If lTries >= 1 Then aMan(3, 6) = "o"

If lTries >= 2 Then aMan(4, 6) = "|"

If lTries >= 3 Then aMan(4, 5) = "/"

If lTries >= 4 Then aMan(4, 7) = "\"

If lTries >= 5 Then aMan(5, 5) = "/"

If lTries >= 6 Then aMan(5, 7) = "\"

For i = LBound(aMan, 1) To UBound(aMan, 1)

For j = LBound(aMan, 2) To UBound(aMan, 2)

sPrint = sPrint & aMan(i, j)

Next j

Debug.Print sPrint

sPrint = vbNullString

Next i

End Sub

Stack Cred

Posted on December 23, 2015February 1, 2024 by Dick Kusleika

Ooh, look how special I am.

As a reward I bought myself some new monitors.

The real story is that one of my 22″ monitors died last weekend. It would display normally for about a second before going black. The consensus on the internet was to throw it away and buy a new one. It’s been a while since I bought monitors and I was expecting that 30″ monitors would be in my price range by now. I was disappointed that I they’ve only fallen in price enough that I could get 24s. But I’m pretty happy with monitors that are identical. And the boy is pretty happy to get my hand-me-down, working 22″ monitor.

My next problem was that my video card was too old to drive these beasts. I solved that problem by allowing the boy to open an early Christmas present.

EVGA GeForce GTX 970

He gets a better 한국 토토사이트 gaming card and I get his old video card. Easy-peasy. Except that it took me four days to get the cards swapped out as am I not the hardware genius I sometimes make myself out to be.

Have a lovely holiday if your religion or culture celebrates one this week. I’m going to see Star Wars on Christmas Eve, so if you see me walking around Denver this week, don’t spoil it for me (but be sure to say ‘hi’).

Listing New Shareholders

Posted on December 21, 2015December 20, 2015 by Dick Kusleika

I have a list of several hundred stock transactions that consist of purchases, redemptions, transfers, and reissues.

To get a list of new shareholders, I start by creating a pivot table from the data (Alt+N+V+Enter)
Put the ShareholderName in Row Labels and the Date in Values
Right click on any of the date numbers and choose Summarize Values By Min to get the earliest date for every shareholder.
Right click on any of the dates (that probably don’t look like dates) and choose Value Field Settings. Then click on Number Format and format the field as a date.
While you still have a date selected, choose Sort from the PivotTable Tools – Options Ribbon and sort largest to smallest
Now you can copy the 10 new shareholder names from 2015.

Now try it yourself. You can download StockRegister.zip

Retrieving Data from Add-in Worksheets

Posted on December 9, 2015December 8, 2015 by Dick Kusleika

Add-ins have worksheets. You just can’t see them. But you can store information on them and it’s a good way to store settings, preferences, and other data. When you want to get to that data, you can go to the Properties for ThisWorkbook and change the IsAddin property to False. Now you can see the worksheets and change the data if necessary.

When you’re done, go back to the VBE and change the IsAddin property back to True before you save your changes. Don’t forget that part; it’s important.

I have a list of vendor codes stored on a worksheet in an addin. I need to see the list, but not change it. I didn’t want to go through all the IsAddin rigmarole, so I did this in the Immediate Window.

wshvendors.ListObjects(1).ListColumns(1).DataBodyRange.value

1	wshvendors.ListObjects(1).ListColumns(1).DataBodyRange.value

That part returns a two-dimensional array of all the values in the first column

application.transpose(...)

1	application.transpose(...)

That turns a two-dimensional array into a one-dimensional array.

?join(...,",")

1	?join(...,",")

That turns an array into a string with commas between the values. In retrospect, I should have used

?join(application.transpose(wshvendors.ListObjects(1).ListColumns(1).DataBodyRange.value),vbnewline)

1	?join(application.transpose(wshvendors.ListObjects(1).ListColumns(1).DataBodyRange.value),vbnewline)

to get each code on its own line. Here’s a way to put it into a range, if that’s where you’re going with it anyway.

wshvendors.ListObjects(1).ListColumns(1).DataBodyRange.Copy workbooks.Add.Worksheets(1).cells(1,1)

1	wshvendors.ListObjects(1).ListColumns(1).DataBodyRange.Copy workbooks.Add.Worksheets(1).cells(1,1)

Structured Table Referencing and Double Brackets in Column Headers

Posted on December 7, 2015December 5, 2015 by Dick Kusleika

If you use certain characters in the column names of your table, then you have to use double brackets around those names when you refer to them in formulas. I don’t care for that, so I don’t use those characters. I was converting some imported data into a table and, of course, it had spaces in the column names (and a few other naughty characters). I needed to clean up those characters before converting to a table. The formula below shows what the double brackets look like.

This office web page provides a list of characters that cause this behavior. I don’t know where they got that list, but I did notice that there was no underscore on it. I like to use underscores to separate words, but I can’t use them in tables because of the double bracket thing. I figured I’d better make my own list if I’m going to clean up data.

I wrote this code to list out all of the bad characters:

Public Sub TestColHeaders()
    
    Dim i As Long
    
    For i = 1 To 255
        Range("G8").Value = "One" & Chr$(i) & "Two"
        If Left(Range("h9").Formula, 4) = "=[@[" Or InStr(1, Range("h9").Formula, "'") > 0 Then
            Debug.Print i, Range("h9").Formula
        End If
    Next i
    
End Sub

Public Sub TestColHeaders()

Dim i As Long

For i = 1 To 255

Range("G8").Value = "One" & Chr$(i) & "Two"

If Left(Range("h9").Formula, 4) = "=[@[" Or InStr(1, Range("h9").Formula, "'") > 0 Then

Debug.Print i, Range("h9").Formula

End If

Next i

End Sub

It loops through all the characters in the basic ASCII character set, inserts the character into a the column header, and reads the formula that references that column. I check for

=[@[

=[@[

or an apostrophe. It turns out that all the characters you have to escape with an apostrophe also cause double brackets, so I only needed to check for the double brackets.

With my list, I created a function to clean the data before I use it as a column header. I don’t check for Chr(13) because I don’t think you can have that in a cell.

Public Function CleanTableColumnHeader(ByVal sHeader As String) As String
    
    Dim i As Long
    Dim sReturn As String
    
    sReturn = sHeader
    
    For i = 32 To 47
        sReturn = Replace$(sReturn, Chr$(i), vbNullString)
    Next i
    
    For i = 58 To 64
        sReturn = Replace$(sReturn, Chr$(i), vbNullString)
    Next i
    
    For i = 91 To 96
        sReturn = Replace$(sReturn, Chr$(i), vbNullString)
    Next i
    
    sReturn = Replace$(sReturn, Chr$(123), vbNullString)
    sReturn = Replace$(sReturn, Chr$(125), vbNullString)
    sReturn = Replace$(sReturn, Chr$(126), vbNullString)
    sReturn = Replace$(sReturn, Chr$(9), vbNullString)
    sReturn = Replace$(sReturn, Chr$(10), vbNullString)
    
    CleanTableColumnHeader = sReturn
    
End Function

Public Function CleanTableColumnHeader(ByVal sHeader As String) As String

Dim i As Long

Dim sReturn As String

sReturn = sHeader

For i = 32 To 47

sReturn = Replace$(sReturn, Chr$(i), vbNullString)

Next i

For i = 58 To 64

sReturn = Replace$(sReturn, Chr$(i), vbNullString)

Next i

For i = 91 To 96

sReturn = Replace$(sReturn, Chr$(i), vbNullString)

Next i

sReturn = Replace$(sReturn, Chr$(123), vbNullString)

sReturn = Replace$(sReturn, Chr$(125), vbNullString)

sReturn = Replace$(sReturn, Chr$(126), vbNullString)

sReturn = Replace$(sReturn, Chr$(9), vbNullString)

sReturn = Replace$(sReturn, Chr$(10), vbNullString)

CleanTableColumnHeader = sReturn

End Function

Now, to make sure my Excel workbooks are utterly un-editable by anyone else, I’m going to write an event handler that converts all my underscores to something else as I type them. The only question, is should I use an elipsis (chr$(133))

One…Two

One…Two

or a macron (chr$(175))

One¯Two

One¯Two

That’s a tough one.

Pivot Table Videos

Posted on December 5, 2015 by Dick Kusleika

If you don’t have an ad blocker installed, you may have noticed we have a new sponsor: myexcelonline.com. If you do have an ad blocker installed, you should whitelist this page because I only show relevant, unobtrusive ads.

myexceloneline.com offers a free pivot table webinar when you click on the add in the sidebar. I didn’t sign up for the webinar, but I did watch a few of the free videos on that site. They’re very well made, professional videos. I really like the pace – they move along without feeling rushed.

If you have an interest in pivot table or dashboards, give myexcelonline.com a try.

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Author: Dick Kusleika

Excel Summit South 2016

Anagrams and Palindromes

Hangman

Stack Cred

Listing New Shareholders

Retrieving Data from Add-in Worksheets

Structured Table Referencing and Double Brackets in Column Headers

Pivot Table Videos