Paste Special Multiply

Thanks to Dennis Wallentin for another article:

Paste Special Dialog – Operating on a Range of Data

For some of you this is already known, but I know there are users who are not aware of the power of the Paste Special Dialog.

Suppose you have been asked to create a budget for next year. The budget will be based on last year’s sales figures and the budget should be 10% more than last year.

As usual, there are several ways to skin a cat. A quick method to achieve it is to use the Paste Special Dialog. The following screen shot shows the details:

  • First we enter 1.1 (which represents the 10% increase) in an empty cell in the worksheet.
  • Next we copy the source, i.e. the cell that holds the value.
  • Then we select the targeted range of data that is to be increased with 10%.
  • Next we select the command Paste Special… from the menu by Right Clicking on the targeted range.
  • In the Dialog we select the operation Multiply and then hit the OK button.

The output of the operation is showed in the following screen shot:

In my opinion this is an excellent way to quickly update a range of data.

Recently I made a suggestion to improve the operation. Instead of using a cell in a worksheet as the source I suggested to add a numeric field in the Paste Special Dialog. The following picture shows the idea:

If You think it’s a good idea, then I suggest that You vote for it at UserVoice:
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/17352001-paste-special-add-a-numeric-field-when-using-one

Enjoy!
Thanks and all the best,
Dennis

Introducing Code Manager

AS you know, we here at DDoE never take a vacation. Except for the day after Thanksgiving. And Christmas. And about 325 other days in the year. Thankfully, Dennis Wallentin wrote about a new tool he’s developed and allowed me to post it here.

Introduction

This project started out one year ago, in November 2015. It’s been a long road and not always an easy way. The project itself has been difficult. I’ve been forced to rewrite it one time (crash) and been struggling with several “funny” issues.

Life is not always an easy ride. Health issues has from time to time prevent me from continue to develop this project as well as several other projects. A To-Do-List should, over time, decrease and not, as in my case, increase. But life can sometime be a bug that effectively prevent us from what we want to do, right?

I must explicitly thank Ron (de Bruin). Not only for being a good friend but for giving me great support with this project. The truth is that without him this project would not have been finalized. I’m amazed what Ron has done for the online community over the years, especially for those who work with MS Excel and MS Office on Apple’s desktop.

Anyway, the Code Manager is a project that I will continue to develop by adding new tools to it. Compared with other development tool the VB Editor is outdated. It’s remarkable that Microsoft still haven’t realized that VBA and the VB Editor is the first choice for the larger group of Excel power users and developers. The best Microsoft can do is to do a total makeover of the VB Editor and update it to today’s standard!

What is Code Manager?

It’s a toolbox for professional Excel/VBA-developers which will include several tools for various coding tasks.

Code Manager can run on both x86 and x64 Windows platforms. It’s a managed COM add-in that can be used for both the x86 version of MS Excel 2010 and later as well as for x64 of MS Excel.

It will always be free of charge.

In the first version of Code Manager Toolbox only one tool is available

  • Code Indentation.

Code Indentation is a versatile tool to manage all VBA code. It can operate with code in the VB Editor as well as with standalone vb-files, cls-files and txt-files.

Overview of Code Indentation
The Code Indentation tool can work with VBA-code in the VB Editor in MS Excel and in standalone files with the extension of vb, cls and txt.

When working with code in the VB Editor we can target three levels of code:

  • Individual Procedures
  • Individual Code Modules
  • All Code Modules in active Workbook

To make it simple and easy to use these commands, i.e. to indent the code, it’s available in two ways:

Select the command Code Manager > Indentation in the VB Editor’s toolbar and select the operation you want to be carried out as the following image shows:

Right-click in the VB Editor and select the command Code Manager. Next, select the operation you want to execute as the following image shows:

To work with standalone files that contain VBA code and to interact between the VB Editor and standalone files, the Code Editor in Code Manager can be used. It’s also via the Code Editor that all settings can be made. The following image shows the Code Indentation tool in the Code Manager:

