[vba][excel][office 2003] Verwijderen VBA formula na uitvoer

[hufkes]

In een vrij uitgebreide excelsheet met diverse formules is er eentje die absoluut nooit automatisch uitgevoerd moet worden, het moet echt een handmatige en eenmalige aanroep zijn.

Ik had dus het idee opgevat om deze formule vanuit de vba-code zelf met iets in de trant van ActiveCell.Formula = vbEmpty te willen 'resetten' zodat er nadat de formule is uitgevoerd en het resultaat er staat geen formule meer in de cell aanwezig is. Dit gaat echter niet, en ook de mogelijkheden die je kunt vinden over het verwijderen van de formule met biojvoorbeeld .Range("A1").value = .Range("A1").value zorgt voor fouten.

Enig nadenken brengt mij bij de vrij logische conclusie dat je klaarblijkelijk niet vanuit je 'eigen formule' je eigen aanroep kunt wissen, en ook een call naar een andere functie die vervolgens de formule in de aanroepende cell verwijdert is niet mogelijk gebleken.

Dan is de oplossing die overblijft volgens mij een 'on change' event-trigger gebruiken waarbij -vanuit een andere routine dus- er na een cell-aanpassing altijd de formule-aanroep met die naam indien aanwezig wordt verwijderd.

Dat laatste heb ik dan echter wat hulp bij nodig

Iemand een idee of andere oplossing natuurlijk? Het is dus een groot werkboek met veel formules die wel continu moeten worden bijgewerkt, dus gewoon calculate uitzetten is geen optie, tenzij ik dat ook weer voor die ene specifieke cell waar deze formule in ingevoerd is kan doen, maar ook een F9 mag geen nieuwe aanroep triggeren.

[ Voor 6% gewijzigd door hufkes op 18-11-2010 17:59 ]

[Lustucru]

Gewoon geen formule in de cel

Visual Basic:

Sub DoeHetDingetje()
     cells[kolom,rij]=f(x,y,z)  'doe de berekening en wijs hem toe aan de cel
End Sub

en zie daar, berekening wordt alleen uitgevoerd als jij de macro aanroept.

[hufkes]

Lustucru schreef op donderdag 18 november 2010 @ 18:10:
Gewoon geen formule in de cel

Visual Basic:

Sub DoeHetDingetje()
     cells[kolom,rij]=f(x,y,z)  'doe de berekening en wijs hem toe aan de cel
End Sub

en zie daar, berekening wordt alleen uitgevoerd als jij de macro aanroept.

mm, ja, logische reactie, ik had zelf ook al bedacht dat ik wel gewoon een menuutje kan maken en daarvandaar de macro aanroepen, maar het is een sheet die ik 'eventjes' aanpas, waar al zeker 6 mensen niet gehinderd door enige structuur aan hebben zitten werken en een project waar ik maar tijdelijk bij betrokken ben.... Kortom, ik zocht een makkelijke manier, en misschien is dan een keyboard shortcut naar een macro die zijn ding doet en dus geen formule in de cel achterlaat wel de beste optie.

Ga ik eens naar kijken..
Moet ik alleen nog even een loopje maken die een lijst uitspuugd van alle plaatsen waar nu wél die foute formule gebruikt wordt.

Maar dan puur voor het idee, een event op on-change van de cel met een

code:

if tolower(left(activecell.formula, 9))) = "recode(af" then
    activecell.formula = vbEmpty
end if

Dat zou toch moeten kunnen werken? Of denk ik nu echt verkeerd...

[Lustucru]

vziw heeft excel 2003 geen onchange event van een cel. Bovendien triggert een berekening niet het changeevent. Dan zou je nog kunnen bedenken dat je het calculate event kunt gebruiken maar dan ben je terug bij af: je zult moeten opsporen welke cellen de formule gebruiken.

Wijzigingen aanbrengen in een cel dmv een formule die door die cel wordt aangeroepen levert iig een kringverwijzing op. Laatste kans die je hebt is om een static variabele op te nemen om bij te houden of de functie al eens is aangeroepen maar ook dat blijft prutswerk. De enige werkbare opties is imho het zo te bouwen dat het geen kwaad kan als de functie vaker wordt aangeroepen.

[hufkes]

Je hebt ook gelijk, ik heb inmiddels een menu met de verwijzing naar de formule aangepast en handmatig met find alle desbetreffende formules gezocht en verwijderd.

Enige dat me nog niet gelukt is is om een shortcut in de vorm van ctrl-shift-R aan die macro te koppelen

code:

cbcCustomMenu.Controls.Add(Type:=msoControlButton)
    .Caption = "&Recode"
    .onAction = "Recode"

waarna ik .de alt-x-r shortcut kan gebruiken die dus het menu opent en de desbetreffende actie start, maar ik zou liever nog gewoon ctrl-shift-r eraan koppelen. Enig idee hoe ik die er nog aan toevoeg?

[Lustucru]

via de ui of

code:

Application.MacroOptions Macro:="recode", Description:="foo", ShortcutKey:="R"

[hufkes]

Lustucru schreef op vrijdag 19 november 2010 @ 01:30:
via de ui of

code:

Application.MacroOptions Macro:="recode", Description:="foo", ShortcutKey:="R"

Met jouw code kwam ik er niet uit, maar met de onKey wel:

code:

Application.OnKey "+^{R}", "Module1.RecodeAddress"

De + staat voor shift, de ^ voor control en tussen haken de letter die er bij hoort.

Deze code heb ik in de Workbook_Activate() van ThisWorkbook gezet, en dat werkt als een tierelier.