1

I have written some VBA functions (listed in code below) I am comparing records from two worksheets using functions to return the related values from one sheet to the other.

The first function, upon which all other functions depend on, returns the Patient ID number.

Criteria to select a Patient ID:

The function compares date and time of patient arrival within a 30 minute interval (since the information recieved from one source usually varies by a few minutes from the other), gender, clinic ID, and birthyear. Patient ID numbers start at around 50000, and go on until around 150000. I need to compare date and time, because from time to time two patients with the same gender, birthdate and clinic arrived on the same day.

The function fails after 100000's rows Beyond this only #VALUE! errors are returned.

Following is a complex scenario I tested, and found the Date and Time to be at fault.

  • Comparing only Date, with no interval, returns a normal value.
  • The last Patient ID to work is 98472 (not all Patient IDs have been reported yet), the Patient has an arrival date of May 1st, 2018 at 8:42pm.
  • The next Patient ID is 100471, arriving on the 4th of May, 2018 at 10:43am. * The function returns this Patient as a #VALUE! error, although all parameters are there.

Here is the code (pardon any rookie mistakes, I'm no professional coder):

Function EINSATZ(aufnahmdat As Date, geburtsdat As Integer, geschlecht As Integer, klinik As Integer)
    'DEFINING PARAMETERS
    'rsu_r is the regional stroke unit row
    'rsu_c is the regional stroke unit column
    'size is the patient list size
    'iffunction allows the function to work through the patient list
    'converter converts letter to integer for sex

    Dim rsu_r As Integer
    Dim rsu_c As Integer
    Dim size As Variant
    Dim iffunction As Single
    Dim converter As Integer

    'here starts the dimension definition for rsu cells
    rsu_r = ActiveCell.Row
    rsu_c = ActiveCell.Column


    'here starts the size function
    'size is predetermined to measure and print the highest value within the first 9996 cells
    For iffunction = 4 To 9999
        If Application.WorksheetFunction.IsNumber(Worksheets("Präklinik").Cells(iffunction, 5)) Then
            size = size + 1
        End If
    Next iffunction

    'here starts the if function
    For iffunction = 4 To size        
        If Worksheets("Präklinik").Cells(iffunction, 6).Value = "m" Then
            converter = 2
        Else
            converter = 1
        End If

        If Worksheets("Präklinik").Cells(iffunction, 4).Value + Worksheets("Präklinik").Cells(iffunction, 17).Value < aufnahmdat + 1 / 48 _
           And Worksheets("Präklinik").Cells(iffunction, 4).Value + Worksheets("Präklinik").Cells(iffunction, 17).Value > aufnahmdat - 1 / 48 _
           And Worksheets("Präklinik").Cells(iffunction, 5).Value = geburtsdat _
           And converter = geschlecht _
           And Worksheets("Präklinik").Cells(iffunction, 41).Value = klinik Then
            EINSATZ = Worksheets("Präklinik").Cells(iffunction, 2).Value
            Exit For
        End If
    Next iffunction      
End Function

Please help me diagnose the actual cause of error!

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Meddy_Coder
  • 11
  • 2
  • I also thought it might be related to the absolute values of the date and time, so I printed them out: for the last "right" Patient ID, the date value is 43221.8625; for the next "error" Patient ID, the date value is 43224.44653. Both within the max and min values of an Integer type. – Meddy_Coder Nov 21 '18 at 12:53
  • 1
    Note that you cannot use `Integer` for row counting variables. Excel has more rows than `Integer` can handle. Always use `Long` instead. It is recommended [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in using `Integer` at all. – Pᴇʜ Nov 21 '18 at 13:07
  • Why is `geburtsdat As Integer` and not as `date`? • And you should declare a type for your function. Eg if it returns a date use: `Function EINSATZ( … ) As Date` in the first line. – Pᴇʜ Nov 21 '18 at 13:09
  • 2
    Probably not the cause of the error (because you never actually *use* `rsu_r` or `rsu_c`), but you should be using `Application.Caller` instead of `ActiveCell`. That said, nothing immediately jumps out (although I *suspect* it's an overflow). The best way to find your issue is to have a test `Sub` call the function with the same parameters that fail on the worksheet. That will get you into a debugger when the error is thrown. – Comintern Nov 21 '18 at 14:54

0 Answers0