Feedback

In order to improve the tools, I welcome any feedback and suggestions of additional tools to be included in the Code Manager toolbox.

E-mail feedback and suggestions to: consult@excelkb.com

Download

To install it, first unzip the file and then execute setup.exe by just double clicking on the exe file and follow the instructions on the screen.

The zipped file can be downloaded from Ron de Bruin’s excellent site:
http://www.rondebruin.nl/win/dennis/codemanager.htm

Requirements

The following requirements must be met before using Code Manager:

  • Microsoft Windows XP and later.
  • Microsoft .NET Framework 4.5 and later.
  • Microsoft Excel 2010 and later.

The following tools have been used to develop Code Manager with:

License

The Code Manager is made available based on the MIT License (MIT).

Special thanks go to Ron de Bruin and Ken Puls.

© 2016 Dennis M Wallentin

Adding Stuff to the Top of a Dictionary

I wrote a KwikOpen addin that I use about a million times a day. I ran into a little nagging problem. When I Save As’d a file from the addin, it never showed up on the recently opened list. I finally decided to track down the bug. A while back, I switched my custom class storage method from Collection to Dictionary. I don’t remember why, but I’m sure it was a fine reason. I ended up with this Add method

I have this optional argument, bToTop, so I can add it to the front of the list. But as you can see from the commented code at the bottom, that argument is basically ignored. Dictionaries don’t allow you to insert values into specific locations and that code no longer works.

So why a bug? Because I only store the most recent 2,000 files, and I’m at that limit, any Save As’d file would become 2,001 and not written to disk. When I’d go to open a file, it would read in from the file again and, of course, that recently saved file was not there.

Surely there’s a quick and easy method for pushing something to the top. Nope. All I could find was rewriting the whole Dictionary.

In that code, I create a temporary Dictionary, dcTemp, put my Save As’d file in first, then fill in the rest, finally replacing the old Dictionary with the temporary one. That’s not exactly elegant, but it gets the job done. I tested it and found that the recently saved file was not on the top of the list. It was near the top, but I inserted it first, it should be at the top. Then I remembered that I read in Excel’s MRU before I read in my file. That means there are 50 files ahead of the one I just saved. No biggie, but it gave me an idea.

Instead of recreating the Dictionary, why don’t I just add it to the MRU? There are some websites about adding entries to the registry but that won’t work. Excel reads the registry when it opens and I wasn’t about to close and reopen the app. Another way to add a file to the MRU are to specify the arguments in the Open and SaveAs methods. I am saving a file. Now my Add method looks like this

The heavy lifting is done when I save the file

That lone True out there is the AddToMru argument. By getting rid of the .Execute method and doing the SaveAs myself, I also got rid of a problem where overwriting an existing file caused two warning prompts. Now there’s no need for me to add it to my list (the commented out code at the bottom) because Excel adds it to its list and that’s what I read first.

International Keyboard Shortcut Day 2016

It’s the first Wednesday in November and you know what that means. It’s International Keyboard Shortcut Day. The day when people from all over the world become far less efficient in an effort to be more efficient the rest of the year.

How to Participate

Pick on of the levels below and commit to advancing your keyboarding skills. You will be on your way to greater efficiency.

Participation Levels

Effecienado: When you’re in Excel, only use Ctrl, Shift, and the arrow keys to select cells, rows, and columns for at least one hour today. If you accidentally select a range with your mouse, select something else and do it again with your keyboard.

Key Master: Only navigate between applications with Alt+Tab. Only navigate between documents or tabs with Ctrl+Tab, Ctrl+PgUp/PgDown, or Ctrl+F6. Do this for at least four straight hours today. If you accidentally select an application, document, or tab with your mouse, go back to where you were and do it again with your keyboard.

Harry Keyboard Jr.: Put your wireless mouse on the credenza behind your desk. Only bring it to your desk when you absolutely have to, and return it when you’re done with that one activity. Do this for at least four straight hours today.

