I need a quick way to create a filter by month on an autofiltered range. Right now I'm manually entering this:

That's tedious. One option is to create another column with just the month and year in it and filter on that, but I don't like my data cluttered. So I wrote this macro that creates the filter. It filters the list to one month based on the date in the activecell.

Sub FilterOnMonth()
   
    'Create a filter in the active column within a sheet's autofilter
    'Use the month of the activecell to filter the range
   
    Dim lMonth As Long
    Dim lYear As Long
   
    If IsDate(ActiveCell.Value) Then
        lMonth = Month(ActiveCell.Value)
        lYear = Year(ActiveCell.Value)
       
            'Check if there is an autofilter
            If ActiveCell.Parent.AutoFilterMode Then
           
                'Make sure activecell is within autofilter range
                If Not Intersect(ActiveCell, _
                    ActiveCell.Parent.AutoFilter.Range) Is Nothing Then
 
                    'Create filter
                    With ActiveCell.Parent.AutoFilter
                        .Range.AutoFilter ActiveCell.Column - .Range(1).Column + 1, _
                            ">=" & DateSerial(lYear, lMonth, 1), _
                            xlAnd, _
                            "<=" & DateSerial(lYear, lMonth + 1, 0)
                    End With
                End If
            End If
    End If
   
End Sub

Recently, someone asked me if there was a better way to manage add-ins in Excel 2007. Specifically, he wants something better than Office Menu - Excel Options - Addins - (wait) - Go. Here's a recent edition to my Personal.xls

Sub ShowAddins()
   
    Dim wb As Workbook
   
    'Dialog won't show if there's no workbook showing
    If ActiveWorkbook Is Nothing Then
        Set wb = Workbooks.Add
    End If
   
    'Show addin dialog
    Application.Dialogs(xlDialogAddinManager).Show
   
    'Close wb if it was created
    On Error Resume Next
        wb.Close False
       
End Sub

And here's how I added it to my QAT (Quick Access Toolbar). Click on the little down-arrow next to the QAT and choose More Commands...

Visit PTS Installing an Add-in in Excel 2007 for more on Add-ins.

Visit Ron de Bruin's How do I create a PERSONAL.xls(B)... page for more on Personal.xls.

Hi everyone,

I have had a Dutch article on XML and Excel on my site for quite some time now, but never got round to translating the thing into English.

Well, yesterday I found a bit of time during a long train-ride, so here it is:

XML And Excel

From the intro:

Microsoft Office 2003 Professional was the first Office version that took the XML standard seriously. The XML standard has been devised to ease the markup of data (especially on the web). A well known example of the use of XML are RSS feeds with which one can gather news from web pages. In reality, these so-called RSS-feeds are nothing less than XML files. This article introduces XML and shows some things that can be done with it (specifically in Excel).

Regards,

Jan Karel Pieterse
www.jkp-ads.com

As I mentioned earlier, I want to develop a framework generating app to create some of the code necessary to read and write to and from an Access database. This is iteration 1 of about 100.

Start with this table in Access

Set a reference (Tools - References) to Microsoft ActiveX Data Objects 2.8 Library.

