Nasty little F Lock key…

No really, that is it’s name!

I was trying to figure out why pressing F4 wasn’t toggling from relative to absolute referencing in my Excel formulas, and was starting to think I found a bug in Excel 2013.  But then other stuff started to get weird too…

  • F4, which I use to toggle absolute and relative formulas all the time seemed to do nothing.  But when I was just in a worksheet, and not the formula, it opened a new workbook.
  • F2, which I use to get into formula editing mode, started undoing my last action… which is really scary as I still don’t know if I un-did anything important. I only actually realized this after I typed a formula, clicked on it, pressed F2 and it went away!
  • Alt F11, to get in the visual basic editor did nothing at all.  What the…?

After finding out that it was doing the same thing in a new workbook in Excel 2010, I realized that this was bigger than just Excel.

Apparently I fat-fingered the F Lock key on my (Microsoft Natural) keyboard, which tells the keyboard to start using alternate commands.

Gave me a bit of a heart attack on a Friday afternoon!

My Workbook Got Stuck in a Cyclone!

I thought I actually posted about this some time back, but I don’t see it. It’s driven me nuts before, and is doing it again.

I have to print out our massive financial model for a meeting tomorrow. It’s almost 120 pages, and is laid out in a mix of colour and black and white, landscape and portrait settings.

The first time I ever saw this I was floored, as I thought Excel was turning each page just to drive me nuts. As it happens, it’s a little more predictable (although no less annoying.)

When I print my model, here’s the output of the pages:

Colour pages (landscape):

    

Colour pages (portrait):

    

Okay, so no biggie. I’d rather have the top of the colour landscape pages on the left so I didn’t need to turn them before putting in a left hand binding, but at least it’s consistent. At least, it is until I start mixing in some non-colour worksheets:

Black & white (landscape):

Rotated 180 degrees from the colour worksheet! What the heck is that about? (Yes, Top should be written the other way up, I just wanted this to be easy to read.)

Black & white (portrait):

    

Seriously? 180 degree rotation from colour again?

Run that out for a bit…

So now picture that you’ve got 120 pages, mixed colour and black and white, mixed landscape and portrait. I need to bind at the left, and want the top of the pages to be consistently like the colour landscape and portrait black and white, so the I can bind them on the left side. I’m going dizzy trying to sort this out!

There’s no setting to change this that I can find, so it’s a process of going through each pile of paper, one sheet at a time, to make sure none get missed. What a waste of time!

I’m not sure if this is a printer thing, or an Excel thing. It did it in Excel 2010, and is doing it in 2013.

I’m curious, if you have a colour printer, do you see similar effects?

Updating Page Sizes

This drives me crazy. I’m not sure if it was the move to Excel 2013, or if it was something else…

I have this massive model, and we print it out on 11×17 paper. Somewhere, sometime, Excel decided that this is a “Custom page size”, which causes me problems. I need to reset all the pages to 11×17. Easy right? Select all the worksheets, go to Page Layout à Size and choose 11×17.

