#VALUE Error in VBA after a specific date value

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!

edited Nov 22 '18 at 7:20

Pᴇʜ

22.4k42850

asked Nov 21 '18 at 12:43

Meddy_Coder

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 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

add a comment |

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!

edited Nov 22 '18 at 7:20

Pᴇʜ

22.4k42850

asked Nov 21 '18 at 12:43

Meddy_Coder

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 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

add a comment |

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!

edited Nov 22 '18 at 7:20

Pᴇʜ

22.4k42850

asked Nov 21 '18 at 12:43

Meddy_Coder

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!

excel vba

edited Nov 22 '18 at 7:20

Pᴇʜ

22.4k42850

asked Nov 21 '18 at 12:43

Meddy_Coder

edited Nov 22 '18 at 7:20

Pᴇʜ

22.4k42850

asked Nov 21 '18 at 12:43

Meddy_Coder

edited Nov 22 '18 at 7:20

Pᴇʜ

22.4k42850

edited Nov 22 '18 at 7:20

Pᴇʜ

22.4k42850

edited Nov 22 '18 at 7:20

Pᴇʜ

22.4k42850

asked Nov 21 '18 at 12:43

Meddy_Coder

asked Nov 21 '18 at 12:43

Meddy_Coder

asked Nov 21 '18 at 12:43

Meddy_Coder

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 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

add a comment |

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 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

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

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 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

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

add a comment |

0

active

oldest

votes

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53412300%2fvalue-error-in-vba-after-a-specific-date-value%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

0

active

oldest

votes

0

active

oldest

votes

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu