Variable Date vs variant

[AB]

Hi,

I've built a ws for data storing (60 or so columns) and also I've
incorporated as if an audit trail. It works something like this - if a
value gets changed, it calls a procedure that passes variables
(oldVal, newVal) and calls auditTrail procedure:

sub auditTrail (oldVal, newVal)

worksheets("AuditTrail").cells(x, oldVal_COL)=oldVal
worksheets("AuditTrail").cells(x, newVal_COL)=newVal

end sub

I haven't declared the variable (oldVal, newVal) types on purpose as
they can be anything from String, to Double, to Date.
And that's why my question - how should I grab the variables from the
calling procedure - either as
..value [gues not]
..text
..value2
or with just specifying range (i.e., without .value or .text etc.)?

I'm just concerned that i might run into errors of having March 12
treated as Dec 03 instead.

Any suggestions how these should be handled?
Thanks,

 

[Kenneth Hobson]

Maybe something like:

'VOG II,http://www.mrexcel.com/forum/showthread.php?p=1666961
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NR As Long
If Intersect(Target, Range("F15:F25")) Is Nothing Then Exit Sub
With Sheets("Log")
.Unprotect Password:="xyz"
NR = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & NR).Value = Target.Address(False, False)
.Range("B" & NR).Value = Now
.Range("C" & NR).Value = Environ("username")
.Range("D" & NR).Value = Target.Value
.Range("D" & NR).NumberFormat = Target.NumberFormat
.Protect Password:="xyz"
End With
End Sub

 

[Dave Peterson]

I'd use .value2, but also keep track of the numberformat (oldNF and newNF???).
Then I wouldn't have to worry about troublesome dates or currency.

If you really don't care about the value in the cell, you may want to use .text
and make sure that the receiving cells are formatted as Text.

Hi,

I've built a ws for data storing (60 or so columns) and also I've
incorporated as if an audit trail. It works something like this - if a
value gets changed, it calls a procedure that passes variables
(oldVal, newVal) and calls auditTrail procedure:

sub auditTrail (oldVal, newVal)

worksheets("AuditTrail").cells(x, oldVal_COL)=oldVal
worksheets("AuditTrail").cells(x, newVal_COL)=newVal

end sub

I haven't declared the variable (oldVal, newVal) types on purpose as
they can be anything from String, to Double, to Date.
And that's why my question - how should I grab the variables from the
calling procedure - either as
.value [gues not]
.text
.value2
or with just specifying range (i.e., without .value or .text etc.)?

I'm just concerned that i might run into errors of having March 12
treated as Dec 03 instead.

Any suggestions how these should be handled?
Thanks,

 

[AB]

Thanks!
The idea of keeping the Old/New format should be helpful.

A problem it doesn't seem to solve is in scenario like this:
- range formatted as dd/mm/yyyy
- a user enters: '03/01/2009 [i.e., a string (because of the " ' ")
that looks like a date]
- the code passes over the format dd/mm/yyyy
- the code changes the value from '03/01/2009 to 01/03/2009...

Unfortunately this scenario is possible as one can never know what a
user decides to enter...

Is there any solution to this? Or i should enforce some data
validation (or calendar control) on all ranges that should contain a
date?
Any ideas?

 

[Dave Peterson]

Data|validation can't hurt.

But if you're only displaying the stuff you see in the cell, you could still
just use the .text property of the sending cell and use a Text format for the
receiving cell.

Or maybe you could incorporate something like:

Option Explicit
Sub testme()
Dim pfxChar As String
With ActiveSheet
pfxChar = .Range("a1").PrefixCharacter
.Range("b1").NumberFormat = .Range("a1").NumberFormat
.Range("b1").Value2 = pfxChar & .Range("a1").Value2
End With
End Sub

It worked in minor testing.

Thanks!
The idea of keeping the Old/New format should be helpful.

A problem it doesn't seem to solve is in scenario like this:
- range formatted as dd/mm/yyyy
- a user enters: '03/01/2009 [i.e., a string (because of the " ' ")
that looks like a date]
- the code passes over the format dd/mm/yyyy
- the code changes the value from '03/01/2009 to 01/03/2009...

Unfortunately this scenario is possible as one can never know what a
user decides to enter...

Is there any solution to this? Or i should enforce some data
validation (or calendar control) on all ranges that should contain a
date?
Any ideas?

 

[AB]

THANKS Dave! It seems that all:
..NumberFormat
..Value2
..PrefixCharacter
together could ensure that the actual data entered by a user (whatever
type) are stored.
Would the above be a correct statement?

I'm just concerned that i might have overlooked something (some weird
data/datatype combination that a user might come up with accidentally)
and the ws might end up storing something that the user actually
wasn't entering... How certain/safe the above is?
(e.g., when I tried .text - if the column is too narrow and a proper
date gets squeezed to look #### - .text grabs only the #### and not
the value behind it...)

Also, it would be great if you could advise the best way to validate a
date (i.e., to validate if the entered value is a valid date). Should
i use 'isnumeric' and then come up with 'valid ranges' or what's the
best way?

It would be really great if you could advise regarding both of the
above: (1) concern regarding entered vs. stored data (as I'm not
confident I've come up with all possible scenarios where VBA might
'convert' a value into something unexpected (to me anyway)), (2) Date
validation.
Thanks again!

 

[Dave Peterson]

That's all that I could think of--and it worked in my testing (very minor at
best).

Data|validation has an option for dates. Use that "Allow" dropdown and choose
Date.

 

[AB]

Thanks Dave! That will do.