Not so fast… if you do that, it replicates ALL the print settings including orientation, margins, fit to x pages by x pages, etc.. Nasty stuff. You can actually see why when you record a macro to change the paper size. This is what I get:

  1. Sub Macro1()
  2. '
  3. ' Macro1 Macro
  4. '
  5. '
  6. Application.PrintCommunication = False
  7. With ActiveSheet.PageSetup
  8. .PrintTitleRows = "$1:$8"
  9. .PrintTitleColumns = ""
  10. End With
  11.  
  12. Application.PrintCommunication = True
  13. ActiveSheet.PageSetup.PrintArea = ""
  14. Application.PrintCommunication = False
  15. With ActiveSheet.PageSetup
  16. .LeftHeader = ""
  17. .CenterHeader = ""
  18. .RightHeader = "Printed &D &T"
  19. .LeftFooter = ""
  20. .CenterFooter = ""
  21. .RightFooter = ""
  22. .LeftMargin = Application.InchesToPoints(0.7)
  23. .RightMargin = Application.InchesToPoints(0.7)
  24. .TopMargin = Application.InchesToPoints(0.75)
  25. .BottomMargin = Application.InchesToPoints(0.75)
  26. .HeaderMargin = Application.InchesToPoints(0.3)
  27. .FooterMargin = Application.InchesToPoints(0.3)
  28. .PrintHeadings = False
  29. .PrintGridlines = False
  30. .PrintComments = xlPrintNoComments
  31. .PrintQuality = 600
  32. .CenterHorizontally = False
  33. .CenterVertically = False
  34. .Orientation = xlLandscape
  35. .Draft = False
  36. .PaperSize = xlPaper11x17
  37. .FirstPageNumber = xlAutomatic
  38. .Order = xlDownThenOver
  39. .BlackAndWhite = False
  40. .Zoom = False
  41. .FitToPagesWide = 1
  42. .FitToPagesTall = False
  43. .PrintErrors = xlPrintErrorsDisplayed
  44. .OddAndEvenPagesHeaderFooter = False
  45. .DifferentFirstPageHeaderFooter = False
  46. .ScaleWithDocHeaderFooter = True
  47. .AlignMarginsHeaderFooter = True
  48. .EvenPage.LeftHeader.Text = ""
  49. .EvenPage.CenterHeader.Text = ""
  50. .EvenPage.RightHeader.Text = ""
  51. .EvenPage.LeftFooter.Text = ""
  52. .EvenPage.CenterFooter.Text = ""
  53. .EvenPage.RightFooter.Text = ""
  54. .FirstPage.LeftHeader.Text = ""
  55. .FirstPage.CenterHeader.Text = ""
  56. .FirstPage.RightHeader.Text = ""
  57. .FirstPage.LeftFooter.Text = ""
  58. .FirstPage.CenterFooter.Text = ""
  59. .FirstPage.RightFooter.Text = ""
  60. End With
  61.  
  62. Application.PrintCommunication = True
  63.  
  64. End Sub

This is what I need:

  1. Sub Macro1()
  2. With ActiveSheet.PageSetup
  3. .PaperSize = xlPaper11x17
  4. End With
  5. End Sub

Actually, even more pointed, this will fix it without messing all my other settings up:

  1. Sub Macro1()
  2. Dim ws As Worksheet
  3.  
  4. For Each ws In ActiveWorkbook.Worksheets
  5. ws.PageSetup.PaperSize = xlPaper11x17
  6. Next ws
  7.  
  8. End Sub

I know why the code records as it does, as Excel doesn’t know what settings I truly need, so it records the current state of all PageSetup stuff. But I sure wish when I tried to update one print setting via the user interface that it gave me the ONE I changed, not everything.

PowerPivot – It sure would be nice if…

I’ve been doing a lot of work with PowerPivot where I connect to databases. My normal development cycle is as follows:

  • Connect to a view or table, pulling in ALL columns
  • Work through my data scenario until I’ve worked out my logic and solve the issue I’m trying to solve
  • Cut the table/view query down to just the required columns
  • Add WHERE clauses to cut the data down to just the bare minimum I need
  • Deploy to my users

By doing all this, I really focus on trying to optimize the file size and refresh time as much as possible.

As I do this, I cut the number of columns out, then flip from the GUI table view to SQL to add my WHERE clause(s). One thing that I find that really sucks though, is that when I do flip the “Table Properties” to SQL view, it comes out like this:

  1. SELECT [dbo].[vw_opt_MemberProfiles].[AccountType],[dbo].[vw_opt_MemberProfiles].[CustomerType],[dbo].[vw_opt_MemberProfiles].[SortCode_Account],[dbo].[vw_opt_MemberProfiles].[SortCode_LastName],[dbo].[vw_opt_MemberProfiles].[ClubMemberCode],[dbo].[vw_opt_MemberProfiles].[Full_Name],[dbo].[vw_opt_MemberProfiles].[LastName],[dbo].[vw_opt_MemberProfiles].[FirstName],[dbo].[vw_opt_MemberProfiles].[MemberCardNumber],[dbo].[vw_opt_MemberProfiles].[GranCert_Previous],[dbo].[vw_opt_MemberProfiles].[Gran_Sold_Date],[dbo].[vw_opt_MemberProfiles].[Gran_Sold_To] FROM [dbo].[vw_opt_MemberProfiles] WHERE [dbo].[vw_opt_MemberProfiles].[SortCode_Account] <> '#'

