Hoe schrijf je een Excel formule die een specifieke waarde in een andere cel plakt?

De volgende aanpak maakt gebruik van een workaround die hier en hier zijn beschreven om een in VBA gedefinieerde werkbladfunctie in staat te stellen de waarde van een andere cel in te stellen.

De aangepaste functie slaat in globale variabelen het adres op van de doelcel en de waarde waarop die cel moet worden ingesteld. Vervolgens leest een macro die wordt geactiveerd wanneer het werkblad opnieuw wordt berekend, de globale variabelen uit en stelt de doelcel in op de opgegeven waarde.

Het gebruik van de aangepaste functie is eenvoudig:

=SetCellValue(target_cell, value)

waarbij target_cell een tekenreeksverwijzing is naar een cel in het werkblad (bijv. “A1”) of een uitdrukking die evalueert naar een dergelijke verwijzing. Dit omvat een uitdrukking zoals =B14 waarbij de waarde van B14 “A1” is. De functie kan in elke geldige uitdrukking worden gebruikt.

SetCellValue retourneert 1 als de waarde met succes naar de doelcel is geschreven, en 0 anders. Eventuele eerdere inhoud van de doelcel wordt overschreven.

Er zijn drie stukjes code nodig:

• de code die SetCellValue zelf definieert
• de macro die getriggerd wordt door de werkbladberekeningsgebeurtenis; en
• een utiliteitsfunctie IsCellAddress om te verzekeren dat target_cell een geldig celadres is.

Code voor SetCellValue Functie

Deze code moet in een standaardmodule worden geplakt die in het werkboek wordt ingevoegd. De module kan worden ingevoegd via het menu voor de Visual Basic editor, dat toegankelijk is door Visual Basic te selecteren op het tabblad Developer van het lint.

Option Explicit

  Public triggerIt As Boolean
  Public theTarget As String
  Public theValue As Variant

  Function SetCellValue(aCellAddress As String, aValue As Variant) As Long

      If (IsCellAddress(aCellAddress)) And _
             (Replace(Application.Caller.Address, "$", "") <> _
              Replace(UCase(aCellAddress), "$", "")) Then
          triggerIt = True
          theTarget = aCellAddress
          theValue = aValue
          SetCellValue = 1
      Else
          triggerIt = False
          SetCellValue = 0
      End If

  End Function

Worksheet_Calculate Macro Code

Deze code moet worden opgenomen in de code die specifiek is voor het werkblad waarin u SetCellValue gaat gebruiken. De eenvoudigste manier om dit te doen is met de rechtermuisknop te klikken op het tabblad van het werkblad in de Home-weergave, View Code te selecteren, en dan de code te plakken in het deelvenster editor dat verschijnt.

Private Sub Worksheet_Calculate()

      If Not triggerIt Then
          Exit Sub
      End If
      triggerIt = False
      On Error GoTo CleanUp
      Application.EnableEvents = False
      Range(theTarget).Value = theValue
  CleanUp:
      Application.EnableEvents = True
      Application.Calculate

  End Sub

Code voor IsCellAddress Functie

Deze code kan in dezelfde module geplakt worden als de SetCellValue code.

Function IsCellAddress(aValue As Variant) As Boolean

      IsCellAddress = False

      Dim rng As Range ' Input is valid cell reference if it can be
      On Error GoTo GetOut ' assigned to range variable
      Set rng = Range(aValue)
      On Error GoTo 0

      Dim colonPos As Long 'convert single cell "range" address to
      colonPos = InStr(aValue, ":") 'single cell reference ("A1:A1" -> "A1")
      If (colonPos <> 0) Then
          If (Left(aValue, colonPos - 1) = _
                Right(aValue, Len(aValue) - colonPos)) Then
              aValue = Left(aValue, colonPos - 1)
          End If
      End If

      If (rng.Rows.Count = 1) And _
          (rng.Columns.Count = 1) And _
          (InStr(aValue, "!") = 0) And _
          (InStr(aValue, ":") = 0) Then
          IsCellAddress = True
      End If 'must be single cell address in this worksheet
      Exit Function

  GetOut:

  End Function

