Relatieve verwijzing gebruiken in eigen functie.

[Jan van Asseldonk]

Meestal kan ik anderen vooruit helpen met antwoorden, maar nu kom ik er zelf even niet uit en roep ik jullie hulp in.
Ik heb een eigen functie nodig voor het berekenen van een bepaalde waarde op een bepaalde plaats. Deze is afhankelijk van een aantal cellen die steed op dezelfde afstand liggen van de cel waarin de functie ingevoerd wordt.
De functie kan op meerdere plaatsen in de sheet nodig zijn.
In bijgaand voorbeeldje heb ik een simpele voorstelling gemaakt en wat verdere uitleg gegeven.
De functie werkt op zich wel zoals ik die nu heb, maar moet steeds handmatig opnieuw geactiveerd worden zodra er waarden gewijzigd worden.
Ik heb al op vele plaatsen gezocht maar datgene wat ik zoek nog niet gevonden.
Tips voor verdere zoektochten zij ook altijd welkom.

 

[Wigi]

En waarom dan niet gewoon in D6:

=ALS(B3="ja";(C3+C4)/C5;C7)

 

[Jan van Asseldonk]

Wigi,

de functie moet op meerdere plaatsen bruikbaar zijn.
In het voorbeeldje heb ik een viertal variabelen gebruikt.
De functie moet in een template komen staan die steeds toegevoegd kan worden aan een bestaand blad. Dit toevoegen vindt plaats door de template te openen, de inhoud te kopiëren, deze onder de gegevens op het blad te plakken en de template weer te sluiten.

In de template staat de functie bijv. op cel X65, de template bevat 80 regels.
Deze template wordt ingelezen en gevuld. Indien er meerdere typen verwerkt moeten wordt de template nogmaals ingelezen en eronder geplakt. De functie komt dan terecht in X145. In de sheet zitten ook nog meer verwijzingen naar andere sheets voor basisgegevens. Het is een vrij complex geheel wat al een aantal jaren in gebruik is en steeds uitgebouwd is.
Dit getal wordt nu steeds handmatig berekend en in de betreffende cel ingevuld. Ik wil dit echter met een functie oplossen.

 

[Wigi]

Jan

Ik denk niet dat ik al veel keer een template gebruikt heb, dus ik weet het niet zeker. Maar als mijn functie nu eens ook in de template zit, en je kopieert alles, dan komt het toch goed? Niet?

Begrijp me niet verkeerd, ik wil gerust die functie van jou eens bekijken en proberen aanpassen. Maar als het simpeler kan zou ik dat toch verkiezen.

 

[Jan van Asseldonk]

Hoe simpeler hoe liever.

De optie om de formule gewoon op te nemen in de template heb ik al gebruikt. Probleem is dat bij alle reeds opgeslagen bestanden (honderden) de ingevulde waarde vervangen zou moeten worden door de formule. Hiervoor is wel een conversiesheet te maken die daar een weekendje mee zoet is maar daarmee heb ik eigenlijk het probleem nog niet opgelost.
De gebruiker heeft de mogelijkheid om de formule te overschrijven met een voor die gevallen geldende of door een instantie opgelegde standaardwaarde. Als er daarna gegevens wijzigen kan hij de functie opnieuw invoeren en dan de nieuwe berekende waarde te zien krijgen.
In werkelijkheid is de berekende waarde afhankelijk van de waarden in een 10-tal andere cellen waarmee binnen de functie nog gerekend moet worden.

Een eigen gedefinieerde functie leek me daarvoor de beste oplossing maar is niet heilig.

 

[Wigi]

Ik ben nog niet echt overtuigd ;-)

maar hier mijn aanpassing:

Function EigenTest(rControl As Range) As Double

Dim r As Range

Set r = Application.Caller.Offset(, -1)

Dim getal1 As Integer
Dim getal2 As Double
Dim getal3 As Integer
Dim getal4 As Integer

getal1 = r.Offset(-3).Value
getal2 = r.Offset(-2).Value
getal3 = r.Offset(-1).Value
getal4 = r.Offset(1).Value

If UCase(rControl.Value) = "JA" Then
    EigenTest = (getal1 + getal2) / getal3
Else
    EigenTest = getal4
End If

End Function

Wigi

 

[Jan van Asseldonk]

Wigi, :thumb:

probleem is opgelost.
De oplossing was het gebruiken van Application.caller i.p.v. ActiveCell.

Weer een stukje wijzer geworden.