I would LOVE to see it come out more like this by default:

  1. SELECT
  2. [dbo].[vw_opt_MemberProfiles].[AccountType],
  3. [dbo].[vw_opt_MemberProfiles].[CustomerType],
  4. [dbo].[vw_opt_MemberProfiles].[SortCode_Account],
  5. [dbo].[vw_opt_MemberProfiles].[SortCode_LastName],
  6. [dbo].[vw_opt_MemberProfiles].[ClubMemberCode],
  7. [dbo].[vw_opt_MemberProfiles].[Full_Name],
  8. [dbo].[vw_opt_MemberProfiles].[LastName],
  9. [dbo].[vw_opt_MemberProfiles].[FirstName],
  10. [dbo].[vw_opt_MemberProfiles].[MemberCardNumber],
  11. [dbo].[vw_opt_MemberProfiles].[GranCert_Previous],
  12. [dbo].[vw_opt_MemberProfiles].[Gran_Sold_Date],
  13. [dbo].[vw_opt_MemberProfiles].[Gran_Sold_To]
  14.  
  15. FROM [dbo].[vw_opt_MemberProfiles]
  16.  
  17. WHERE [dbo].[vw_opt_MemberProfiles].[SortCode_Account] <> '#'

It would sure make it a LOT easier to read, and a LOT easier to work with.As it is, I now copy my code out of PowerPivot, then head over to Instant SQL Formatter.  Copy, paste, format, copy and paste it back into PowerPivot.  A heck of a lot easier to read, but also an unnecessary pain in the behind.

I’m confused

Back in the days of classic Excel (versions 97 through 2003), I displayed about 10 toolbars, working out to hundreds of commands represented by tiny icons. We recognized them, and were able to have all of our most used commands one click away.

Then came 2007 which gave us the Ribbon in its place. The Ribbon consumed as much real estate as three rows of toolbars, and gave us a lot less commands one click away. The story we heard was how Microsoft was trying to make the experience easier for new users… making commands more discoverable. The revised system was supposedly more logical than all the commands buried under menus in the old structure.

To some extent, I’d agree that Microsoft accomplished their goals here. The applications are much less intimidating than they were for new/less experienced users. The issue is that it smacked the power users pretty badly. I still feel that I’m less efficient with the Ribbon than I was with the toolbar hierarchy, and I’ve written the book on how to customize the user interface. The only reason I got into ribbon customization in the first place was to try and get some of that efficiency back that we lost under the new paradigm.

Interestingly, as we can see with the copy/paste icons in Office 2010, Microsoft is starting to move toward and icon basis again, without as much text:

I wonder if this is to make it easier to port the application to other languages? I also wonder how long it will be before we start seeing a Ribbon with no text on it at all? After all, it just wastes space when you know what the commands do.

Now, here’s where I get confused… I installed Internet Explorer 9. And here’s the basic install:

This is discoverability? I’m not saying I want a full blown Ribbon here, but you can’t tell me that these two philosophies are the same? The menus are gone, granted that’s consistent, but where are the favourites that I used to be able to have one click away? I actually had to download a toolbar for it. In fact, I’ve found the lack of discoverability of controls to be so frustrating that I went and installed Firefox. Unfortunately this seems to be the new thing.

I don’t get the completely opposite directions here. In one app we’re putting in big, bloated user interfaces to be in the users faces. In the other we’re trying to remove it all and make them hunt for it. What gives?

Installing RSAT on Windows 7 SP1

Today we began the process of migrating away from VMWare to Microsoft Hyper-V for our server farms. Something I’m sure that Microsoft would be pretty happy to hear. And yet I got burned by an issue in the process that irks me.

I keep my OS pretty current, and installed Windows 7 SP1 as soon as it was pushed out in Windows Update. Today we go to install Microsoft’s Remote Server Administration tools so that I can connect to Hyper-V to build and manage my Virtual Machines, and it won’t install. What the hell? I get a nice little error message telling me “This update is not applicable to your computer.” Like hell it’s not!

After some searching, I found out that someone has come up with a route around this issue to get it to work correctly, which you can find here.

Microsoft has acknowledged it as an issue. In their KB’s wording: “Microsoft has confirmed this to be by design, as RSAT was designed for Windows 7 RTM version. A newer version of RSAT is slated to be released in the future.” Their advice is to uninstall SP1, install RSAT, then reinstall SP1 again. To me that sounds more dangerous than the route I went to fix it.

Personally, I don’t think this is good enough. If this is truly “by design”, then someone needs a smack upside the head. Microsoft wants people to keep their software current, and these are the exact people getting smacked!

