[VBA] Excel Cstr Date formatting gaat opeens fout

[Da_maniaC]

Ik heb een Excel sheet met wat VBA code.
1 van de dingen die de VBA doet is wanneer een gebruiker een nieuwe regel met gegevens invoert (middels een userform), op deze nieuwe regel ook een Cell vullen met de datum.
Om te voorkomen dat datum notities de mist in gaan etc. (bij het maken van de sheet veel tegenaan gelopen) gebruik ik wat code die de datum waarde omzet naar String/Text.

De datum wordt standaard getoond in een Date picker van het userform zodat de gebruiker deze eventueel nog kan wijzigen. Doet de gebruiker niets met de date picker, dan wordt gewoon de huidige datum ingevoerd.

Nu is deze week de nieuwe maand in gegaan (februari) en ondanks dat er totaal geen wijziging in de code heeft plaatsgevonden gaat Excel nu toch opeens anders met de datum notitie om.

Voorbeeld:


Voorbeeld van de VBA Code die ik gebruik voor het wegschrijven van deze datum waarde/text:

code:

Option Explicit

Public CurDate As String, LastRowInvoer, InvoerTime, InvoerDate As String

Private Sub UserForm_Activate()

Me.InvoerDatum.Value = Now

End Sub

Private Sub OKButton_Click()

CurDate = CStr(Format(Now, "DD-MM-YY"))
InvoerDate = CStr(Format(Me.InvoerDatum.Value, "DD-MM-YY"))
InvoerTime = CStr(Format(Me.InvoerDatum.Value, "HH:MM"))

LastRowInvoer = ThisWorkbook.Sheets("Data Invoer").Range("B65534").End(xlUp).Row + 1

                    If InvoerDate < CurDate Then
                        InvoerTime = Format("00:00", "HH:MM")
                    End If

                    ThisWorkbook.Sheets("Data Invoer").Range("C" & LastRowInvoer) = InvoerDate                      
                    ThisWorkbook.Sheets("Data Invoer").Range("D" & LastRowInvoer) = InvoerTime   

Unload Me

End Sub

Heeft iemand enig idee waarom de Datum nu toch opeens van notatie veranderd?
Het zou een text string moeten zijn toch?

De formattering van de doel cellen heb ik ook nagekeken....deze is nog niet aangeraakt (maw. niet vooraf geformatteerd als Date bijvoorbeeld).

[Hegeman]

Da_maniaC schreef op woensdag 1 februari 2017 @ 10:35:
Ik heb een Excel sheet met wat VBA code.
1 van de dingen die de VBA doet is wanneer een gebruiker een nieuwe regel met gegevens invoert (middels een userform), op deze nieuwe regel ook een Cell vullen met de datum.
Om te voorkomen dat datum notities de mist in gaan etc. (bij het maken van de sheet veel tegenaan gelopen) gebruik ik wat code die de datum waarde omzet naar String/Text.

De datum wordt standaard getoond in een Date picker van het userform zodat de gebruiker deze eventueel nog kan wijzigen. Doet de gebruiker niets met de date picker, dan wordt gewoon de huidige datum ingevoerd.

Nu is deze week de nieuwe maand in gegaan (februari) en ondanks dat er totaal geen wijziging in de code heeft plaatsgevonden gaat Excel nu toch opeens anders met de datum notitie om.

Voorbeeld:
[afbeelding] [afbeelding]

Voorbeeld van de VBA Code die ik gebruik voor het wegschrijven van deze datum waarde/text:

code:

Option Explicit

Public CurDate As String, LastRowInvoer, InvoerTime, InvoerDate As String

Private Sub UserForm_Activate()

Me.InvoerDatum.Value = Now

End Sub

Private Sub OKButton_Click()

CurDate = CStr(Format(Now, "DD-MM-YY"))
InvoerDate = CStr(Format(Me.InvoerDatum.Value, "DD-MM-YY"))
InvoerTime = CStr(Format(Me.InvoerDatum.Value, "HH:MM"))

LastRowInvoer = ThisWorkbook.Sheets("Data Invoer").Range("B65534").End(xlUp).Row + 1

                    If InvoerDate < CurDate Then
                        InvoerTime = Format("00:00", "HH:MM")
                    End If

                    ThisWorkbook.Sheets("Data Invoer").Range("C" & LastRowInvoer) = InvoerDate                      
                    ThisWorkbook.Sheets("Data Invoer").Range("D" & LastRowInvoer) = InvoerTime   

Unload Me

End Sub

Heeft iemand enig idee waarom de Datum nu toch opeens van notatie veranderd?
Het zou een text string moeten zijn toch?

De formattering van de doel cellen heb ik ook nagekeken....deze is nog niet aangeraakt (maw. niet vooraf geformatteerd als Date bijvoorbeeld).

Ik heb hier zelf niet super veel verstand van, maar ligt het niet gewoon aan de opmaak van je cel? Is deze wel opgemaakt op de manier waarop jij de datum wil zien?

