Browse by Tags

Going Home with Frozen Panes

I like to split a worksheet and freeze the panes so I can see the row and column headings when I scroll. Like me, I’m sure you’ve done it a million times. In that state, the Ctrl+Home shortcut will take you to the intersection of the split panes rather than A1 (where it takes you [...] Read More...

Published Tue, Jun 06 2013 8:30 AM by Daily Dose of Excel

Filed under: VBA

Excel corrupts certain workbooks in migrating from 2003 to 2007

I got a email from a client asking for help because Excel was “destroying,” to use his terminology, his 2003 workbook after conversion to the 2007 format. And, after analyzing the kind of change Excel made, I had to agree. The following in 2003 Figure 1 becomes, in 2013 (and in 2010), Figure 2 The [...] Read More...

Published Sun, Apr 04 2013 3:04 AM by Daily Dose of Excel

Filed under: Excel12, Bugs, VBA, Formulas, Names, Excel 14, Excel 2007, Excel 2010, Excel 2003, Excel 15, Excel 2013, bug

Swapping Strings the Tricky Way

Alfred Thompson posted recently on how to swap integers using only two variables. It used Exclusive Or, which I believe is just Or in VBA. He concluded with This is all fun and interesting in a geeky sort of way. But is it a good idea? No so much. For one thing it is tricky, [...] Read More...

Published Fri, Mar 03 2013 12:20 PM by Daily Dose of Excel

Filed under: VBA

An MSForms Treeview 2: Ready for beta testing

Hi everyone, Some time ago I announced I was working on “An MSForms Treeview” replacing the Common Controls Treeview with an all-VBA counterpart. This home-made treeview control will work on any Office version as of Office 2000, including 32 and 64 bit Office. I expect it will even work on MAC Office, but I’m still [...] Read More...

Published Fri, Feb 02 2013 10:20 AM by Daily Dose of Excel

Filed under: Classes, VBA Advanced, Collections, VBA, MVP, Events, Downloads, User Interface, ActiveX Controls, UI Design, Excel Advanced

An MSForms Treeview

If you have ever used the Treeview control from the “Additional controls” section, then you know what a versatile control this is to show hierarchically organized data. There are a couple of problems with this Treeview control: Compile errors due to a difference in how the control libraries are registered in 32 bits Windows’ System32 [...] Read More...

Published Thu, Jan 01 2013 4:24 AM by Daily Dose of Excel

Filed under: Classes, VBA Advanced, Collections, VBA, MVP, Events, Visual Basic Editor, Windows API, Userforms and Controls, ActiveX Controls, Controls, UI Design, Excel Advanced

The Range.Find method and a FindAll function

Two things that could be better about the Range.Find method have been 1) up-to-date and correct documentation, and 2) adding the UI’s ‘Find All’ capability to the Excel Object Model. As of Office 2013 neither has happened. Consequently, every time I want to use the Find method, I continue to have to jump through hoops [...] Read More...

Published Thu, Dec 12 2012 9:35 PM by Daily Dose of Excel

Filed under: VBA, Range Object, VBA Functions and Keywords, Excel Intermediate, Excel 15, Excel 2013, Range.Find documentation, Range.Find method, FInd all cells, search for format

Excel 2013, SDI and modeless userforms

Hi Everyone, With Excel 2013 we also got new document windowing in Excel; Microsoft decided to make Excel behave the same as Word: from MDI: to SDI: This causes havoc when one shows a modeless userform which should stay on top of all Excel windows: I’ve devised a way to prevent this problem and written [...] Read More...

Published Mon, Nov 11 2012 10:16 AM by Daily Dose of Excel

Filed under: VBA Advanced, VBA, MVP, Downloads, User Interface, Windows API, Userforms and Controls, Excel Advanced, Excel 15

Dir for Excel 2011 for the Mac

Hi all Like a lot of other things in VBA in Office 2011 Dir is not working the same as in Windows. Together with a great Apple Scripter DJ Bazzie Wazzie I create a VBA function that you can use instead. You can download a file from my site so you can test it. http://www.rondebruin.nl/mac.htm#Loop You [...] Read More...

