Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Daily Dose of Excel

Sort by: Most Recent | Most Viewed | Most Commented

  • Pages to Print

    In Microsoft Word’s print dialog, there’s a textbox called Pages. In it, you can specify which pages you want to print by separating individual pages and page ranges by a comma. I have a small app wherein I enter a starting serial number and an ending serial number. The output is [...] Read More...
    Filed under: ,

  • Good Row Bad Row

    In the office, I sometimes deal with a table of values. In that table, there are good rows, and bad rows. I want to quickly put the good rows to the top of the table, and leave the bad rows at the bottom of the table. So I take the first unused column, give it a rubbish [...] Read More...

  • Runway Math

    From Wikipedia: Runways are given a number between 01 and 36. This indicates the runway’s heading: A runway with the number 36 points to the north (360°), runway 09 points east (90°), runway 18 is south (180°), and runway 27 points west (270°). Thus, the runway number is one tenth of the runway centerline’s magnetic azimuth, [...] Read More...

  • Test an Object and Its Property

    When you need to test an object’s property, there are times when you must also test that the object exits. E.g. If Not rControl Is Nothing Then     If IsEmpty(rControl.Offset(0, 1).Value) Then         rControl.Offset(0, 1).Resize(1, 7).Value = clsTimeSheet.TimeArray     Else         MsgBox "Operation Failed"     End If Else     MsgBox "Operation Failed" End If This [...] Read More...
    Filed under: ,

  • Personal.xls

    I’ve been saying for years that I was going to get rid of Personal.xls and put all that code into add-ins. Well, I finally did it. Last week, Code for Excel and Outlook posted Which Macro Shortcut Keys Do You Use. That seems like a good opportunity to layout what I did. I [...] Read More...
    Filed under:

  • String Building Class

    I read that someone uses a string builder class in vba. For some reason, that thought had never occurred to me, but I thought I’d give it a try. Here’s my first attempt. Private msText As String Public Property Get Text() As String     Text = msText End Property Public Sub Append(sText As String, Optional sDelim As [...] Read More...
    Filed under: ,

  • Properties and Object Continued

    Yesterday, I was writing about how you can access worksheet functions in different ways. And I was going on and on about it to the point where I’m now recreating a slimmed down Excel Object Model in VBA. How did this topic get away from me so fast? Anyway, I set up the CCell [...] Read More...
    Filed under:

  • Properties and Objects

    I know you’re thinking that class module week is over, and you’re right. This post is more about the Excel Object Model and how it is set up. It stems from a couple of comments on another post that correctly point out that Application.Transpose(...) and Application.WorksheetFunction.Transpose(...) do the same thing. For this example, we’ll [...] Read More...
    Filed under:

  • Creating a Parent Class

    Hasn’t it been a great class module week? It all started with Rob’s post about custom collection classes, or what I call parent classes. If I make a Person class, I want a People class. If I make an Invoice class, I want an Invoices class to hold all of them. Back in [...] Read More...
    Filed under:

  • Preventing Workbook_Open events from running (2)

    In Preventing auto_open and Workbook_Open events from running I descibed how to do exactly that. Well, it appears that the methods I gave do not always work with Excel 2007. A visitor of my website complained he could not prevent his Auto_Open macro from running and during a BeamYourScreen session I took over his desktop and [...] Read More...
    Filed under: , , ,

  • Forever Stamps

    The USPS is raising rates again, or at least proposing it. I have very specific plans for when I’m appointed Postmaster General. But until then, I’ll just try to determine if the Forever Stamps are a good investment. =RATE($A3-$A$2,0,-B$2,B3)*365 RATE(nper,pmt,pv,fv,type,guess) nper = the number of days between 7/7/10 and 5/14/07 pmt = no payment pv = if you [...] Read More...
    Filed under:

  • Class Property Fixed Values

    In my previous post on a Custom Collection Class, I used an example of a People collection with Person items. As well as First Name, Last Name, and City, I had originally included the Gender property (Female, Male, Unknown), but removed it just prior to posting. I purposely left it out because I didn’t want to distract [...] Read More...

  • Button image add-in for 2007 and 2010

    Hi all I want to let you know that I upload a new version of Jim Rech’s button image add-in today. You can find it in the “Find the names of the button images” section on my Ribbon page. This great Add-in is working now in Excel 2007 and in Excel 2010. While you there check out also my [...] Read More...
    Filed under: , ,

  • Make Plural

    As part of “class module week”, I need a function that takes a class module name and makes it plural. I’d like to catch more than 95% of standard nouns and throw in a few non-standard ones for good measure. Here’s what I have so far. Function MakePlural(sWord As String) As String       [...] Read More...
    Filed under:

  • Public Variables in Class Modules

    Rob’s post made me revisit some class module code, so it’s probably going to be class module week. Stop groaning out there. First, a quick discussion on the merits of property statements vs. public variables. Rob comments: I don’t see the point of traditional public property get/set if only to set a private variable. It [...] Read More...
1 2 3 4 5 Next > ... Last »
Copyright Excel User Group and the relevant contributors, 2009. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.