Then run this code. (It won't work for you without changing the connection string.)

Sub MakeClass()
   
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim adField As ADODB.Field
    Dim sql As String
    Dim sCon As String
    Dim sType As String, sPrefix As String
    Dim sDeclare As String
    Dim sCode As String
    Dim sVariableName As String
   
    Const sNL As String = vbNewLine & vbTab & vbNewLine
   
    'create strings for ado connection
    sCon = "DSN=MS Access Database;DBQ=C:\Documents and Settings\Dick\My Documents\Payroll.mdb;"
    sql = "SELECT * FROM tblEmployees;"
   
    Set cn = New ADODB.Connection
    cn.Open sCon
   
    Set rs = cn.Execute(sql)
   
    'sDeclare will hold the declarations secton
    'sCode will hold the Property statements
    sDeclare = "Option Explicit" & sNL
   
    For Each adField In rs.Fields
       
        ConvertADOType adField.Type, sType, sPrefix
        sVariableName = "m" & sPrefix & adField.Name
       
        sDeclare = sDeclare & "Private " & sVariableName & " As " & sType & vbNewLine
       
        sCode = sCode & "Public Property Let " & adField.Name & "(" & sPrefix & _
            adField.Name & " As " & sType & ")" & sNL
        sCode = sCode & vbTab & sVariableName & " = " & sPrefix & adField.Name & sNL
        sCode = sCode & "End Property" & sNL
       
        sCode = sCode & "Public Property Get " & adField.Name & "() As " & sType & sNL
        sCode = sCode & vbTab & adField.Name & " = " & sVariableName & sNL
        sCode = sCode & "End Property" & sNL
       
    Next adField
   
    'copy from immediate window and paste into class module
    Debug.Print sDeclare & vbTab & vbNewLine & sCode
   
    rs.Close
    Set rs = Nothing
   
    cn.Close
    Set cn = Nothing
   
End Sub

It uses this helper sub

Sub ConvertADOType(lType As Long, ByRef sType As String, ByRef sPrefix As String)
   
    If lType = 3 Then
        sType = "Long"
        sPrefix = "l"
    ElseIf lType = 202 Then
        sType = "String"
        sPrefix = "s"
    ElseIf lType = 135 Then
        sType = "Date"
        sPrefix = "dt"
    ElseIf lType = 5 Then
        sType = "Double"
        sPrefix = "d"
    Else
        sType = "Variant"
        sPrefix = "v"
    End If
   
End Sub

Obviously that's not going to cover all of the types, but it's a start. To be honest, I don't need this thing to do 100% of the work, just 98% of it. So if there's some touch up after the fact, I'm OK with that.

When I paste the resulting code in a class module, I get:

Option Explicit
   
Private mlEmployeeID As Long
Private msEmployeeFirstName As String
Private msEmployeeMiddle As String
Private msEmployeeLastName As String
Private mdtHireDate As Date
Private mdSalary As Double
   
Public Property Let EmployeeID(lEmployeeID As Long)
   
    mlEmployeeID = lEmployeeID
   
End Property
   
Public Property Get EmployeeID() As Long
   
    EmployeeID = mlEmployeeID
   
End Property
   
Public Property Let EmployeeFirstName(sEmployeeFirstName As String)
   
    msEmployeeFirstName = sEmployeeFirstName
   
End Property
   
Public Property Get EmployeeFirstName() As String
   
    EmployeeFirstName = msEmployeeFirstName
   
End Property
   
Public Property Let EmployeeMiddle(sEmployeeMiddle As String)
   
    msEmployeeMiddle = sEmployeeMiddle
   
End Property
   
Public Property Get EmployeeMiddle() As String
   
    EmployeeMiddle = msEmployeeMiddle
   
End Property
   
Public Property Let EmployeeLastName(sEmployeeLastName As String)
   
    msEmployeeLastName = sEmployeeLastName
   
End Property
   
Public Property Get EmployeeLastName() As String
   
    EmployeeLastName = msEmployeeLastName
   
End Property
   
Public Property Let HireDate(dtHireDate As Date)
   
    mdtHireDate = dtHireDate
   
End Property
   
Public Property Get HireDate() As Date
   
    HireDate = mdtHireDate
   
End Property
   
Public Property Let Salary(dSalary As Double)
   
    mdSalary = dSalary
   
End Property
   
Public Property Get Salary() As Double
   
    Salary = mdSalary
   
End Property

It looks right and it compiles, so that's good.

Here's my todo list:

• Create the class module rather than copy and paste
• Name the class module based on the table name
• Select the database and table from a list on a userform
• Establish Parent Child relationships between two classes
• Create functions to fill the class from a recordset
• Create a userform to add/edit/delete records

What if you ran through all the steps of this utility and you had a working CRUD program? That'd be pretty cool, I think. Sure, the userform wouldn't be just what you want, but it would give you a great starting point with the underlying structure in place.

Looking at the list, I expect this utility to be functionally complete in 2014 and polished in 2036. But I already used what I have here today and it saved me some time. Anything to add to this list?

Here's some code I have to generate a SQL statement

Public Function BuildRepSQL(ByRef clsRepLine As CRepLine, eType As aiDBStatus) As String
 
    Dim sReturn As String
 
    With clsRepLine
        Select Case eType
            Case aidbstatusdelete
                sReturn = "DELETE * FROM tblRepLine WHERE ReplineID = " & .RepLineID & ";"
            Case aidbstatusadd
                sReturn = "INSERT INTO tblRepLine (TxnLineID, ItemNumber, Quantity, SalesDollars) "
                sReturn = sReturn & "VALUES ('" & .TxnLineID & "'," & Nz(.ItemNumber) & "," & .Quantity & "," & .SalesDollars & ");"
            Case aidbstatusupdate
                sReturn = "UPDATE tblRepLine SET ItemNumber = " & Nz(.ItemNumber) & ", "
                sReturn = sReturn & "Quantity = " & .Quantity & ", "
                sReturn = sReturn & "SalesDollars = " & .SalesDollars
                sReturn = sReturn & " WHERE RepLineID = " & .RepLineID & ";"
        End Select
    End With
 
    BuildRepSQL = sReturn
 
End Function

Here's what a piece of that code looked like yesterday

Case aidbstatusupdate
    sReturn = "UPDATE tblRepLine SET ItemNumber = " & Nz(.ItemNumber) & ", "
    sReturn = sReturn & "Quantity = " & .Quantity & ", "
    sReturn = sReturn & "SalesDollars = " & .SalesDollars & ";"

The difference between those is that with the second one, 164 records in tblRepLine are made identical. I forgot to put the WHERE clause in there so it effected every record instead of just one. Whoops, thank goodness for backups. Luckily I have seven days of backups. Unfortunately, it seems I made this error more than seven days ago.

It only took me an hour or so to fix all the records. I'm glad it wasn't 1,164 records. It was a development database, not production, so it's going to get deleted someday anyway. But it's nice to have a development database that actually has correct data in it.

I've been working between Excel and Access a lot recently. I feel like I'm recoding the same stuff over and over - setting up classes, filling the class, writing back to the database, etc. I really need a framework generating application that sets up the classes and the basic structure of the code for me. It's one my 'to code' list.

Hi all

This week I create a small Add-in to add a few options to the contextual Table Tools tab.
This is a start and love to have feedback what you miss or like to see in a add-in like this.

This are now the options in the Add-in

1) Turn AutoFilter On or Off
2) Clear Filter of the ActiveCell's column
3) Special Filter options : Filter on selected Cell value, Color, Font color or Icon
4) Special Sort Options : Put selected Cell Color, Font Color or Icon on top and Custom Sort
5) Copy Visible data to a new worksheet
6) Copy visible data to a new workbook
7) Delete visible data
8)) Print Table
9) Split data of every unique value in a column to a new worksheet of the same workbook
10) Split data of every unique value in a column to a new worksheet in a new workbook
11) Split data of every unique value in a column to a new workbook in a new folder
This option creates a worksheets with hyperlinks to the workbooks so you can open them easy.

