1

I wrote a VBA function that checks several things and returns a code that triggers conditional formatting. Everything works well except that the formula often triggers a #value error. It does this for all cells containing the formula (a few thousands). The error appears whenever I open another workbook. It sometimes happen when I don't open another workbook.

The function is here:

  Function jjcheck(STDTRow As Integer, cuCOL As Integer, cuMax As Integer, trmEnd As Integer, trmEMax As Integer, worksheetSRC As String, lstCTCT As Date) As Variant
   'use in spreadsheet =jjcheck(B2,Variables!$G$4,Variables!$G$2,Variables!$F$2,"SRM",U2)
   '=jjcheck(B2,Variables!$G$4,Variables!$F$4,Variables!$G$2,Variables!$F$2,"SRM",IF(ISBLANK(U2),TODAY(),U2))
   Dim V() As String, dayMax As Integer, lookup As Date, theDiff As Integer, lstContact As String
   V = Split(ActiveWorkbook.ActiveSheet.Cells(1, 2).Value, "-"): dayMax = V(1): theDiff = 256
   lookup = lstCTCT
   theDiff = DateDiff("d", lookup, Date): lstContact = ""
   If theDiff > dayMax Then lstContact = "Alert"
   Dim STDcu As Integer, STtrmEnd As Date, daysTOtrmend As Integer
   STDcu = ActiveWorkbook.Worksheets(worksheetSRC).Cells(STDTRow, cuCOL).Value
   STtrmEnd = ActiveWorkbook.Worksheets(worksheetSRC).Cells(STDTRow, trmEnd).Value
   daysTOtrmend = DateDiff("d", Date, STtrmEnd)
   If STDcu < cuMax And daysTOtrmend < trmEMax Then
       jjcheck = "CHECK" & lstContact
   ElseIf daysTOtrmend < trmEMax / 2 Then
       jjcheck = "ETerm" & lstContact
   Else
       jjcheck = "" & lstContact
   End If
End Function

suspecting that the error might be due to the times cell U2 is empty,I changed what I put for lstCTCT to IF(ISBLANK(U2),TODAY(),U2) That did not seem to help.The image shows what happens in the worksheet Thanks for any insight you can give on that.

litelite
  • 2,857
  • 4
  • 23
  • 33
TMZ
  • 43
  • 3
  • 1
    You should consider using `ThisWorkbook` rather than `ActiveWorkbook` or even better, give your worksheets names (in the VBA editor) and refer directly to them - `wksHome.Range(...)`. The problem with `ActiveWorkbook` is that it changes depending on which workbook you have last interacted with. – CallumDA May 12 '16 at 14:40

1 Answers1

0

Let me know if this makes any difference? The main change is from ActiveWorkbook to ThisWorkbook. (a few others just to tidy up)

Function jjcheck(STDTRow As Integer, cuCOL As Integer, cuMax As Integer, trmEnd     As Integer, trmEMax As Integer, worksheetSRC As String, lstCTCT As Date) as Variant

'use in spreadsheet   =jjcheck(B2,Variables!$G$4,Variables!$G$2,Variables!$F$2,"SRM",U2)
'=jjcheck(B2,Variables!$G$4,Variables!$F$4,Variables!$G$2,Variables!$F$2,"SRM",IF(ISBLANK(U2),TODAY(),U2))

Dim V() As String, lstContact As String
Dim dayMax As Integer, theDiff As Integer, STDcu As Integer, daysTOtrmend As Integer
Dim lookup As Date, STtrmEnd As Date

STDcu = ThisWorkbook.Worksheets(worksheetSRC).Cells(STDTRow, cuCOL).Value
STtrmEnd = ThisWorkbook.Worksheets(worksheetSRC).Cells(STDTRow, trmEnd).Value
daysTOtrmend = DateDiff("d", Date, STtrmEnd)
V = Split(ThisWorkbook.ActiveSheet.Cells(1, 2).Value, "-"): dayMax = V(1): theDiff = 256
lookup = lstCTCT
theDiff = DateDiff("d", lookup, Date): lstContact = vbNulltring

If theDiff > dayMax Then lstContact = "Alert"

If STDcu < cuMax And daysTOtrmend < trmEMax Then
    jjcheck = "CHECK" & lstContact
ElseIf daysTOtrmend < trmEMax / 2 Then
    jjcheck = "ETerm" & lstContact
Else
    jjcheck = vbNullString & lstContact
End If
End Function
CallumDA
  • 12,025
  • 6
  • 30
  • 52
  • Thanks so much. That seems to do it. Good explanation too. Thanks. – TMZ May 12 '16 at 16:34
  • No problem @TMZ if you are happy that this answered your question please select the tick next to this answer to mark it as accepted. – CallumDA May 13 '16 at 08:09
  • Thanks I did not know I could that. I tried to vote but could not since I am a new member. Anyway, the answer worked wonderfully for the most part. The error does not appear anymore when I open new spreadsheets. However, it still sometimes appear (less frequently). I am suspecting it is appearing whenever some "unrelated" macros in the same sheet are run. I am suspecting the other macros have the same issue than that function, but find it curious that the error appears in this "unrelated" function. – TMZ May 14 '16 at 13:30
  • Not problem - my suggestion would be to not use ActiveSheet if possible. This means that when your function is updating in the background but you have a different worksheet active the references will be wrong. – CallumDA May 16 '16 at 08:11