Do you live in Wellington, practically *live* in Micosoft Excel, day in, day out, and want an opportunity to pick up tips, tricks, hacks, and code from other Excellers? Have some tricks of your own to pass on? Need a second opinion on an approach you’re taking with a spreadsheet, or help untangling the monster mission-critical minefield of an XLSX file you just inherited?
If so, then boy do I have a group for you: I’ve just set up a dedicated Excel-focused meetup group at http://www.meetup.com/Wellington-Microsoft-Excel-Meetup/ that is just itching for folks like you to join. This Excel-centric group will pretty much cover the spectrum in terms of Excel’s incredibly deep functionality: Formulas, Tables, PivotTables, Charting, PowerPivot, PowerQuery, VBA, spreadsheet auditing and optimisation, dashboarding tips, information visualisation , the works.
I’m in the process of securing a venue for the first meetup, so expect to see more details soon. (And if you can help out with a venue, let me know).
At the same time I’ve come across Phil Seamark’s PowerBI group that has its first meetup on Wednesday at TradeMe. See http://www.meetup.com/Power-BI-User-Group-Wellington-NZ/ for details). No matter where your interest lies on the Excel/PowerPivot/PowerBI spectrum, between us we’ll have you covered. (I’ll be at the PowerBI event on Wednesday night at Trademe, and would love to see you there.)
So regardless of whether you’re a VLOOKUP virgin, or a VBA Virtuoso, a PivotTable pariah, or a PowerBI Professional, between these two groups we’ll have community led content, comment, and camaraderie relevant to you, your job, and your career.
So what are you waiting for? Sign up TODAY()
At first I wasn’t going to. I live in Wellington, which is 636 km to the south. (395 miles, to those of you in the dark ages). And this being Middle Earth, to get there by road I would have had to run the gauntlet of Goblins, Orcs, and cliche tourists like Zack Barrasse likely driving on the wrong side of the road as he heads to Hobbiton behind a grin bigger than one face could safely hold:
(Don’t get me wrong…the traffic south of Auckland is not without its own challenges):
And then there’s the whole “Paying for Excel feels a little like the concept of paying for sex…I’m good enough at it these days that I really shouldn’t have to” thing. (Excel, that is.)
But then I read the program. And there is truly something for everyone. Even me, who’s a virtual demigod at it compared to the poor suckers around me who don’t use it at all and have no desire to start now. Not to mention the outstanding cast. These guys and gals are like the Dirty Dozen:
Or at least, they would be, if there were one more of them. Oh wait, look: there’s two more of them below. Okay, so they’re like the Dirty Baker’s Dozen, then.
Those two extras are a couple of guys called Yigel Edery and Ben Rampson from the Excel Project Team. These guys think they’re here to discuss the future of Excel. But I still live in the past – along with 99% of the rest of us – so I’ll make sure they find some time to answer all my tricky questions, like “How ’bout refreshing the Conditional Formatting Dialog. Have you ever had to actually use it yourselves? Huh? Huh?” …and… Where the hell are my Dynamic PivotTable References already? We’ve got ‘em for Tables, but not for PivotTables. Have you ever tried to integrate PivotTables into a formula-driven spreadsheet? Huh? Huh? …and… Why does every ‘new’ Excel feature have the prefix ‘Formally Known As’? Have you ever tried to write a book about characters that kept changing their names half-way through? Huh? Huh?
Boy do I feel sorry for those guys: They’ll be dead keen to talk about the new extensions, while I’ll be dead keen to point out that the stairs that lead to them still are a little unsafe to use. Let’s hope they’re still smiling like that when they get back on the plane.
I’ll also get to meet this guy, Ken Puls, …who wrote the most useful Excel blogpost ever in the history of most useful blogposts ever on the strength of it’s excellent existential first line alone: Do you know why you are here? He’s moving on to something more future focused in his presentation: Do you know where you are going? (A little place called PowerQuery apparently, located in the hip new suburb of Get and Transform.) Can’t wait, because I know zipcode about it.
And following straight on from that, I’ll either get to meet the Myth-Buster shown left, or the Chart-Buster shown right. (Apparently speakers are provisional and may change.) At least one of those guys needs no introduction: He’s Jon Peltier, and he’s a regular addition to my Google Search Terms whenever I try to squeeze out a good chart. And I owe him an entire beer of gratitude thanks to the best macro I never paid for. He’s going to pick up where Ken left off: Using Ken’s Powerquery data to build a Dashboard.
I also finally get to meet Bill Jelen. I’m gonna ask him nicely to autograph my stack of Excel books…even the ones he didn’t write. (He didn’t write like 0.01% of them, so it won’t take him long).
The only problem is that Bill’s talk coincides with another from Ken on PowerQuery. Damn. But maybe I’ll just have to skip Ken’s sequel, because Bill’s gonna dive deep into Data Visualisations (yes, it’s spelt with an s down here), Conditional Formatting, and PivotTables.
And of course, Zack isn’t here merely to look down Hobbits’ holes. He’s also here to talk about Tables, and perhaps to drink me under one afterwards.
And I’m super-excited to be meeting Charles Williams, who’s like the Phar Lap of Fast Excel. The Edmund Hillary of Excel’s capillaries. The Ernest Rutherford of Excel under-the-hood. If you’ve got spreadsheets that take about as long to open as the running time of your average installment of The Hobbit, then you need to read this, and then you need to come meet this guy.
And that’s just a select few of the wizened, Excel-scarred faces that I’ll see there. There’s a whole bunch of other international stars and local heroes coming too (including from that sleepy continent-sized Island that lies to the West of New Zealand) that I just can’t wait to INDEX and MATCH.
So yes, I’ve got my ticket. And I suggest you get yours pronto, because – as we say down here – it’s going to be O for Owesome.
You going? Give me a shout out in the comments, and we’ll CONCATENATE. You thinking of going, but haven’t quite committed? Shout out below anyway, and perhaps one of the Dirty Baker’s Dozen will drop by and talk you around.
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:
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.
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:
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.
Jan Karel Pieterse