[Da_maniaC]

Hegeman schreef op woensdag 1 februari 2017 @ 10:39:
[...]


Ik heb hier zelf niet super veel verstand van, maar ligt het niet gewoon aan de opmaak van je cel? Is deze wel opgemaakt op de manier waarop jij de datum wil zien?

Zoals aangegeven in mijn TS:
De formattering van de doel cellen heb ik ook nagekeken....deze is nog niet aangeraakt (maw. niet vooraf geformatteerd als Date bijvoorbeeld).

De waarde die wordt weggeschreven zou dan moeten bepalen wat de formatting van de cell uiteindelijk wordt (bijvoorbeeld General of Text). Dat heeft voor de gehele maand Januari goed gewerkt.

[nescafe]

Zolang je datum niet naar string converteert gaat het prima:

[Da_maniaC]

nescafe schreef op woensdag 1 februari 2017 @ 10:44:
Zolang je datum niet naar string converteert gaat het prima:

[afbeelding]

Maar wat als ik nu juist wil forceren dat er een textuele waarde komt te staan met "01-02-2017" in plaats van een datum? Is dat wel mogelijk?
(Bij de maand Januari lukte dit wel).

[ Voor 46% gewijzigd door Da_maniaC op 01-02-2017 10:51 ]

[Atanamir]

Ik denk dat het eraan ligt dat de januari invoer niet als datum wordt herkend door Excel omdat die blijkbaar naar US notatie zoekt.

Je zou nog kunnen proberen in de string een single quote op te nemen om het zo als een comment ipv een value weg te schrijven. Iets van invoerdate = Chr(39) & invoerdate ofzo...

[nescafe]

Da_maniaC schreef op woensdag 1 februari 2017 @ 10:50:
[...]

Maar wat als ik nu juist wil forceren dat er een textuele waarde komt te staan met "01-02-2017" in plaats van een datum? Is dat wel mogelijk?
(Bij de maand Januari lukte dit wel).

Dat is iets wat je met NumberFormat regelt; eerst instellen via celeigenschappen en dan via Msgbox ActiveCell.NumberFormat het daadwerkelijke format bekijken.

code:

    Range("A1").NumberFormat = "@"
    Range("A1").Value = CStr(Date)
    Range("A2").NumberFormat = "m/d/yyyy"
    Range("A2").Value = Date
    Range("A3").NumberFormat = "dd/mm/yyyy"
    Range("A3").HorizontalAlignment = xlLeft
    Range("A3").Value = Date

Ehm..

Resultaat:



Ik zei niet dat het logisch was Maar het werkt dus prima zo.. (NumberFormat = "@" is misschien eerder wat je zoekt)

[Da_maniaC]

Dank voor de tips!

Deze aanpassing geeft geen "Text" als date maar de formattering is in ieder geval correct.

code:

                    ThisWorkbook.Sheets("Data Invoer").Range("C" & LastRowInvoer).NumberFormat = "@"
                    ThisWorkbook.Sheets("Data Invoer").Range("C" & LastRowInvoer) = InvoerDate                      'Ingevoerde datum invoeren

Zou dit nog problemen opleveren wanneer een Windows installatie met een US locale dezelfde datum in gaan voeren?

[Lustucru]

Da_maniaC schreef op woensdag 1 februari 2017 @ 10:50:
[...]

Maar wat als ik nu juist wil forceren dat er een textuele waarde komt te staan met "01-02-2017" in plaats van een datum? Is dat wel mogelijk?
(Bij de maand Januari lukte dit wel).

Ik kan me eerlijk gezegd geen scenario voorstellen waarbij je dat zou willen. Je introduceert nu nieuwe problemen omdat je een foute oplossing hebt voor een ander probleem.


42767 is eenduidig. Dat staat voor 1 februari 2017. "01-02-2017" kan geinterpreteerd worden als 2 januari.

[Paultje3181]

Alleen zo jammer dat 42767 voor niemand eenvoudig te lezen is. Als je in je bedrijf alleen Nederlanders hebt, is de kans dat het gelezen wordt als 2 januari niet zo heel groot normaal gesproken.

[Lustucru]

Het gaat niet om menselijke lezers. Computers maken de interpretatiefout. En voor computers is 42767 veel eenvoudiger te lezen dan "01-02-2017".

Het fijne van Excel is dat je data en presentatie kunt scheiden. Dus op het scherm kun je het prima presenteren zoals je collega's dat graag zien. Zolang je maar van de data afblijft.

[biomass]

Da_maniaC schreef op woensdag 1 februari 2017 @ 10:35:


Voorbeeld van de VBA Code die ik gebruik voor het wegschrijven van deze datum waarde/text:

code:

..

De weergave van de waarde in de cellen moet je gescheiden zien van de waarde van het onderliggende type van de .Value van de cel. In regel 7 schrijf je een VBA.Date naar de .Value property van de DatePicker. Prima.

