Excel’s Most Evil Functions

It’s Evil Function election time: cast your votes so we can find out what the most evil Excel function is!

The Evil Function Survey

My vote has gone to INDIRECT (see my blog post here)

I will publish the results when we have got enough votes.

 

New Excel and PowerBI user groups in Wellington

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()

Why I’m going to Excel Summit South. (And why you should too).

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:
Zack

Here’s a picture of me imagining I’m speeding around a corner only to find Zack heading the other way:
Jeff

And then even if I manage to avoid Zack, there’s no avoiding the famous Auckland traffic. I still haven’t gotten over how bad it was last time I was up there:
Auckland traffiic

(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:
speakers2

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.
yigaledery_2 ben_rampsonThose 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, Ken…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.


Mythbusters jon 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.


jelen 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.


ZackAnd 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.


charles 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.

Excel Summit South 2016

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

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

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

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

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

Problems with Apple’s sandbox requirements and Mac Office 2016 with VBA code

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

http://www.rondebruin.nl/mac/mac034.htm

Have a nice day

The Amsterdam Excel Summit 2016 open for registration

SpandoekLicht

Hi Everyone!

We’ve opened registration for our third annual

Amsterdam Excel Summit.

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.

Tentative program

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!

Regards,

Jan Karel Pieterse

topexcelcass.com

jkp-ads.com