E
Enz
I have coded a macro function that takes as input a cell value that
could be a range that looks like "1-3 May" or "31 Aug". The function
returns a value only if the range provided as input is a seven day
range. The function works well in term of the algorithm to determine
this.
The function is distributed to a global network, and it does not work
only at one site (located in Budapest), but works for all other sites
(at least it seems to). I have interactively tested with the folks
having the issue, and when they execute the function the function
returns "#VALUE!" instead of a valid value. I traced the logic being
executed in debug mode and on their machine the execution stream is as
follows:
In the site where it does not work, the function executes correctly
until it hits the Day function execution line. It does not show a
value in ltempvariant and it does not error. The next statement
executed is "s=" for the next iteration of the function. It does not
execute the rest of the function (i.e. never gets to the line "If
IsError(ltempvariant) Then" - and does not provide any error). If I
take the same macro file and open it and use Ctrl - Alt - F9, on my
laptop, all the "#Value!' errors go away and I am able to save it
(with all values corrected) and send it back to them. Once they try
using Ctrl - Alt - F9 on the corrected file they once again get the
same error.
I have checked and their version of Excel (and Service Packs) is the
same as mine as are their options within Excel.
At this point I am not sure if my logic is weak in some way with
regards to potential environmental variables? But in addition, I
cannot see how the rest of the logic in the function is completely
ignored by the execution on the site machine but not on mine? That
one is bizarre.
Does anyone have any suggestions or seen such a phenomenon occur
before by chance?
Any feedback is greatly appreciated.
regards,
Enzo
===========================================================================
Dim lfirst As Integer
Dim lsecond As Integer
Dim llength As Integer
Dim ltempvariant As Variant
s = CStr(lDateCell.Value)
llength = CInt(Len(s))
lfirst = 0
lsecond = 0
If (s <> "") Then
' Convert date to a day using the Excel Function
ltempvariant = Day(s)
If IsError(ltempvariant) Then
Else
…..
......
......
could be a range that looks like "1-3 May" or "31 Aug". The function
returns a value only if the range provided as input is a seven day
range. The function works well in term of the algorithm to determine
this.
The function is distributed to a global network, and it does not work
only at one site (located in Budapest), but works for all other sites
(at least it seems to). I have interactively tested with the folks
having the issue, and when they execute the function the function
returns "#VALUE!" instead of a valid value. I traced the logic being
executed in debug mode and on their machine the execution stream is as
follows:
In the site where it does not work, the function executes correctly
until it hits the Day function execution line. It does not show a
value in ltempvariant and it does not error. The next statement
executed is "s=" for the next iteration of the function. It does not
execute the rest of the function (i.e. never gets to the line "If
IsError(ltempvariant) Then" - and does not provide any error). If I
take the same macro file and open it and use Ctrl - Alt - F9, on my
laptop, all the "#Value!' errors go away and I am able to save it
(with all values corrected) and send it back to them. Once they try
using Ctrl - Alt - F9 on the corrected file they once again get the
same error.
I have checked and their version of Excel (and Service Packs) is the
same as mine as are their options within Excel.
At this point I am not sure if my logic is weak in some way with
regards to potential environmental variables? But in addition, I
cannot see how the rest of the logic in the function is completely
ignored by the execution on the site machine but not on mine? That
one is bizarre.
Does anyone have any suggestions or seen such a phenomenon occur
before by chance?
Any feedback is greatly appreciated.
regards,
Enzo
===========================================================================
Dim lfirst As Integer
Dim lsecond As Integer
Dim llength As Integer
Dim ltempvariant As Variant
s = CStr(lDateCell.Value)
llength = CInt(Len(s))
lfirst = 0
lsecond = 0
If (s <> "") Then
' Convert date to a day using the Excel Function
ltempvariant = Day(s)
If IsError(ltempvariant) Then
Else
…..
......
......