Replies

That sounds like a bear. Do any of your links skip levels (i.e. do Division files ever link to Branch files, or do Divisions only link to Regions.) I’m hoping the links don’t skip levels. Here are a few suggestions that might help:

1. You could open all of the Region and Branch files and do “Save As” on the branch files to the new directory. This will cause all links to Branch files to change. You can then delete the Branch files from their original location.
2. You could use search and replace to change the links. Move all of the Branch files to their new directories. Then open the Region files and replace c:olddirectorybranch1.xls with c:newdirectorybranch1.xls. For each Branch name, do a Replace instead of a Replace All for the first instance. If you messed up the new file name, Excel says the new file can’t be found and asks for a new name. If you do a Replace, you only need to fix your error once. If you do Replace All, you’ll have to do this for every instance where Excel tries to replace the old file with the new messed up file name.

Make sure that “SupplierList” refers to a 1 dimensional range of cells. Match doesn’t work if your range is 2 dimensional.

Make sure that “SupplierList” refers to a 1 dimensional range of cells. Match doesn’t work if your range is 2 dimensional.

Here’s my stab at something. You add new donations to the bottom of the Donations sheet. You type into the yellow cells to set your report parameters.

There are some range names and data validations and there’s code in the Donations worksheet:

Option Explicit
Private Sub Worksheet_Activate()
    Dim Col As Integer
    On Error GoTo TurnOnFilter
    Col = ActiveSheet.AutoFilter.Range.Column
    On Error GoTo 0
    Exit Sub
TurnOnFilter:
    If Err.Number = 91 Then
        Range("Dates").AutoFilter
        FilterDates
        FilterIDs
    End If
    Resume
End Sub

Private Sub Worksheet_Change(ByVal ChangedCell As Range)
    If Not Intersect(ChangedCell, Range("Quarter")) Is Nothing Or _
       Not Intersect(ChangedCell, Range("Year")) Is Nothing Then
        FilterDates
    Else
        If Not Intersect(ChangedCell, Range("ID")) Is Nothing Then
            FilterIDs
        End If
    End If
End Sub

Private Sub FilterDates()
    With Range("Dates")
        .AutoFilter Field:=.Column - .Parent.AutoFilter.Range.Column + 1, _
        Criteria1:=">=" + Format(Range("StartDate").Value, "m/d/yyyy"), _
        Operator:=xlAnd, _
        Criteria2:="<=" + Format(Range("EndDate").Value, "m/d/yyyy")
    End With
End Sub

Private Sub FilterIDs()
    With Range("IDs")
        If IsEmpty(Range("ID")) Or Not IsNumeric(Range("ID")) Or Range("ID") = 0 Then
            .AutoFilter Field:=.Column - .Parent.AutoFilter.Range.Column + 1
        Else
            .AutoFilter Field:=.Column - .Parent.AutoFilter.Range.Column + 1, _
            Criteria1:="=" + Format(Range("ID").Value, "#")
        End If
    End With
End Sub

It would be a fairly simple matter to write a macro that would cycle through the Donor list and print a page for each one. If you need help with that piece, just post back here.

Here’s my stab at something. You add new donations to the bottom of the Donations sheet. You type into the yellow cells to set your report parameters.

There are some range names and data validations and there’s code in the Donations worksheet:

Option Explicit
Private Sub Worksheet_Activate()
    Dim Col As Integer
    On Error GoTo TurnOnFilter
    Col = ActiveSheet.AutoFilter.Range.Column
    On Error GoTo 0
    Exit Sub
TurnOnFilter:
    If Err.Number = 91 Then
        Range("Dates").AutoFilter
        FilterDates
        FilterIDs
    End If
    Resume
End Sub

Private Sub Worksheet_Change(ByVal ChangedCell As Range)
    If Not Intersect(ChangedCell, Range("Quarter")) Is Nothing Or _
       Not Intersect(ChangedCell, Range("Year")) Is Nothing Then
        FilterDates
    Else
        If Not Intersect(ChangedCell, Range("ID")) Is Nothing Then
            FilterIDs
        End If
    End If
End Sub

Private Sub FilterDates()
    With Range("Dates")
        .AutoFilter Field:=.Column - .Parent.AutoFilter.Range.Column + 1, _
        Criteria1:=">=" + Format(Range("StartDate").Value, "m/d/yyyy"), _
        Operator:=xlAnd, _
        Criteria2:="<=" + Format(Range("EndDate").Value, "m/d/yyyy")
    End With
End Sub

Private Sub FilterIDs()
    With Range("IDs")
        If IsEmpty(Range("ID")) Or Not IsNumeric(Range("ID")) Or Range("ID") = 0 Then
            .AutoFilter Field:=.Column - .Parent.AutoFilter.Range.Column + 1
        Else
            .AutoFilter Field:=.Column - .Parent.AutoFilter.Range.Column + 1, _
            Criteria1:="=" + Format(Range("ID").Value, "#")
        End If
    End With
End Sub

It would be a fairly simple matter to write a macro that would cycle through the Donor list and print a page for each one. If you need help with that piece, just post back here.

Legare,
Thanks for your thoughts on this. It looks like your algorithm will always pass through the data exactly .5*N^2 times ((N-1)+(N-2)+…+1). If I recall from my old comp sci classes, I think I will average N/2 passes through the data to sort my list. Since each pass is N-1 loops, I think I will have an average of .5*(N^2-N), but I could be wrong.

Legare,
Thanks for your thoughts on this. It looks like your algorithm will always pass through the data exactly .5*N^2 times ((N-1)+(N-2)+…+1). If I recall from my old comp sci classes, I think I will average N/2 passes through the data to sort my list. Since each pass is N-1 loops, I think I will have an average of .5*(N^2-N), but I could be wrong.

