Need UDF to work from personal.xls NOT add-in for new Excel files

[9tanstaafl9]

I have a Crystal Report that generates a poorly formated Excel worksheet every day. I have created a macro that the users can install onto their personal.xls so that once this worksheet is generated, they can simply open it and click CTRL-Shift-F will make it all look nice for them and have proper totals etc.

My problem is that I need a UDF to total only the items that are bold in parts of the worksheet.

I have created this UDF and it works fine when in my test workbook, but not when only in the personal.xls. I am new to UDFS and don't know if this is because I am lacking in knowledge, or if it just isn't possible.


My UDF is:

Function SumIfBold(MyRange As Range) As Double

    Dim cell As Range
    For Each cell In MyRange
        If cell.Font.Bold = True Then
            SumIfBold = SumIfBold + cell
        End If
    Next cell

End Function

Also, please reply in dummy-English. This is my first UDF and I don't really get them. I borrowed this from somewhere else, but it does the trick.

Thank you!

 

[9tanstaafl9]

Me again. Sorry I didn't specify the Excel version. The worksheet is exported automatically to Excel 2003, but I can have the users save it into 2010 before running the macro if needed.

 

[shg]

There are several ways to do this. One way for instant gratification is to enter the function as

=personal.xls!SumIfBold(Whatever that range is)

Or, according to the individual user,

=personal.xlsb!SumIfBold(Whatever that range is)

 

[9tanstaafl9]

Sorry for being dense, but why is one xlsb and one xls?

 

[9tanstaafl9]

thank you for your reply! Unfortunately it didn't work. I tried it in both 2003 and 2010. When I type the formula, select the range, and hit enter for this: =personal.xls!SumIfBold(F10:F42) - it brings up the Update Values personal.xls window. I tried just making up a name but then it tells me file not found. Was I supposed to locate the personal.xls file and update it? It has already been saved.

What appears to happen on the spreadsheet itself is that the correct answer appears in the cell, but the aforementioned window opens up, and as soon as I exit that window I get a #Name? error in that same cell.

 

[9tanstaafl9]

THANK YOU! It did work. I must be an "individual user" because I needed to put the B at the end of personal.xls before it would work. I don't know why, but at this point I don't care. (Though if you wanted to tell me that would be great, because I do care, but I don't NEED to know.)

 

[shg]

"Personal" in versions of Excel through 2003 is an xls file. In 2007, several new file formats were introduced, and the default format for Personal became one of them (xlsb). An "individual user" of Excel 2007/2010/2013 could have either. I have an xls file because it's the same one I use for Excel 2003, which I still use occasionally.