You can find the add-in together with a test workbook on the page
http://www.rondebruin.nl/table.htm

Thanks for looking at it
See also the links in the "More Information" part

Ron de Bruin
http://www.rondebruin.nl/tips.htm

Ribbon, you're nothing to me now. You're not a brother, you're not a friend. I don't want to know you or what you do. I don't want to see you at the hotels, I don't want you near my house. When you see our mother, I want to know a day in advance, so I won't be there. You understand?

Last week I said: "As a developer (not a user), my first impression of the Ribbon is a good one. I fully expect it to turn sour ..."

Consider it soured.

Whilst struggling with the Ribbon over the last week, I took stock of what is important in a menu. There are three basic things that I want to do with a toolbar/menu.

1. Run code when a button is pressed
2. Add/delete buttons from existing toolbars
3. Disable buttons when they're out of context

The Ribbon runs code when I press buttons. Awesome. There's probably a way to add buttons to existing groups, but I can't figure it out. In my add-in, I have this xml code:

<customui xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab id="dkInvTab" label="Invoices">
             <group id="dkInvGroup" label="Invoices">
                    <button id="dkInvNew"
                        label="Create New Invoice"
                        size="large"
                        onAction="CreateNewInvoice" />
                    <button id="dkInvOpen"
                        label="Open Existing Invoice"
                        size="large"
                        onAction="OpenInvoice" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customui>

