Huidige cel oproepen, maar NIET ActiveCell

[edeboeck]

Hallo allemaal
Wie ongeduldig is: scroll door naar het einde van deze startpost voor de werkelijke vraag

ik kreeg volgende vraag van een collega:

als ik in Excel in een cel invul:

code:

=ALS(A1>0;NU();" ")

(dus als cel A1 niet nul is moet je in een bepaalde cel de actuele tijd invullen)
dan krijg ik in de cel inderdaad de actuele tijd te zien, maar die verandert constant telkens ik bv ergens ander op Enter druk of sheet opnieuw open etc.
Kan jij een manier om die waarde die via nu() is ingevoerd meteen ook VAST te maken zodat die niet verandert?

offtopic:
Ik weet wel dat de voorwaarde uit zijn voorbeeld overeenkomt met A1 groter dan nul ipv niet nul, maar dat doet even niet terzake

Terminologie: ik noem in dit voorbeeld A1 de controlecel en de cel waarin de datum+tijd moet komen de doelcel.

Ik dacht aan volgende oplossingen, maar geen enkele doet het volledig correct of zonder work-around.

Oplossing 1:
Zelf een macro definiëren en die telkens oproepen als je datum+tijd wilt laten invullen.

Visual Basic:

Public Sub VulDatumIn()
    Dim strDoelCel As String
    Dim strControleCel As String
    
    strControleCel = InputBox("Geef het adres in van de controlecel", , "A1")
    strDoelCel = InputBox("Geef het adres in van de cel waarin de datum ingevuld moet worden", , "A2")
    
    Dim strDoelValue As String
    Dim strControleCelValue As Integer
    strDoelValue = Trim(Range(strDoelCel).Value)
    strControleCelValue = Range(strControleCel).Value
    
    If strDoelValue = "" And strControleCelValue <> 0 Then
        Range(strDoelCel).Value = DateTime.Now
    End If
End Sub

Het nadeel is duidelijk:je moet zelf telkens op de knop drukken. In dat geval lijkt het me zinvoller om Ctrl+Shift+; te gebruiken om de huidige datum+tijd in te vullen.

Oplossing 2:
Een eigen functie definiëren en die oproepen in de doelcel.

Visual Basic:

Public Function EenmaligeDatum()
    Dim strReturnValue As String
    strReturnValue = ActiveCell.Text
    If (Trim(strReturnValue) = "0" Or Trim(strReturnValue) = "") And Range("A1").Value > 0 Then
        strReturnValue = DateTime.Now
    End If
    EenmaligeDatum = strReturnValue
End Function

Nadeel: de doelcel wordt niet herrekend als je de waarde in de controlecel aanpast. Bovendien is het adres van de controlecel hard-coded (al kan je dit opvangen via inputbox).

Oplossing 3:
Dit zou dé oplossing kunnen zijn: als we het adres van de controlecel doorgeven als parameter aan onze functie, wordt de doelcel wel herberekend bij aanpassing van de inhoud van de controlecel.

Visual Basic:

Public Function EenmaligeDatum(objControleCel As Range)
    Dim strReturnValue As String
    strReturnValue = ActiveCell.Text
    If (Trim(strReturnValue) = "0" Or Trim(strReturnValue) = "") And objControleCel.Value > 0 Then
        strReturnValue = DateTime.Now
    End If
    EenmaligeDatum = strReturnValue
End Function

Spijtig nadeel: dit werkt de eerste keer wel goed, maar als we nadien onze controlecel aanpassen, verwijst ActiveCell naar de controlecel, niet naar de doelcel.

Work-around oplossing:
Volgende code doet het werk correct, maar het nadeel is dat we telkens zelf de doelcel als tweede parameter moeten meegeven.

Visual Basic:

Public Function EenmaligeDatum(objControleCel As Range, objDoelCel As Range)
    Dim strReturnValue As String
    strReturnValue = Trim(objDoelCel.Text)
    If strReturnValue = "0" Then
        strReturnValue = ""
    End If
    If strReturnValue = "" And objControleCel.Value > 0 Then
        strReturnValue = DateTime.Now
    End If
    EenmaligeDatum = strReturnValue
End Function

Het nadeel is dat dit uiteraard een work-around is voor het échte probleem:
Kun je het adres te weten komen van waaruit een bepaalde formule wordt opgeroepen?

Dr. Google kan me niet direct helpen.

Wie-o-wie vindt het antwoord?

[Verwijderd]

je kan op 2 manieren tewerk gaan. ik raad de eerste aan, het gebruik van een ingebouwde worksheet gebeurtenisafhandelaar waar inderdaad het adres meegegeven wordt :

Visual Basic:

Private Sub Worksheet_Change(ByVal Target As Range)
  If ("$A$1" = Target.Address) Then
    If (0 < Target.Value) Then

    Else
    
    End If
  End If
End Sub

gezien je TS ga ik er vanuit dat het duidelijk is. de code moet in de worksheetmodule geplaatst worden.

een 2de mogelijkheid is een UDF (user defined function) die getriggerd wordt van zodra er een een cel herberekend wordt. dit wordt mogelijk gemaakt dmv het statement application.volatile in deze functie op te nemen en dan op te roepen dmv =eenmaligedatum(a1)+nu()*0

[pedorus]

Hoe stabiel zou dit zijn?

Visual Basic:

Public Function StaticNow() As String
    Dim result As String
    result = Application.Caller.Text
    If (Trim(result) = "0" Or Trim(result) = "") Then
        result = DateTime.Now
    End If
    StaticNow = result
End Function

Technisch i18n-probleem hiermee: Dit is in ieder geval nog niet echt hetzelfde als =now() omdat dat de cellopmaak op datum zet en een double retourneert. Als nu initieel het numberformat op datum staat werkt het bijvoorbeeld niet goed (idee: converteren met cdate en testen op 0). Eigenlijk zou je Value2 willen gebruiken, en doubles willen retourneren, maar dat werkt niet goed omdat Value/Value2 een circular reference geeft, en je numberformat niet kan instellen vanuit een formule-functie. Formules op het resultaat zullen dus niet werken in alle locales. Ik denk niet dat hier een oplossing voor is, maar misschien zie ik iets over het hoofd.

Misschien dat trouwens zelfs dit goed gaat, met ander gedrag bij kopiëren, en fout gedrag bij CalculateFullRebuild, het is tenslotte geen volatile function:

Visual Basic:

Public Function StaticNow() As String
    StaticNow  = DateTime.Now
End Function