Published Fri, Nov 11 2012 1:47 PM by Daily Dose of Excel

Filed under: VBA

Seven Segment Display

I was reading about seven segment displays over at Sparkfun and thought it would be a fun exercise in Excel. I’m sure it’s been done a million times, but not by me. The first one was VBA based. Type a number into a cell and this code fills cells to display the number as a [...] Read More...

Published Thu, Nov 11 2012 10:02 AM by Daily Dose of Excel

Filed under: VBA, Formatting, Math and Stats Functions

Placing Chart Data Labels

I made this chart in Excel 2003: I used this data: Vehicle 1/31/2012 2/29/2012 3/31/2012 4/30/2012 5/31/2012 6/30/2012 7/31/2012 8/31/2012 9/30/2012 Cars 603 615 627 634 646 659 672 679 693 Trucks 405 413 433 442 451 440 430 445 475 Vans 545 556 567 578 590 602 590 585 580 SUVs 465 512 527 [...] Read More...

Published Tue, Oct 10 2012 3:44 PM by Daily Dose of Excel

Filed under: VBA, Charting

Testing First

I’m not a TDD guy, but I am intrigued by it. With TDD, you write a test that fails and then write code to make it pass. Then you write another test that fails and write code to make it pass. Every so often you stop to refactor the code. I’m not an expert, so [...] Read More...

Published Tue, Oct 10 2012 10:20 AM by Daily Dose of Excel

Filed under: VBA

Model business dependencies in an Excel worksheet

The Civilization game dependency tree How to build a dependency tree when there is no obvious connection between the business model and an Excel model. A long time ago I spend a lot of time playing the strategy game Civilization. For those not familiar with the game, one of its features was “advances.” Each advance [...] Read More...

Published Thu, Aug 08 2012 5:42 PM by Daily Dose of Excel

Filed under: Excel12, VBA, Formulas, User Interface, Excel 14, Games, Excel 2007, Excel 2010, Excel 15, Excel model, Excel 2013, arrow, business model, dependent, precedent, visualization

Installation Instructions for Office add-ins

I updated the installation instructions for Office VBA add-ins to include Office 2010, Office 2007, and Office 2003. The updated material is at http://www.tushar-mehta.com/excel/software/installation/index.htm. Tushar Mehta Read More...

Published Sun, Jun 06 2012 1:28 AM by Daily Dose of Excel

Filed under: Excel12, VBA, VBA Basics, Excel 14, Excel Basic, Excel 2007, Excel 2010, add-in, VBA Basic, Excel 2003, macro warning, security, installation

Minimum and maximum values of numeric data types

There has been many an occasion when I have wanted programmatic access to the maximum or minimum or smallest value of a data type. Some programming languages have built-in support through names like MaxInt. VBA, unfortunately, is not one of them. I decided to “translate” the documentation defining the different data types into code. The [...] Read More...

Published Tue, Jun 06 2012 7:21 PM by Daily Dose of Excel

Filed under: Excel12, VBA, VBA Basics, Excel 14, Excel 2007, Excel 2010, excel 2010 32-bit, excel 2010 64-bit, VBA Basic, vba 7, vba 6

Copy Chart as a Picture

I needed to copy a chart to a picture, but I wanted it to be an enhanced metafile (EMF) which is kind of like a vector graphic picture format. EMF graphics scale well when the page resizes. A user would … Continue reading → Read More...

Published Sat, May 05 2012 6:55 PM by Daily Dose of Excel

Filed under: VBA, Windows API, Charting, VBA Code Library

Syndication

Recent Posts

Tags

News

Archives

Going Home with Frozen Panes

Excel corrupts certain workbooks in migrating from 2003 to 2007

Swapping Strings the Tricky Way

An MSForms Treeview 2: Ready for beta testing

An MSForms Treeview

The Range.Find method and a FindAll function

Excel 2013, SDI and modeless userforms

Dir for Excel 2011 for the Mac

Seven Segment Display

Placing Chart Data Labels

Testing First

Model business dependencies in an Excel worksheet

Installation Instructions for Office add-ins

Minimum and maximum values of numeric data types

Copy Chart as a Picture