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.
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)
bReturn = sWordTwo = String(Len(sWordOne), sRPLC)
bReturn = False
IsAnagram = bReturn
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
IsPalindrome = bReturn
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.
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) = "_"
'Start the game
Do While bPlaying
Debug.Print "Welcome to hangman"
'This is the guessing loop
'Print the hangman and whatever was guessed
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
'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
lTries = lTries + 1
Loop Until lTries >= lMAXTRIES Or Join(aDispWord, vbNullString) = sTheWord
If Join(aDispWord, vbNullString) = sTheWord Then
Debug.Print "You win"
Debug.Print "You lose. The word was " & sTheWord
bPlaying = MsgBox("Play again?", vbYesNo) = vbYes
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)
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)
sPrint = vbNullString
Hi all. long time ago I post here but i like to share this page with you all. After reading it you are happy that you only use Excel for windows <g> .
Problems with Apple’s sandbox requirements and Mac Office 2016 with VBA
Have a nice day
We’ve opened registration for our third annual
May 26, 2016
Join us in Amsterdam to learn how to Excel from our Experts (all MVPs):
Jon Peltier, Bob Umlas, Roger Govier, Henk Vlootman, Oz du Soleil, Tony de Jonker, Jan Karel Pieterse.
Attend this comprehensive training event and you will:
- Improve your Power Query skills
- Learn how to Customize the ribbon for your workbooks and add-ins.
- Get insight how to Build Excel models based on ranges and positions.
- Understand how to create involved Array Formulas
- Get advice on Best practices in Power pivot.
- See how to use VBA to customize charts
- Receive Tips & Tricks, documentation and lots of valuable files
The Excel Charting And Dashboard Masterclass
May 27th 2016
The Amsterdam Excel summit also features a post-conference training. Attend this one-day masterclass and:
Excel MVP and charting Guru Jon Peltier teaches you how to visualize your numerical information in the most effective way.
Excel MVP and financial expert Tony de Jonker and communication & visualization expert David Hoppe unveil the secrets of creating powerful and flexible dashboards.
So head over to our website to register or to signup to our mailing list so we can keep you posted!
Jan Karel Pieterse
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’).
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
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.
That part returns a two-dimensional array of all the values in the first column
That turns a two-dimensional array into a one-dimensional array.
That turns an array into a string with commas between the values. In retrospect, I should have used
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.