Storing Stuff in VBA Lists

You no doubt recall when snb wrote about Scripting.Dictionaires. Well, there’s more.

I use Collection objects in my custom class modules almost exclusively. It’s the only object, that I know of, that I can enumerate using For Each.

Outside of custom class modules, I use Dictionary objects. I used to avoid them because they weren’t built in to the language. I was always afraid of some dependency problem. But I’ve never seen one in all my years, so I’m over that now. The advantage of the Exists property and the ability to produce an array of keys or items is awesome. it’s probably more awesome than For Each, but I just haven’t made that leap yet.

And I never use ArrayLists because I never remember them. That’s not totally true. When I’m writing a procedure with a Dictionary and I need to sort, I kick myself for not using an ArrayList.

Here’s some features of Collections, Dictionaries, and ArrayLists.

Feature Collection Dictionary ArrayList
New Enum in class Yes No No
Exists No .Exists .Contains
Key Value paradigm Yes Yes No
Unique keys Yes Yes NA
Key data types String Any NA
Get keys No Yes NA
Auto create items No Yes No
Insert anywhere .Add(,,before,after) No .Insert
Output to array No .Keys or .Items .ToArray

There are other differences. Those are just the ones that are important to me. If there’s a difference that’s important to you, leave a comment. You can read everything you ever wanted to know about these objects at one of the pages below:

Collections: http://www.snb-vba.eu/VBA_Collection_en.html
Dictionaries: http://www.snb-vba.eu/VBA_Dictionary_en.html
ArrayLists: http://www.snb-vba.eu/VBA_Arraylist_en.html

New Computer Setup

I got a new computer at work. This is how big it is.


It’s got an i7-6700T @2.8GHz and 16GB or RAM. All in that little box. It also has Windows 10, which I’m finding quite acceptable. I guess I should have upgraded my home computer when it was free. I’m opposed to Microsoft’s Windows 10 revenue model on principal and that really hasn’t changed.

The other major change is that I have Office 2010 64 bit. You can read some of my comments at the bottom of this post. To be fair, Microsoft doesn’t recommend installing 64 bit unless you have a specific reason. I don’t have a good reason, I just want Excel to address as much memory as possible. And it does. And it’s super-fast. Except when it crashes. My email to IT reads as follows:

I give up. Office 64bit sucks. When you have time, I’d like it uninstalled and 32bit installed.

Thanks,
Dick

Finally, I’m making the switch from Firefox to Chrome. It’s been an adjustment, but generally I like it. My biggest blocks to switching have been Type Ahead Find and the treatment of diverted tabs. I installed an extension called Type-ahead Find that takes care of the first problem. And I installed an extension called Inoreader Companion which takes care of the second problem within Inoreader. The vast majority of diverted tabs for me happen in Inoreader. I still don’t like how Ctrl+Click opens the tab just to the right rather than at the end, but it’s only a minor annoyance and I’ll get used to it.

Here are the rest of the settings I make when I do a clean install.

Windows

  • Pin programs to taskbar.

    That’s Outlook, Chrome, Excel, an RDP to our accounting software, SQL Server Management Studio, and Notepad++. I leave Outlook running all the time so it processes my client side rules. I start the rest of them each morning by holding down the Windows key and typing 23456. Everyone in my office closes the entire application when they’re done with it (except Outlook). That means every time they want to work in Excel, they start Excel. They don’t have 16 GB of RAM, either, so it takes a few seconds. I just don’t understand why they don’t leave it running all day.

  • Uncheck Hide Extensions for Known File Types
  • Check Show Hidden Files. I did these the old fashioned way, but have discovered that they are on the View menu in Windows Explorer. They’re called File name extensions and Hidden items. Just another little Windows 10 convenience. Although I still argue that File extensions should be on by default.
  • I use Ctrl+Alt+Down and Ctrl+Alt+Right for a couple of Excel macros so I can’t have them rotating my screen around.

