Formulas w/ Custom Functions work fine but then turn into #Value error

Question

I have a colleague who is on Excel 2003. I made some custom functions for him, in an .xla add-in. He was having too much trouble around the path issues of custom functions in add-ins, so I moved the custom functions to a workbook, and he makes copies of that workbook as needed.

When I sent it to him, and since then whenever I send him an updated version, when he opens the workbook he finds that all the formulas using the custom functions are returning #Value errors. Each time, the same workbook had worked fine for me, also in Excel 2003 (I also have later versions of Excel but I use 2003 for my work with him).

Then, if he recalcs nothing happens. I've had him try doing a search & replace on the equals sign, which normally forces the affected formulas to recalculate, but it had no effect. The only way we were able to get the formulas to start working again was to manually activate the cell (F2, or click in the formula bar) and then press Enter. I tried a VBA loop to use SendKeys to send the F2 and then the enter, and it worked on some of the cells but not all.

After working with him on it for a while, and getting to a point where some of the formulas were working again while some were still returning #Value, I had him save the workbook and send it to me. If I was getting the same behavior I'd expect to see #Value in all of the formulas. But instead I saw that the formulas that were working fine for him were working fine for me, and the formulas that were returning #Value for him were doing the same for me. So, whatever is making them change from working fine to returning #Value when he opens a file I sent, does not seem to be happening for me, in the same version of Excel.

Any idea why this would be happening? Any troubleshooting suggestions?

UPDATE:

I've been asked to provide an example affected custom function. Here's one, "GetCompanyName":

Option Explicit

Public Const APP_NAME As String = "Download Financials"

'TABLE NAME:
Public Const TABLE_INCOME_STATEMENT_ANNUAL As String = "_TABLE_INCOME_STATEMENT_ANNUAL_"
Public Const TABLE_BALANCE_SHEET_ANNUAL As String = "_TABLE_BALANCE_SHEET_ANNUAL_"

'LINE ITEM NAME:
Public Const ROW_TOTAL_REVENUE As String = "Total Revenue"
Public Const ROW_GROSS_PROFIT As String = "Gross Profit"
Public Const ROW_OPERATING_INCOME As String = "Operating Income"

'SPECIAL:
Public Const EXCHANGE_SYMBOL As String = "_EXCHANGE_SYMBOL_"
Public Const COMPANY_NAME As String = "_COMPANY_NAME_"
Public Const COMPANY_DESC_SHORT As String = "_COMPANY_DESCRIPTION_SHORT_"
    Public Const DESC_SHORT_NA As String = "Short Company Description is not available."
Public Const COMPANY_DESC_LONG As String = "_COMPANY_DESCRIPTION_LONG_"
    Public Const DESC_LONG_NA As String = "Long Company Description is not available."


Public Const DATE_ADD_INTERVAL_MINUTE As String = "n" 'm is month.
Public Const DATE_ADD_INTERVAL_SECOND As String = "s"

Public Const WEB_OUTPUT_FILE_NAME As String = "Output"

Public Const COLOR_SOFT_YELLOW As Long = 10092543
Public Const COLOR_DARK_RED As Long = 128

Public Const DOWNLOAD_STATUS_SUCCESS As String = "Success"
Public Const DOWNLOAD_STATUS_FAILURE As String = "Failure"


'shDownloadIndex (worksheet's codename):
Public Const COL_DOWNLOADINDEX_URL As Long = 1
Public Const COL_DOWNLOADINDEX_SHEETNAME As Long = 2
Public Const COL_DOWNLOADINDEX_TIME As Long = 3
Public iRowDownloadIndex As Long
Public rgDownloadIndexCell As Range, strDownloadIndexURL As String

Public wbCurrent As Workbook, shCurrent As Worksheet
Public shDownloadIndex As Worksheet

Public wbWeb As Workbook, shWeb As Worksheet
Public rgColA As Range
Public rgFind As Range
Public rgHeaderIncomeStatement As Range, rgRowHeaderIncomeStatement As Range
Public rgHeaderBalanceSheet As Range, rgRowHeaderBalanceSheet As Range