It adds a tab, then a group, then two buttons that I always want active. I want to add two more buttons when my invoice template is active. That should be easy enough.

<customui xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab id="dkInvTab">
             <group id="dkInvGroup">
                    <button id="dkinvSave"
                        label="Save Invoice"
                        size="large"
                        onAction="SaveInvoice"  />
                    <button id="dkInvCalcTax"
                        label="Calculate Sales Tax"
                        size="large"
                        onAction="CalcTax"  />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customui>

Nope, that doesn't do it. I open my template and the Ribbon doesn't change. The xml is well formed, per the Custom UI Editor. There may be a syntax error in there that's causing the problem, but I don't see it. And Excel isn't going to tell me where it is.

But I really don't want to add those buttons when the invoice template is active. I want to add them as disabled when the add-in is loaded and enable them when the invoice template is active. I should be able to do that with xml that looks like this:

<customui xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab id="dkInvTab">
             <group id="dkInvGroup">
                    <button id="dkinvSave" enabled="true" />
                    <button id="dkInvCalcTax" enabled="true" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customui>

Download music on line
Download mp3 music
mp3 online
buy music online
buy mp3 music
Download mp3 music online
Download free MP3 music
Download Adobe software
Download Adobe software
Download Adobe software
Download Adobe software
Music downloads
Online MP3 music
MP3 music downloads online
Downloads music mp3
mp3 downloads online
Buy phentermine
buy viagra online buy cialis online buy levitra online buy cialis professional online buy tramadol online
buy xanax no prescription
Nope, that doesn't do it. Apparently I have to use a getEnabled event to change the enabled status. As far as I can tell, that event only runs when the add-in is loaded. WTF good is that? How exactly is that an event? I'm really struggling to figure out what the people who designed this customization scheme were thinking.

Let's assume for a moment that there is a way to enable/disable buttons based on the active workbook. Do I have to use event procedures to somehow refresh the ribbon and check what's active? I already have to do that with the Commandbars object model, so what's the benefit to me, as a developer, to using the Ribbon? None, that I can see.

So I'm sticking with Commandbars for now. It adds them to the Add-ins tab, which I don't like. But it allows me to easily enable and disable buttons, which I do like.

I'm a reasonable guy. I'd like to think that the Ribbon is a work in process and that the next iteration will be better. But I've culled my list of requirements to three things and they didn't get those right. How can I expect them to produce something worthwhile when they can't even get the basics right. I'm soured. Even when someone shows me how to do what I want, I'll still be soured. It's not intuitive and it's hard to debug. I simply don't like it.

Hi everyone,

Already 5 years have passed since I founded my company. And I must say those years passed in the blink of an eye.

I've never enjoyed my work as much as in these past years. Even if at times things were hectic and I worked crazy hours. I'll never start working for a boss again (if I can help it)!

My conclusion after my first half-a-decade: If you think you're good at something (even if it is outside of your current field of work), strongly consider becoming self-employed. It'll take you a year or two to get up and running, but if you're an independent kind of person you'll love the "being in control" feeling it will give you. Very rewarding.

I felt a 5 year anniversary needed more than just posting about it here. My website hadn't been redone in the same amount of time (except maybe for some bells 'n whistles) and I had already thought I might give this new-fangled Microsoft Expression Web (EW) a whirl.