I found out that some prior references to the UDF were not removed from the workbook. Once I cleaned all of those up, the add-in worked perfectly. Thanks everyone for your help.

I found out that some prior references to the UDF were not removed from the workbook. Once I cleaned all of those up, the add-in worked perfectly. Thanks everyone for your help.

Legare,
Your comments made me think back to my university days and programming “bubble sort” routines in FORTRAN. Back then, it was considered an efficient way to sort so I tried implementing it in VBA and my testing indicates that it is indeed faster. The code is a little less readable than using the RANK function, but it seems to run considerably faster in most cases. I was also having trouble with the RANK function returning a zero value in some cases (perhaps due to inadequate numeric precision) so this solution avoids that.

Function CTE(Level As Double, Values As Object, Optional Max0 As Boolean = False, _
 Optional Probabilities As Variant, Optional Smallest As Boolean = True)
' Computes Conditional Tail Expectation from the specified percentage (i.e. 1-Level) of Values
'
' If Max0=TRUE, any Values greater than 0 will be set to 0
' If Smallest=TRUE, it will compute the average of the smallest Values
' If SmallestTRUE, it will compute the average of the largest Values
'
' DC 9/23/2003
'  *7/15/2004 Modified to handle duplicate values. Prior version did not do this properly.
'    Also modified to require explicit declaration of variable types. Also modified to
'    normalize Probabilities so they sum to 1.00
'  *7/20/2004 Modified to improve efficiency
'
    CTE = CVErr(xlErrValue)
    If Level >= 1 Or Level < 0 Then Exit Function
    
    Dim SortedValues() As Double, SortedProbs() As Double, SumProbs As Double, Temp As Double
    Dim TotalProb As Double, TotalValue As Double, ProbLimit As Double
    Dim i As Long, j As Long, N As Long
    Dim SortFinished As Boolean, UniqueProbs As Boolean
    Dim wfunc As Object
    Set wfunc = Application.WorksheetFunction
    
    N = Values.Count
    ReDim SortedValues(1 To N), SortedProbs(1 To N)
    
    UniqueProbs = IsArray(Probabilities)
    SumProbs = 0
    For i = 1 To N
        If Max0 Then
            SortedValues(i) = wfunc.Min(0, Values(i))
        Else
            SortedValues(i) = Values(i)
        End If
        If UniqueProbs Then
            SortedProbs(i) = Probabilities(i)
        Else
            SortedProbs(i) = 1 / N
        End If
        SumProbs = SumProbs + SortedProbs(i)
    Next i
    
    SortFinished = False
    Do While Not (SortFinished)
        SortFinished = True
        For i = 1 To N - 1
            If SortedValues(i) < SortedValues(i + 1) Then
                SortFinished = False
                Temp = SortedValues(i)
                SortedValues(i) = SortedValues(i + 1)
                SortedValues(i + 1) = Temp
                If UniqueProbs Then
                    Temp = SortedProbs(i)
                    SortedProbs(i) = SortedProbs(i + 1)
                    SortedProbs(i + 1) = Temp
                End If
            End If
        Next i
    Loop
    
    If Smallest Then
        i = N + 1: j = -1
    Else
        i = 0: j = 1
    End If
    TotalValue = 0: TotalProb = 0: ProbLimit = 1 - Level
    Do While TotalProb < ProbLimit
        i = i + j
        TotalValue = TotalValue + SortedProbs(i) / SumProbs * SortedValues(i)
        TotalProb = TotalProb + SortedProbs(i) / SumProbs
    Loop
    CTE = (TotalValue - (TotalProb - ProbLimit) * SortedValues(i)) / ProbLimit
End Function

I’m not sure you really need a routine. You can put the =rand() function into cells A1:A321. Then in cells B1, put in =RANK(A1,$A$1:$A:$321). Then copy B1 down to B321. Column B will then have the integers 1-321 in random order. Just hit F9 to generate a new random list.

I’m not sure you really need a routine. You can put the =rand() function into cells A1:A321. Then in cells B1, put in =RANK(A1,$A$1:$A:$321). Then copy B1 down to B321. Column B will then have the integers 1-321 in random order. Just hit F9 to generate a new random list.

Legare,
Thanks for the guidance on the Dim statement. I’m sure I’ve repeated this mistake many times so thanks for setting me straight.

The Application.Rank statement calls the Rank worksheet function which gives me the ranking of each Values(i) within the Values array. This is how I’m sorting my array.

Upon reviewing the Excel97 help, it says, “In previous versions of Microsoft Excel, worksheet functions were contained by the Application object.” I guess the proper syntax is now application.worksheetfunction.rank

Legare,
Thanks for the guidance on the Dim statement. I’m sure I’ve repeated this mistake many times so thanks for setting me straight.

The Application.Rank statement calls the Rank worksheet function which gives me the ranking of each Values(i) within the Values array. This is how I’m sorting my array.

Upon reviewing the Excel97 help, it says, “In previous versions of Microsoft Excel, worksheet functions were contained by the Application object.” I guess the proper syntax is now application.worksheetfunction.rank

The add-in is installed as you described via ToolsAdd-ins and checking the box. If I change the function name in the workbook and the add-in, Excel executes the function correctly.

I’ve always noticed that with UDFs, the case of the UDF name (i.e. which letters are capitalized in the name) is determined by your first usage of the UDF in your workbook. This makes me believe that Excel is building a table of UDF names.

In my current situation, the function name continues to revert to all lower case because that’s how it was originally used in the workbook, even though I’ve deleted the UDF from the workbook, opened the workbook, deleted references to the function, removed the add-in, saved the workbook, and re-installed the add-in all in an attempt to delete this entry from this presumed UDF name table.