I get that software is tough to deploy, but if the route I went is all that’s needed to fix it, surely someone could roll up a quick hotfix to release in a few hours.

Enabling Outlining Commands on a Protected Worksheet

I have a financial model that I set up using a grouping in some key places so that I could collapse sections of the model when I didn’t want to look at them. As I was handing off the model to someone else to work with, I wanted to protect the worksheets, but unfortunately there is no setting in the user interface to allow for expanding/collapsing the outlining tools when the sheet is protected. In fact, trying to do so gives you the following message:

I found this a little frustrating, but gave up on it. I expanded the model completely, protected the sheets and let the users have at ‘er.

Tonight at VBAExpress.com though, I was posting on a thread where the user had included the following in their code:

Sh.EnableOutlining = True

Wow! So obviously there IS a way to enable the outlining tools when the worksheet is protected, right? I ran the macro I had modified for the user and sure enough it worked. Cool!

So then I opened up a copy of my model and:

  • Ran the following code: Activesheet.EnableOutlining = True
  • Protected the worksheet

I didn’t work. What the hell?

After a little sleuthing I found out what the issue was. In order for the EnableOutlining to take effect, you must run the code that protects your worksheet with the userinterfaceonly:=true argument.

The unfortunate part of this is that userinterfaceonly:=true doesn’t stick between sessions. So that nice macro free workbook is now going to have to be saved into an xlsm format with the following code in it:

Private Sub Workbook_Open()

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets

With ws

.Protect userinterfaceonly:=True

.EnableOutlining = True

End With

Next ws

Application.ScreenUpdating = True

End Sub

 

That shouldn’t be necessary in my opinion, but whatever. A macro laden file is a small price to pay for the functionality. Man I love VBA!

Built in Ribbon Customizations in Office 2010

I know it’s been forever since I’ve blogged here, and I’m sorry to have to revive this with a rant, but…

I was writing an article up for how to customize the Office Ribbon in Excel 2010, and thought I’d build a custom Auditing Tab. Using the built in groups is REALLY easy, but there were commands there I didn’t need. So I figured that I’d try to make some custom groups, insert the commands I wanted, and see how it worked. Even better, I know that you can now export the customizations, so I could even share them!

To build a custom Ribbon tab:

  • Right click the Ribbon and choose “Customize Ribbon”, or go to FileàExcel OptionsàCustomize Ribbon.
  • Click New Tab
  • Select the tab and choose Rename
  • Go to the “Choose commands from” dropdown and choose Main Tabs
  • Find the groups you like, select them and click the Add>> button

To add a new group to a tab, first select the tab then:

  • Click New Group
  • Select the group and choose Rename
  • Drill down to the commands you like (left window), select them and click the Add>> button

Overall it’s not too complicated really.

So here’s the setup I did for my Auditing Tab… (I shrunk it so that you only see stuff pertaining to that tab):

