Mixing
#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!
excel vba
edited Nov 22 '18 at 7:20
Pᴇʜ
22.4k42850
asked Nov 21 '18 at 12:43
Meddy_CoderMeddy_Coder
62
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!
excel vba
edited Nov 22 '18 at 7:20
Pᴇʜ
22.4k42850
asked Nov 21 '18 at 12:43
Meddy_CoderMeddy_Coder
62
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 useIntegerfor row counting variables. Excel has more rows thanIntegercan handle. Always useLonginstead. It is recommended always to use Long instead of Integer in VBA since there is no benefit in usingIntegerat all.
– Pᴇʜ
Nov 21 '18 at 13:07
Why isgeburtsdat As Integerand not asdate? • And you should declare a type for your function. Eg if it returns a date use:Function EINSATZ( … ) As Datein the first line.
– Pᴇʜ
Nov 21 '18 at 13:09
2
Probably not the cause of the error (because you never actually usersu_rorrsu_c), but you should be usingApplication.Callerinstead ofActiveCell. That said, nothing immediately jumps out (although I suspect it's an overflow). The best way to find your issue is to have a testSubcall 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!
excel vba
edited Nov 22 '18 at 7:20
Pᴇʜ
22.4k42850
asked Nov 21 '18 at 12:43
Meddy_CoderMeddy_Coder
62
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
excel vba
edited Nov 22 '18 at 7:20
Pᴇʜ
22.4k42850
asked Nov 21 '18 at 12:43
Meddy_CoderMeddy_Coder
62
edited Nov 22 '18 at 7:20
Pᴇʜ
22.4k42850
asked Nov 21 '18 at 12:43
Meddy_CoderMeddy_Coder
62
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
22.4k42850
asked Nov 21 '18 at 12:43
Meddy_CoderMeddy_Coder
62
asked Nov 21 '18 at 12:43
Meddy_CoderMeddy_Coder
62
asked Nov 21 '18 at 12:43
Meddy_CoderMeddy_Coder
62
62
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 useIntegerfor row counting variables. Excel has more rows thanIntegercan handle. Always useLonginstead. It is recommended always to use Long instead of Integer in VBA since there is no benefit in usingIntegerat all.
– Pᴇʜ
Nov 21 '18 at 13:07
Why isgeburtsdat As Integerand not asdate? • And you should declare a type for your function. Eg if it returns a date use:Function EINSATZ( … ) As Datein the first line.
– Pᴇʜ
Nov 21 '18 at 13:09
2
Probably not the cause of the error (because you never actually usersu_rorrsu_c), but you should be usingApplication.Callerinstead ofActiveCell. That said, nothing immediately jumps out (although I suspect it's an overflow). The best way to find your issue is to have a testSubcall 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 useIntegerfor row counting variables. Excel has more rows thanIntegercan handle. Always useLonginstead. It is recommended always to use Long instead of Integer in VBA since there is no benefit in usingIntegerat all.
– Pᴇʜ
Nov 21 '18 at 13:07
Why isgeburtsdat As Integerand not asdate? • And you should declare a type for your function. Eg if it returns a date use:Function EINSATZ( … ) As Datein the first line.
– Pᴇʜ
Nov 21 '18 at 13:09
2
Probably not the cause of the error (because you never actually usersu_rorrsu_c), but you should be usingApplication.Callerinstead ofActiveCell. That said, nothing immediately jumps out (although I suspect it's an overflow). The best way to find your issue is to have a testSubcall 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
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
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
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
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
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
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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
Integerfor row counting variables. Excel has more rows thanIntegercan handle. Always useLonginstead. It is recommended always to use Long instead of Integer in VBA since there is no benefit in usingIntegerat all.– Pᴇʜ
Nov 21 '18 at 13:07
Why is
geburtsdat As Integerand not asdate? • And you should declare a type for your function. Eg if it returns a date use:Function EINSATZ( … ) As Datein the first line.– Pᴇʜ
Nov 21 '18 at 13:09
2
Probably not the cause of the error (because you never actually use
rsu_rorrsu_c), but you should be usingApplication.Callerinstead ofActiveCell. That said, nothing immediately jumps out (although I suspect it's an overflow). The best way to find your issue is to have a testSubcall 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