So I downloaded and installed the thing and imported my current Frontpage web into EW. Sheesh, what a huge difference in UI. EW seems much more aimed at the web developer than at the casual let-me-build-me-a-site-for-my-club kind of user.

Luckily I bumped into a course which seemed tailored at what I was about to do:
Migrating from FrontPage to Expression Web
I subscribed, followed the course and here is the result:

www.jkp-ads.com

Kudos to Tina Clarke and Patricia Geary (both Frontpage MVP's) who were the course writers and -instructors. Excellent job.

So, have a look at my new site and let me know what you think.

Regards,

Jan Karel Pieterse
JKP Application Development Services

Without further ado:

<customui xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab idMso="TabHome">
                <group idMso="GroupClipboard" visible="false" />
            </tab>
        </tabs>
    </ribbon>
</customui>

I'm finally working my way through this book:

OK, so here's my initial impressions. Initial, mind you. I do a tiny bit of xml coding and I'm able to disable the cut-copy-paste triumvirate without VBA. In addition, the Ribbon changes based on the active workbook. When my template is active, the Clipboard group disappears. When I activate a different workbook, it comes back. This is good. I can eliminate half of my Windows_Activate and Windows_Deactivate events because the "toolbars" take care of themselves. I'm sure I'm missing something, but this seems like quite an improvement to me.

But it's not all puppies and kittens. Ideally I'd like to just disable the controls rather than hide the whole group. My problem is that I don't know the idMso attribute for the controls. I found this list of control IDs but I haven't downloaded it yet.

As a developer (not a user), my first impression of the Ribbon is a good one. I fully expect it to turn sour and that's where you come in. For instance, I hear that it's nearly impossible to truly disable everything Ribbon. I don't create dictator apps, so I'm not sure how this is going to effect me. Yet, I know it will somehow. If you have a particular gripe about the Ribbon, from a developer's perspective, I'd like to hear it. It will help me prepare for the inevitable.

Hi all

If you create a dictator application in Excel 2007 you maybe want to disable the
shortcuts to the old Excel 2003 menu items in Excel 2007.

I create this page with example code to do this.
Note: There is no test on this moment to see if a tag is already used.
http://www.rondebruin.nl/acceleratorskeys.htm

If you want to have a RibbonX example to disable/hide all Ribbonstuff see
the dictator examples on my Ribbon page (number 2)
http://www.rondebruin.nl/ribbon.htm

To disable other shortcuts see my onkey page
http://www.rondebruin.nl/key.htm

If you have suggestions please let me know

Ron de Bruin
http://www.rondebruin.nl/tips.htm

At some point in the future, we're going to look back at the stock market during this financial crisis. If we take a broad enough view, we'll see something like this:

If you move your long-term common stock investments into money market accounts (basically cash) now, you'll probably be doing it at the worst time possible. You've already taken the losses. Those are in the past and you can't go back and sell a month ago. But just because it sucks to lose all that value, isn't justification for missing out on all the up-side.

Do you think the star should be left of where it is now? That is, do you think we'll see more losses before a rebound? You may be right. I can't predict where the bottom is and I doubt you can too. The people who are selling emotionally now aren't doing so in order to re-buy at the bottom. No, they'll wait until it's way back up.

Do you think the slope to the right of the star is too steep? That is, do you think it will take longer to recover than this graph shows? You're probably right. Yet, it's still better to sell anywhere to the right of the star.

As Warren Buffet says:

Be fearful when others are greedy. Be greedy when others are fearful.

When I test formulas on a spreadsheet, it s a very manual process. I'm trying to figure out a way to automate this. Here's what I have so far.

I want to identify some input cells and output cells. Then record some known good values for the inputs and outputs and store them. I'm using my spreadsheet from my amortization table post as an example. I would select the cells I want to include in the test and run a macro that creates a text file with the cells' values. If a selected cell has a formula, I'll assume it's an output test. Presumably, I would select all input cells on a spreadsheet, but there may be instances where I don't.

This sheet only has three inputs, so I select D1:D3 for sure. I may or may not want to select every output cell. Maybe I only test a sample of the output. For this example, let's assume I select D4, E7, E27, E107, and E367. Then I run this macro:

Sub CreateTest()
   
    Dim rCell As Range
    Dim sFname As String
    Dim lFnum As Long
    Dim sInput As String
    Dim sOutput As String
   
    If TypeName(Selection) = "Range" Then
        'Pick a name for the test file
        sFname = Application.InputBox("Enter test file name to create.", "File Name")
       
        If sFname <> "False" Then 'if inputbox not canceled
           
            'Add file extension if not there
            If Right$(sFname, 4) <> ".txt" Then
                sFname = sFname & ".txt"
            End If
           
            sInput = "[Input]" & vbNewLine
            sOutput = "[Output]" & vbNewLine
           
            'Build input and output strings based on selection
            For Each rCell In Selection.Cells
                If rCell.HasFormula Then
                    sOutput = sOutput & ActiveSheet.Name & "|" & _
                        rCell.Address & "|" & rCell.Value2 & vbNewLine
                Else
                    sInput = sInput & ActiveSheet.Name & "|" & _
                        rCell.Address & "|" & rCell.Value2 & vbNewLine
                End If
            Next rCell
           
            sOutput = Left$(sOutput, Len(sOutput) - 2) 'delete new line
           
            'Create the test file
            lFnum = FreeFile
            Open sFname For Output As lFnum
           
            Print #lFnum, sInput
            Print #lFnum, sOutput
           
            Close lFnum
           
        End If
    Else
        MsgBox "Please select one or more cells and try again"
    End If
   
End Sub

And I get this in my text file:

I haven't written the procedure that actually runs the test, but I think it will be pretty trivial (famous last words). I thought I would get some opinions on the methodology first. Is anyone automating testing of formulas? What do you think of this method?

When rolling your own csv files, you have to account for commas in the text. Extra commas will create extra columns and you don't want that. Let's start with this data:

Two of the names have commas and two don't. By ignoring those commas, extra columns are created because a comma delimits a column. In this code, no account is made for commas in the text.

Sub MakeCSV1()
   
    Dim rCell As Range
    Dim rRow As Range
    Dim lFnum As Long
    Dim sFname As String
    Dim sText As String
   
    lFnum = FreeFile
    sFname = "C:\MyCSV1.csv"
   
    Open sFname For Output As lFnum
   
    For Each rRow In Sheet1.UsedRange.Rows
        For Each rCell In rRow.Cells
            sText = sText & rCell.Text & ","
        Next rCell
        sText = Left$(sText, Len(sText) - 1) 'remove comma
        sText = sText & vbNewLine 'add line break
    Next rRow
   
    Print #lFnum, sText
   
    Close lFnum
   
End Sub

When you open MyCSV1.csv in Excel, you get this:

To keep that name field as one, test for the existence of a comma and enclose the field text in double quotes (Chr$(34) is used here).

Sub MakeCSV2()
   
    Dim rCell As Range
    Dim rRow As Range
    Dim lFnum As Long
    Dim sFname As String
    Dim sText As String
   
    lFnum = FreeFile 'get availabe file number
    sFname = "C:\MyCSV2.csv"
   
    Open sFname For Output As lFnum
   
    For Each rRow In Sheet1.UsedRange.Rows
        For Each rCell In rRow.Cells
            If InStr(1, sText, ",")> 0 Then
                sText = sText & Chr$(34) & rCell.Text & Chr$(34) & ","
            Else
                sText = sText & rCell.Text & ","
            End If
        Next rCell
        sText = Left$(sText, Len(sText) - 1) 'remove comma
        sText = sText & vbNewLine 'add line break
    Next rRow
   
    Print #lFnum, sText 'write to csv file
   
    Close lFnum
   
End Sub

That keeps everything nice and neat.

When Excel is in the news, it's never a good thing.

via Above The Law

According to the various affidavits (posted below) the Cleary first-year did not notice that the 179 contracts were marked as "hidden" in Excel, and certainly didn't notice that those entries became "un-hidden" when he globally reformatted the document.

If you're buying any troubled assets in the near future, be sure to use the auditing feature in any spreadsheets you get. Show the Formula Auditing toolbar and click the Trace Dependents button. The difference between a reference to a visible and hidden cell is shown below.

I ran into a little problem with Excel 2007 this week. When I have an Excel 2003 add-in loaded in 2007, it exhibits some strange behavior when copying sheets. Namely, in certain circumstances the CodeName property of the sheet gets changed. I'm not sure what all those circumstances are, but I do know one of them.

If the VBE is visible, the CodeName is preserved. If not, the CodeName is changed to Sheet1 (or whatever it would be if a new sheet was added).

I rely on the CodeName quite a bit in my code. For instance I only allow the user to do certain things when a certain sheet is active, usually triggered by an application-level event.

For the time being, I'm using the sheet name or the value in a particular cell to identify the sheet. But as Jon Peltier pointed out, that's dangerous. So I'm going to rewrite the code to use a hidden range name.

First, I create a sheet-level range name with a Value of TRUE. The value isn't really important as the mere existence of the name identifies the sheet, but I use the TRUE value to simplify the function that comes later.

To hide the name, I execute this VBA statement in the Immediate Window (via the SpreadsheetPage)

Sheet1.Names("IsInvoice").Visible = False

Then I use that name in these functions:

Public Function IsInvoice(sh As Worksheet) As Boolean
   
    On Error Resume Next
        IsInvoice = Evaluate(sh.Names("IsInvoice").Value)
       
End Function
 
Public Function GetInvoiceSheet(wb As Workbook) As Worksheet
   
    Dim ws As Worksheet
   
    For Each ws In wb.Worksheets
        On Error Resume Next
            If IsInvoice(ws) Then
                Set GetInvoiceSheet = ws
                Exit Function
            End If
        On Error GoTo 0
    Next ws
   
End Function

I recieved this email from my pal Simon:

"One of the problems in many of the spreadsheets and potential spreadsheets I come across is the need to automatically adjust to changing areas of external data. It seems at first glance that, to refer to a single column in a block of external data, the intersection operator would be useful e.g. =SUMIF(A:A NorthwindInv,A2,C:C NorthwindInv) where ‘NorthwindInv’ is the external data range name. I haven’t seen this much used in practice or indeed written about. Is there some good reason why it’s a bad idea?"

Hmm, the thing is, that style of referring to a 'Range within a Range' is new to me. Anyone have any thoughts to share?

It's Wednesday night. That means I'm drinking Tangueray and reviewing football games (for entertainment purposes only). I like to rate games on a three-star system. Three stars means refinance the house and fly to Vegas. Two stars means I really like a team. And one star means I'd have as much luck flipping a coin. Zero stars means Purdue is playing (why Purdue wins when they should lose and loses when they win is beyond me).

Instead of putting a 1, 2, or 3 next to the team, I want actual stars.

To get that look: left align, set font to Wingdings, and use this custom number format:

[<2]"«";[<3]"««";"«««";

And to get those double chevrons (or whatever they are), hold down the Alt key and type 0171 on the numeric keypad. Now type a 1, 2, or 3 in the cell and your in like Roger Ebert.

P.S. I can never remember how to do that conditional number format thing, so I always go to PeltierTech.com to re-learn it.

I'm porting an application to Excel 2007 and thought I'd share some problems I've run into. First, there's FileSearch. I use FileSearch to read CustomDocumentProperties from closed Excel files. That way I can determine if the file is a part of my app and relevant to the context before I open it up. FileSearch was discontinued in Excel 2007. I've heard it's because there was some bugs that would cost more to fix than it was worth. Now I simply open the file to read the CDPs. It's not as efficient if the user opens the wrong file, but for a correct file, it's faster. Instead of reading the properties then opening the file, I just open the file. Reading properties from an already opened file has to be quicker that FileSearch.

Once I eliminated the reference to FileSearch, I could actually open my add-ins and test them. In one procedure I insert a Picture object onto a sheet with code similar to this:

sh.Range("E43").Select
Set pic = sh.Pictures.Insert(sPICFILENAME)

I'm very vocal about my objections to selecting ranges. It's almost never necessary. So why do I do it here? Good question. In Excel 2003, the Insert method will place the picture at the ActiveCell. In 2007, it seems to place the picture in the same place on the sheet regardless of the ActiveCell. So instead of Selecting, I change the code to something like:

Set pic = sh.Pictures.Insert(sPICFILENAME)
pic.Top = sh.Range("E43").Top
pic.Left = sh.Range("E43").Left

That also works in 2003, so there was really no need to select anything.

The last problem I encountered was with the UsedRange property. We all know how Excel can lose track of where the last cell is and give you a UsedRange bigger than actual. I have one sheet where I loop through all the cells. I never add or delete rows or columns and the UsedRange is never wrong. Even if it was wrong by a few rows or columns, it wouldn't impact performance. So I iterated through the cells in UsedRange.

When I opened the template in Excel 2007 the UsedRange was huge. The last cell was something like CW1024 when it should have been G40. If I open it in 2003, no problem. I stopped using UsedRange and rolled my own.

Public Function GetUsedRange(sh As Worksheet) As Range
   
    Dim lLastRow As Long
    Dim lLastCol As Long
   
    lLastRow = 1: lLastCol = 1 'in case the sheet is empty
   
    'Finding "*" will find anything.  I look backward from the first
    'cell to get the last cell with anything in it
    On Error Resume Next
        lLastRow = sh.Cells.Find("*", sh.Cells(1), , , xlByRows, xlPrevious).Row
        lLastCol = sh.Cells.Find("*", sh.Cells(1), , , xlByColumns, xlPrevious).Column
    On Error GoTo 0
   
    Set GetUsedRange = sh.Cells(1).Resize(lLastRow, lLastCol)
   
End Function

This doesn't give the same results as UsedRange. This will always start at A1, whereas UsedRange will show the smallest rectangle that includes all non-empty cells and that may start somewhere other than A1.

Sorry no Excel posts lately. I thought I'd give you a quick update on what's happening.

The Texas trip was canceled due to Hurricane Ike. If I had flown Frontier I could change my ticket for nothing. Instead, American Airlines is going to charge me $175. I honestly don't know how these people sleep at night.

Stackoverflow opened its public beta last week. I've been following the Excel and VBA tags on RSS. I'm not sold on the site, but I want to try it for a while. I'm an NNTP guy and never really found web forums to be all that productive. And now that I barely post on NNTP, I don't know what kind of guy I am. Just another lazy blogger I guess.

The Excel User Conference started today. I'll be looking forward to reading some blog posts about that. Speaking of training, don't forget about Excel Dashboard and Visualization Bootcamp. There's about a 75% chance that I'll be there and I'll know for sure by next week. So if you go, be sure to say "Hi".

Here's a tip: Get the same laptop at work that you have at home. I liked my D810 so much that when I started a new job I got a virtually identical machine. Now that my personal D810 is at Dell getting fixed, I can use my work laptop by just swapping hard drives. Oh, it's not convenient. But it's better than being without or even trying to get by on an old desktop I have laying around. To the extent that I'm able to, I think I'll always use the same computer at work and home.

I'm working on some VBA to take some of the drudgery out of payroll.

Public Function ThisFriday() As Date
   
    ThisFriday = Date + 8 - Weekday(Date, vbFriday)
   
End Function
 
Public Function LastFriday() As Date
   
    LastFriday = Date + 1 - Weekday(Date, vbFriday)
   
End Function

I thought I needed to know this Friday (pay check day), but I actually need last Friday (end of pay period).