As you can see, I created custom Formula Auditing, Other Tools and Sort & Filter groups. (I don’t know why I use the default Sort & Filter as well a custom one, but there you have it. At any rate, it doesn’t change this… the tab looks like crap!

The icons for Trace Precedents, Trace Dependents and Remove Arrows are all large and very blocky looking. Same with the Sort buttons on the custom group. Why is it that they are shown nice and small on the built in group, but not the custom one? And isn’t it interesting how Microsoft broke the 1 or 3 to a row rule with the Sort group? Makes it very obvious here!

If you want the customization file to try this yourself, click here.

Personally, I’m happy enough with the way the built-in groups work, but I think there should have been a bit more control on the sizing of the icons here. It seems that the need for my book (RibbonX – Customizing the Office 2007 Ribbon) isn’t dead yet, but honestly, it should be!

The danger of being out of date…

Tonight I discovered that my site had been hacked… as it turns out I was running an old version of WordPress and there was some injection code fed into a security vulnerability. I’ve upgraded to the latest version, which has cured that issue, but in the process some things seem to have occurred:

  • I’ve got some funny looking code samples
  • I’ve got a weird A character showing up all over
  • I’ve lost my navigation to the RibbonX TOC

I’ll try and fix this up over the next few weeks (kind of busy), but my main priority is going to have to be my main site, which still has some issues and needs a pretty major upgrade…

Flowchart Programming

This has to be one of the weirdest little programs I’ve ever worked with…

Back in 2003 I built a database to track our liquor inventories at work. It’s fairly simple in that it has a table of product details like name, price, size, cost, etc… and a table that records my inventory counts. We used a little Palm Pilot with a built in Symbol scanner to collect the scan date, UPC code and quantity on hand. As we estimate our partial bottles that sit on the bar, we record decimal quantities on hand. (i.e. 2.1 bottles.)

This has worked really well for the last few years, but as with all technology, it wears out. While the database is fine, the Palm Pilot is another matter. The screen calibration went so far off and despite several resets I couldn’t fix it, rendering it useless. The bad news, of course, is that the unit we used has been discontinued for about 2 years, and my last replacement unit… well… it’s now in the trash.

Fortunately I already had a new device on hand to use. We’d installed a new system in another department last year that came with a handheld ruggedized scanner. Very nice little unit made by Unitech (HT-630). We were so impressed that we bought a second one. One reason was that we now have a backup unit, but the other is that the vendor (whom I know fairly well) convinced me that I could use this as a replacement for my aging Palm units. “Oh yeah,” he told me, “it’s fully programmable. You’ll have no problem knocking up a little program for it. There’s a whole programming environment, you build the job, upload it and you’re good to go. It won’t be a problem for you.”

I love that people have that much faith in me sometimes… then there’s other times where I want to phone them up and…

At any rate, I’ve been meaning to get around to programming this new unit, but just haven’t been able to make it a priority. But since our little Palm Pilot blew up, that kind of forced the issue. And naturally it had to happen two days before quarter end. Great. So last Thursday/Friday I spent time trying to get this thing working in time for them to count inventory on Saturday. Nothing like a little pressure!

The odd thing is that this unit is programmed by building a flowchart. I’ve never seen the like before. To me flowcharts are used for documenting how the program works, not telling the program how to work. Very odd. The shot below is from the program I released on Friday to count inventory. (Yeah… I DID do it, but I wouldn’t say it wasn’t a problem!)

This was a real trip to work with. Ever seen a flowchart that is allowed to go multiple directions at once? I haven’t. To me flowcharts have a decision point between every directional choice, but this little program actually flows data along one arrow and the input screens along another one.

And well creating the flowchart is all good, you then have to configure every option of every node in the chart. You can’t copy an existing node that is setup correctly either… each option of each box has to be configured. Each link, each input, everything. This is just one of at least 14 screens to configure. Each line you see (incoming and outgoing) increases that count by 1, (image below has 20 screens to do) so it’s very tedious:

It took me about 1½ days to built this program. I spent a ton of time spinning my wheels trying to figure out how to make a lookup file work, then trying to figure out how to write the data to the text file, then trying to figure out how to upload the program to the unit, and then I thought I had it. Then I reviewed the collection file and figure out that it was submitting a full record every time a single piece was captured. So I’d get four records for every UPC code, one with the correct quantity, three with the prior… arrggh!

I also capture the count date and month end date. I was really happy to see that you could use a formula too, so I figured that I could use a formula to convert the count date to the month end date. I quickly jumped into the formula screen… and after staring at that for about 4 seconds I clicked the Help button:

Seriously? “Write pieces of C code”? Try web searching for some help there… even when I did find something, I couldn’t figure out how to make any sense of it!

In Excel this would be easy…. =If(Day(CountDate)<15,Eomonth(CountDate,-1),EOMonth(CountDate,0))

If anyone has any idea how to write that in C code, I’d be really grateful. There is no manual with this software and the “Help” files are about as useful as… well… they’re not. I ended up ignoring the formula and elected to capture the count date twice. I just have to edit the text file and replace one of them with the month end date right now, and I’m good to go.

At any rate, I DID get the program working in time, and deployed it out there. The staff are ecstatic as it is much simpler to use than the last one. Yay, it’s time to celebrate!

Almost.

Today I was trying to import the text file into the existing table in Access. Access is causing me a hell of a hassle as it wants to populate the AutoNumber index field with my text file data. I don’t get that. It was never an issue before. I even put headers in my data fields, but no luck. I may have to resort to VBA to use a SQL insert statement in a loop to get this licked.

Worse though is that I messed something up. All of the decimal counts have been truncated to whole numbers, which makes my count file useless. I HATE having to deploy new technology in a hurry without proper testing. Our only recourse now is to estimate our inventory this month end and deal with fixing the technology issue for next month end.

At any rate, this has been an interesting learning experience. Programming C using flowcharts… it’s just plain weird.