Excel Survey Results

I hit 100 survey responses, so it’s time to pick a winner.

The two winners, John and Sean, have been contacted. If you don’t have an email from me, sadly, you didn’t win. Unless John or Sean fail to send me a proper mailing address, in which case you might still be a winner. But probably not. You should just go buy the book on Amazon to console yourself.


Excel 2016 Formulas Spreadsheets Bookshelf


Excel Power Programming Spreadsheets Bookshelf

I’m planning a post about making money from Excel. That’s part of the reason I did the survey. You don’t have to wait for my misinterpretations; you can see the survey results right now and misinterpret them for yourself.







Hyperlink Keyboard Shortcut Update

I have a custom keyboard shortcut, Ctrl+L, to “click” on a hyperlink in Excel. I thought I had posted that code, but I can’t find it. It’s not much.

If ActiveCell.Hyperlinks.Count > 0 Then
    ActiveCell.Hyperlinks(1).Follow
End If

It doesn’t work with links created with the HYPERLINK function because a formula doesn’t create a member of the Hyperlinks collection. I fixed it by parsing the formula and trying to follow the link inside.

Public Sub FollowLink()
   
    Dim vaSplit As Variant
    Dim sForm As String
   
    Const sLINKFORM As String = "=HYPERLINK("
   
    On Error GoTo ErrHandler
   
    If ActiveCell.Hyperlinks.Count > 0 Then
        ActiveCell.Hyperlinks(1).Follow
    Else
        If InStr(1, ActiveCell.Formula, sLINKFORM) = 1 Then
            sForm = ActiveCell.Formula
            sForm = Left(sForm, Len(sForm) - 1) 'remove last parent
            sForm = Replace(sForm, Mid(sLINKFORM, 2, 255), vbNullString) 'Remove function name
            vaSplit = Split(sForm, ",")
            If IsError(Evaluate(Join(vaSplit, ","))) Then 'friendly name argument used
                ReDim Preserve vaSplit(0 To UBound(vaSplit) - 1)
            End If
            ActiveWorkbook.FollowHyperlink Evaluate(Join(vaSplit, ","))
        End If
    End If
   
ErrExit:
    On Error Resume Next
    Exit Sub
   
ErrHandler:
    MsgBox Err.Description & vbNewLine & Evaluate(Join(vaSplit, ","))
    Resume ErrExit
End Sub

Here’s an example of a HYPERLINK formula I use.