Public rgExchangeSymbol As Range, rgCompanyName As Range
Public rgDesc As Range, strDesc As String
Public iPosStart As Long, iPosEnd As Long, iPosTmp As Long, iPosCounter As Long, iLen As Long

Public rgTmpRow As Range, shTmpHeader As Range
Public iTmpRow As Long

Public dateLastCall As Date
Public bDownloadInProgress As Boolean

Public bCancelDownload As Boolean    

Public Const COL_DOWNLOADINDEX_URL As Long = 1
    Public Const COL_DOWNLOADINDEX_SHEETNAME As Long = 2
    Public Const COMPANY_NAME As String = "_COMPANY_NAME_"

    Public Function GetCompanyName(full_url As String) As Variant

        Set wbCurrent = ActiveWorkbook: Set shCurrent = ActiveSheet
        Set shDownloadIndex = SetDownloadIndexSheet(shDownloadIndex, wbCurrent, shCurrent)

        Set rgDownloadIndexCell = shDownloadIndex.Columns(COL_DOWNLOADINDEX_URL).Find(What:=full_url, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)
        strDownloadIndexURL = shDownloadIndex.Cells(rgDownloadIndexCell.Row, COL_DOWNLOADINDEX_SHEETNAME).Value
        Set shWeb = wbCurrent.Worksheets(strDownloadIndexURL)
        Set rgColA = shWeb.Columns(1).EntireColumn
        Set rgFind = rgColA.Find(COMPANY_NAME, rgColA.Cells(1), xlValues, xlWhole, , xlNext, False)
        Set rgCompanyName = rgFind.End(xlDown)

        GetCompanyName = rgCompanyName.Value

    End Function


    Public Function SetDownloadIndexSheet(index_sheet As Worksheet, work_book As Workbook, after_sheet As Worksheet) As Worksheet
        Dim shTmp As Worksheet, shDownloadIndex As Worksheet
        Dim shActiveSheet As Worksheet
        Dim bIndexSheetIsBad As Boolean

        On Error GoTo CheckingIndexSheet

        bIndexSheetIsBad = False 'init.
        If Not index_sheet Is Nothing Then
            If Not bIndexSheetIsBad Then
                If index_sheet.Name = "DownloadIndex" Then
                    If Not bIndexSheetIsBad Then
                        Set SetDownloadIndexSheet = index_sheet
                        Exit Function
                    End If
                End If
            End If
        End If

        On Error GoTo GenErr

        For Each shTmp In work_book.Worksheets
            If shTmp.Name = "DownloadIndex" Then
                Set shDownloadIndex = shTmp
                Exit For
            End If
        Next shTmp
        If shDownloadIndex Is Nothing Then
            Set shActiveSheet = ActiveSheet
            Set shDownloadIndex = work_book.Worksheets.Add(after:=after_sheet)
            shDownloadIndex.Name = "DownloadIndex"
            shDownloadIndex.Visible = xlSheetVisible
            shActiveSheet.Activate
        End If
        Set SetDownloadIndexSheet = shDownloadIndex

        Exit Function
    CheckingIndexSheet:
        If False Then
            Resume
        End If
        bIndexSheetIsBad = True
        Resume Next

    GenErr:
        If False Then
            Resume
        End If

    End Function

UPDATE 2:

Here at work I don't have Excel 2003, instead the employer has only provided me with Excel 2007. I see that the same workbook from him opens fine in Excel 2007; all the functions work correctly. Last night at home on the phone with him it was late and we were pressed for time and I didn't test 2007, 2010, and 2013.

However, he really hates the Ribbon so he's still on Excel 2003. Any ideas on what he can do in Excel 2003 to get around the problem?

UPDATE 3:

Updated the code to show Ron that all variables are declared.

Answer 1

Since I don't have 2003 (only 2007) I hope this could work for you.

What you could do is use "Application.Volatile" in the top of each function that is used in the formula bar. This marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change.

public Function xx () as variant
  Application.Volatile(True)
  '
  'Your code
  '  
end function