Laten we aannemen dat u de tekst “Tekst A” wilt laten zien in cel C5 als cel B5 de waarde “groen” bevat.

Je kunt een formule gebruiken, maar omdat formules geen waarden in andere cellen kunnen veranderen, zal de formule in cel C5 moeten worden ingevoerd.

=IF(ISNUMBER(FIND("green",B5)),"Text A","")

Cel C5 zal nu alleen “Tekst A” tonen als B5 het woord “groen” bevat.

Een formule als deze kan gebouwd worden om met vele voorwaarden te werken. Je moet je eisen definiëren om hulp te krijgen bij je specifieke situatie.

Als je niet wilt dat C5 een formule heeft, kun je ook een VBA-aanpak gebruiken. Je kunt een gebeurtenis Worksheet Change uitvoeren die telkens wordt uitgevoerd als cel B5 wordt gewijzigd, hetzij door de waarde handmatig te wijzigen, hetzij door er iets in te plakken.

Een voorbeeld voor zo'n macro zou kunnen zijn

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5")) Is Nothing Then
    If InStr(1, Target, "green", vbTextCompare) Then
        Target.Offset(0, 1) = "Text A"
    End If
End If
End Sub

De condities en de plaatsing van de uitvoer zijn natuurlijk maar een voorbeeld en moeten aan jouw eisen worden aangepast.

Het verschil tussen de formule- en de macro-aanpak is

• met de formule-aanpak zal cel C5 een formule bevatten. Als de gebruiker per ongeluk de formule verwijdert, wordt de functionaliteit die de formule levert ook verwijderd. (Er zijn echter manieren om dat te beheren)
• met de VBA macro, zal cel C5 geen formule tonen, en zal de woordelijke tekst als waarde hebben, maar het veranderen van de instelling vereist kennis van Excel VBA. Ook moet met een VBA-aanpak de werkmap worden opgeslagen als een werkmap met macro’s en moet de gebruiker macro’s toestaan of het bestand tot een vertrouwd bestand maken.

Opmerking: het bovenstaande is slechts een voorbeeld. U moet uw vereisten definiëren, of u getallen of tekst wilt evalueren, of de evaluatie hoofdlettergevoelig is, wat de evaluatieregels zijn, waar u het resultaat wilt plaatsen, enz.

Om te controleren of aan een voorwaarde is voldaan, schrijft u een IF-formule in een of andere cel om “een specifieke regel tekst in een andere cel” bij te werken. De resultaatcel zal alleen de waarde van de formule bevatten, NIET de formule die die waarde genereerde, zoals dit:

Werkcel (b.v. J5) =IF(A1="yes","Specific line of text","") Resultaat cel (b.v. B5) =J5

Dus als aan de voorwaarde is voldaan (A1=“ja”), zal B5 “Specifieke regel tekst” bevatten. Anders blijft hij leeg.

Noten: Cellen met variabele waarden bevatten gewoonlijk een of andere formule om de waarden te blijven bijwerken.

Als je niet wilt dat iemand de echte formule kent die de waarde creëert, kan de formule in een andere cel gaan om de waarde te genereren, die dan naar de resultaatcel wordt gekopieerd, bijv. B5.

Om de formule die de waarde genereert te verbergen of om de cel te verbergen waar B5 naar verwijst (om te blijven controleren of de waarde moet worden geactualiseerd), raadpleegt u: https://support.office.com/en-us/article/display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f

Andere functies die hetzelfde effect kunnen bereiken zijn CHOOSE, HLOOKUP, LOOKUP, VLOOKUP.

=value(cell)

Rechttoe rechtaan formule om te gebruiken, had hetzelfde probleem en het werkt.