Excel

  • Uncheck Show Mini Toolbar on Selection
  • Include this many sheets = 1
  • Uncheck Use GetPivotData functions for PivotTable references
  • Turn off Autocorrect – Replace as you type: Internet and network paths with hyperlinks
  • Uncheck Allow editing directly in cells
  • Uncheck Show paste options button when content is pasted
  • Show this number of Recent Documents = 50
  • Uncheck Show all windows in taskbar
  • Add Max and Min to Status bar

Visual Basic Editor

  • Tools – Options – Break in Class Modules
  • Uncheck Auto Syntax Check
  • Check Require variable declaration
  • Comments to gray and Keywords to green. I don’t write a ton of comments. I’m a proponent of writing self-documenting code and only commenting when necessary. But when I do comment, I certainly don’t want it slapping me in the face. If I need clarification, I’ll look for comments. Otherwise I prefer not to see them. I realize there are others in the VBA community who have the exact opposite opinion. I’m in favor of people having opinions about programming in VBA even if I don’t share them.

  • Add CommentBlock and Uncomment Block to Tools menu

Outlook

  • Compose messages in this format: Plain Text
  • When a new message arrives Play a sound – off
  • When a new message arrives Briefly change the mouse pointer – off
  • When a new message arrives Display a Desktop Alert – off
  • Check When replying to a message that is not in the inbox, save the reply to the same folder
  • Check Always send a read receipt. I hate that some people always request read receipts. But I also hate that prompt, so I just gave in.

SQL Prompt

  • Add space as special character
  • Put commas as start of row
  • Put space after comma
  • Add snippet gob = GROUP BY $PASTE$ ORDER BY $PASTE$ I have to copy the SELECT list for this to work. I wish I could make it work without copying. That is, it would group by and order by everything in the SELECT list that wasn’t an aggregate.
  • Add snippet ssfgross = SELECT * FROM dbo.OSASGrossMargin_vw WHERE GLYear = $DATE(yyyy)$ Just a query I start with a lot. We’re a fiscal year end, so the $DATE$ variable doesn’t quite work.

One last word about 64 bit office. You can’t program the menus in the VBE as I documented here. My temporary fix for that was going to be AutoHotKey. This is bad, so be sure to plug your nose if you choose to consider reading. Here’s my AHK script:

I’d go to the Immediate Window and type, for example, vbPrivate. It would expand into an Application Run statement and I’d press Enter to execute. I told myself that this is how I would access these procedures until I was able to rewrite this as COM add-in or a .Net thingy or however people automate the VBE these days. I’m pretty sure I would have just kept using this method and never actually rewrote it. Now that I’m switching back to 32 bit, it’s a non-issue.

I hope you’ve enjoyed this small glimpse into my computing life.

Deleting Pivot Table Drilldown Sheets

I tried to make drilling into pivot tables better once upon a time. I failed. Earlier this week, I read Debra’s blog post about showing details and deleting the sheets later. It got me thinking.

The problem I have is that her solution (and many others) rely on the Before_DoubleClick event. As you might imagine, I don’t double click to show pivot table details. I press the context menu key and choose Show Details from the menu. I need a different event or to capture that context menu item. I don’t think there’s any event that will allow me to identify new sheets only when they come from showing details of a pivot table. It doesn’t matter. The better answer is create my own shortcut.

In my Auto_Open and Auto_Close procedures in my PMW:

That’s Ctrl+Shift+D for the uninitiated. That will now run PTDrillDown

Lot’s of

in there. That’s the sign of really tight code, you know. This determines if the ActiveCell is in a pivot table by trying to set a PivotTable variable. If it’s in a pivot table, it next checks to see if it’s in the body (as opposed to row or column headers or filters). If it’s in the body, the code shows the detail, deletes any sheet with my special name, and names the resulting sheet with my special name. The special name lives in my MGlobals module.

And for the coup de grace, I have a class module that defines an Application variable WithEvents. I added this event procedure to it.

Whenever I switch off of the details sheet, it goes away. Now that’s keeping things tidy.