=HYPERLINK(LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf","Backup")

This links to a file named CurrentFileName_Backup.pdf. The first thing the code does is remove the last parenthesis.

=HYPERLINK(LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf","Backup"

Next it removes the function name. It doesn’t remove the equal sign because I’ll need that for the Evaluate function later.

=LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf","Backup"

Next it splits the string on a comma. A comma will separate the link argument from the friendly name argument. This one has more than one comma, but we’ll deal with that later by Joining the array.

vaSplit(0) = =LEFT(SUBSTITUTE(CELL("filename")
vaSplit(1) = "["
vaSplit(2) = "")
vaSplit(3) = FIND("."
vaSplit(4) = CELL("filename"))-2)&"_Backup.pdf"
vaSplit(5) = "Backup"

The friendly name argument is optional. This example has a friendly name, but not every one will. To determine if the friendly name argument is used, I try to evaluate the string. A legitimate formula with a , friendly_name after it won’t evaluate and will return an error. If that’s the case, I remove the last element of the array and evaluate the remaining elements.

In this case, the Redim Preserve gets rid of element #5, but leaves the others intact. The remaining five elements are joined into

=LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf"

The Evaluate function turns into the result of the formula (the file is named JE35.xlsm).

S:\Accounting\General_Ledger_Information\201606\JE35\JE35_Backup.pdf

Passing that to FollowHyperlink opens the file. Unless it doesn’t exist. Then I get an error “Cannot open the specified file.” and a message box tells me the file name it tried to open. That way I can troubleshoot any problems before I go ask why the backup wasn’t included in this journal entry.

Windows API Viewer for MS Excel

This is a guest post by Dennis Wallentin.

The Tool

Windows API Viewer for MS Excel is a standalone, powerful tool for creating Windows API code, with or without conditions, to be inserted into code modules. It offers several lists of APIs for both platforms, x86 and x64, that can easily be used in various solutions. If wanted, it can create conditional Windows API solutions. The two conditions that can be applied are Win64 and VBA7. More information about them can be found in the help files shipped with the tool. The tool is shipped with extensive help support; the help includes help files and web pages that are relevant about Windows API.

The following screen shot shows the main form:

It is very easy to use Windows API Viewer. Select the code options you want and then export them to the clipboard. That’s all! Since it’s a standalone tool it can be used with other development tools. But the Windows APIs here are selected for MS Excel in particular. The tool’s help file explains and shows the various output you can create. Before the code solution is exported to the clipboard you can preview it as the following screen shot shows:

If you want further information about the selected Windows API the tool offers use of Google to find out more as the below screen shot shows:

Download

The Windows API Viewer for MS Excel is available in x86 version and in x64 version. To install it just double click on the downloaded exe file and follow the instructions on the screen.

Windows API Viewer – x86 Version
Windows API Viewer – x64 Version

Requirements

Since Windows API Viewer for MS Excel is not an add-in to MS Excel it can be used with whatever Excel version available. It can also be used with other development tools as well. However, the following requirements must be met in order to use the tool:

  1. Windows XP or later version.
  2. Microsoft.NET 4.0 or later version.

Development tools

The following tools have been used when creating Windows API Viewer for MS Excel:

  1. Microsoft Visual Studio 2015
  2. DevExpress WinForm Controls
  3. SQLite
  4. Help + Manual
  5. SamLogic Visual Installer 2015
  6. BoxedApp Packer

The Source Files

The Windows API sources come from two different text files by Microsoft. Unfortunately, the file for x86 Windows API is rather old, released by Microsoft in 1998. The other file was released when Excel 2010 was released and includes the x64 versions of Windows API. For obvious reason they are not complete. The quality has been improved as I have been forced to manually work with these files. All the Windows API are now stored in a SQLite database and can easily be updated when necessary. If you find some error or you see that some APIs are missing, please send me an e-mail and I will update accordingly. New versions of the tool will then be made available. By the way, did I mention that working with these files has been tedious?

License

The Windows API Viewer for MS Excel is made available based on the MIT License (MIT).

Home Page

Windows API Viewer has its home at Ron de Bruin’s site where upgrades and news will be published:
http://www.rondebruin.nl/win/dennis/windowsapiviewer.htm

Special Thanks
Special thanks goes to Ron de Bruin and Ken Puls.

All the very best,
Dennis

Double Your Pleasure

Can you believe this blog is 12 years old? In celebration of its birthday, I’m giving away a couple of books. Scroll down to the end if you just want the free stuff.

Personal Stuff
I had full knee replacement surgery in January. That’s less fun than it sounds. When the medical folks or former patients talk about knee replacements, they talk about the few days after surgery and they talk about week 7 and beyond. For the few days after surgery, everything is great because of the drugs. Week 7 is a nice turning point and roughly the time when people feel that it was worth it. The part in between those two sucks.

Physical therapy started the day after I was released from the hospital. Back in the good old days, you would lay around for six weeks to heal, then start physical therapy. Then someone realized that the scar tissue was fully formed in six weeks. So now they start it as soon as possible to get maximum range of motion before the scar tissue sets up. My last measurements were -1 and 122. Zero extension means your leg is straight. Normal people are negative, I think. On the flexion side, between 120 and 125 is normal for healthy knees. My good knee is only 122, so I was happy to get that.

I learned something about myself: I value efficiency and independence. OK, I probably didn’t “learn” that, but those facts were driven home as every task either took forever or had to be done by someone else. It was pure hell, I tell ya. I set up my work computer at home, but it was so slow I only tried to keep my inbox empty and fix emergencies. I went back to the office after four weeks. Week 5 sucked. When I told the doctor that I may have gone back a week too soon, he said “not necessarily”. He said that the first week back is terrible no matter how long you wait.

The recovery has gone as well as can be expected. I can walk three miles pain-free. I drove to St. Louis and after four hours in the car, I got out and walked around like a normal person. I used to have to limp around for 10 minutes until the joint got warmed up. It’s the little things. Speaking of little things, I rejoice in walking up and down stairs. My knee stays in line with the rest of my leg. A couple of weeks ago I tried to walk 18 holes. I’m not quite ready for that yet. My goal is walk 9 in a couple of weeks and go from there.

Daily Dose of Server Frustrations
After a nice run of no crashes, there have been several in the last month. They happen about 5AM my time, so the site is usually only down for a couple of hours before I notice it. Many of you probably didn’t notice it all. It is, without question, the most frustrating part of my existence. I actually considered turning everything into static web pages. Then I backed off the ledge.

I could go back to a web host. But they don’t really help. When something goes wrong, they just shut down the site so I don’t steal resources from other sites they’re hosting. Then I have to turn off all of my plugins to try to convince them to turn me back on. No, I won’t be going back to that. With Digital Ocean, I pay $120 per year and I can power cycle my server any ol’ time I want. But I’m not a server admin – not even close. So when my CPU spikes or my RAM spikes, I really have no idea what to do about it.

I’d like to find some blogging software that’s not WordPress and try that. I like Ghost, but it doesn’t support comments and that’s the best part of this site.

Free Stuff
Mike Alexander and I wrote some books recently.


Excel Power Programming Spreadsheets Bookshelf


Excel 2016 Formulas Spreadsheets Bookshelf

Rewrote, actually. And you can have one of them if the cost of shipping it to you is reasonable and if you complete this survey about your Excel spending habits:

https://www.surveymonkey.com/r/D5SXM3M

I’ll pick two survey respondents (one for each book) at random to receive a book. All the normal rules apply: if it’s illegal or causes me any heartache, it’s forbidden. You can take the survey even if you don’t want a book. At the end of the survey you can provide your name and email to be entered. If you skip that step, your survey results will still be included.

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.