In regel 23 en 24 laat je het van de automatische conversie van Range.Value afhangen wat de onderliggende waarde van de cel wordt. VBA.String of VBA.Date. Niet doen, en gebruik de VBA datum functies (DateDiff in regel 19)

DateSerial, TimeSerial, Hour, Minute, Second Year Month Day - InvoerDatum.Value

[ Voor 5% gewijzigd door biomass op 01-02-2017 17:53 ]

[kakanox]

Paultje3181 schreef op woensdag 1 februari 2017 @ 17:16:
Alleen zo jammer dat 42767 voor niemand eenvoudig te lezen is. Als je in je bedrijf alleen Nederlanders hebt, is de kans dat het gelezen wordt als 2 januari niet zo heel groot normaal gesproken.

Als je anders wil presenteren dan "standaard" mogelijk is zou ik de opmaak definiëren als aangepast, en er dan wel een datumwaarde in schieten.
Als je die aangepaste opmaak dan vult met dd-mm-jjjj zit je altijd goed (als je zelf tenminste werkt met een Nederlandse Excel, anders voor Engels dd-mm-yyyy ).

Wil je per se een datumveld hebben, dan kan je tussen de andere talen kijken voor een bepaalde formattering.
Ik gebruik bijvoorbeeld vaak Engels (even uit m'n hoofd) om te kunnen formatteren als 2017-05-03 (en gebruik die datumwaarde dan weer voor het genereren van bestandsnamen).

[ Voor 5% gewijzigd door kakanox op 01-02-2017 17:48 . Reden: NL Excel ]

[Lustucru]

Da_maniaC schreef op woensdag 1 februari 2017 @ 10:35:
Voorbeeld van de VBA Code die ik gebruik voor het wegschrijven van deze datum waarde/text:
[code]
Public CurDate As String, LastRowInvoer, InvoerTime, InvoerDate As String

[...]
If InvoerDate < CurDate Then
InvoerTime = Format("00:00", "HH:MM")
End If

BTW, dit is te zot voor woorden. Wat denk je: is "01-04-2017" kleiner dan "02-01-2017"?
Het antwoord is ja.

Om op het antwoord in je openingspost terug te komen. Excel probeert een ingave te herkennen. Van de tekst 31-01-2017 kan hij op jouw systeem geen chocolade pakken en presenteert het als tekst. 1-2-2017 lukt wel. Maw troost je: vanaf 13 februari werkt je code weer goed.

[ Voor 19% gewijzigd door Lustucru op 01-02-2017 18:01 ]

[biomass]

Je zou kunnen beginnen met

code:

Private CurDate As Date
Public LastRowInvoer As Long
Private InvoerTime As Date
Private InvoerDate As Date

[Atanamir]

Zou het ook geen optie zijn om dd-MMM-yyyy te gebruiken? Daardoor is het voor zowel NL als US locale duidelijke welke datum het is.

[Da_maniaC]

Iedereen bedankt voor de tips.

Lustucru schreef op woensdag 1 februari 2017 @ 17:59:
[...]

BTW, dit is te zot voor woorden. Wat denk je: is "01-04-2017" kleiner dan "02-01-2017"?
Het antwoord is ja.

Hoezo is dit te zot voor woorden? Je weet toch niet exact in welke context het wordt gebruikt?
Gebruikers van deze sheet voeren *altijd* data in met de huidige dagdatum.
Indien men met terugwerkende kracht iets invoert wordt de tijdwaarde aangepast naar 00:00 (in plaats van de huidige tijd). Dat is gewoon omdat anders de huidige tijd ook zou worden ingevuld en dat wilde ik voorkomen (zonder tussenkomst van wederom een extra invoerveld om de gebruiker mee lastig te vallen).
De enige reden dat ik het hier liet zien was omdat ik alle code wilde laten zien in de module waar iets met de datum gebeurde.

[Lustucru]

Da_maniaC schreef op donderdag 2 februari 2017 @ 16:10:
Hoezo is dit te zot voor woorden? [...] Indien men met terugwerkende kracht iets invoert dan [...]

Omdat je datums wilt vergelijken maar je vergelijkt strings. "31-01-2017" is 'groter' dan "03-02-2017".

[Da_maniaC]

Lustucru schreef op donderdag 2 februari 2017 @ 19:43:
[...]


Omdat je datums wilt vergelijken maar je vergelijkt strings. "31-01-2017" is 'groter' dan "03-02-2017".

Okay, dat is helemaal waar. Thanks for pointing that out.
Ik heb besloten om deze functionaliteit maar te vervangen (de oorspronkelijke tijd te laten staan en niet maar aan te raken.... maar de font color blauw te maken in plaats van zwart... zo kan je nog steeds zien dat de regel gewijzigd wordt).

[ Voor 40% gewijzigd door Da_maniaC op 02-02-2017 20:16 ]

[Lustucru]

Oke, ik praat tegen dovemansoren, maar voor de laatste keer: doe jezelf en plezier en sloop die hele omzetting van datum naar tekst eruit. Zijn al je problemen opgelost.