[Excel VBA] Eigen functie binnen een knop

[waztone]

Ik ben bezig met een excel bestand om facturen te genereren.
In de VBA code die ik gebruik, moet ik vaker gegevens van de ene cel naar de andere cel kopiëren met de code:

code:

Range("BronCel").Copy
    Sheets("Factuur").Range("DoelCel").PasteSpecial Paste:= xlValues

Dit wordt uitgevoerd zodra er op een knop wordt gedrukt en dat werkt ook dusver.
Nu dacht ik dat ik de code iets kon inkorten door een eigen functie te maken, waarbij ik dus alleen de variabelen (BronCel en DoelCel) hoef aan te passen. Maar ik krijg het niet voor elkaar. Als ik in andere fora zoek, zie ik alleen voorbeelden van hoe je een User Defined Function kunt gebruiken IN een cel, niet in een ander stuk code.

Ik dacht zelf aan iets van:

code:

function CopyPaste(From As String, To As String)
     Range("&From&").Copy
     sName.Range("&To&").PasteSpecial Paste:= xlValues
end function

En dan in het stuk code (van de knop) roep ik de functie aan als volgt;

code:

sName = Sheets("Factuur")
CopyPaste(BronCel, DoelCel)

BronCel en DoelCel zijn in dit voorbeeld namen die ik aan de betreffende cellen heb toegewezen.
Waar zit mijn denkfout?

[Daos]

Dit ziet er nogal vreemd uit. Wat probeer je daar precies? & is om strings aan elkaar te plakken, maar jij zet het in een string-constante

code:

Range("&From&")

Volgens mij moet je gewoon dit doen. Oftewel je variabele From meegeven aan de functie Range. Zelfde voor de To.

code:

Range(From)

[dix-neuf]

Kijk eens of dit iets is om onder je knop vast te leggen.
Daarmee bereik je wat je wil, denk ik.
Aanname is dat bron- en doelcel in hetzelfde werkblad staan.

code:

Sub Kopieer()
Dim B, D
B = InputBox("Broncel = ", "Broncel")
D = InputBox("Doelcel = ", "Doelcel")
Range(D) = Range(B).Value
End Sub

[ Voor 10% gewijzigd door dix-neuf op 11-10-2022 09:34 ]

[dix-neuf]

Als het blad 'Factuur' een ander blad is als waarin de broncel staat, gebruik dan in plaats van regel 5:

code:

Range(B).Copy Sheets("Factuur").Range(D)

[ Voor 3% gewijzigd door dix-neuf op 11-10-2022 09:49 ]

[waztone]

Heb het iets anders gedaan, maar het is gelukt.
Ik ging abusievelijk uit van een function, maar ik had een Sub nodig.

Dus in mijn werkblad heb ik twee sheets.
Op sheet 1 staan mijn broncellen die ik een naamverwijzing heb gegeven. In dit voorbeeld noem ik de cel A2 "Bron" en de cel A3 "Bron2".
Dan heb ik op deze sheet een knop aangemaakt genaamd "CopyPaste_btn".
Dan heb ik een sheet genaamd "Factuur". Op deze sheet heb ik cel A2 "Doel" genoemd en cel A3 "Doel2".

Daarna heb ik de VBA editor geopend en een nieuwe module toegevoegd. Daar heb ik de volgende code aan toegevoegd:

code:

Option Explicit

Dim ws As Worksheet
Dim Bron, Doel As Range

Public Sub CopyPaste(Bron, Doel)
    Bron.Copy
    Doel.PasteSpecial Paste:=xlValues
End Sub

Daarna heb ik de volgende code onder de knop geplakt.

code:

Private Sub CopyPaste_btn_Click()
    Set ws = ThisWorkbook.Worksheets("Factuur")
    Set Bron = Range("Bron")
    Set Doel = ws.Range("Doel")
    
    Call CopyPaste(Bron, Doel)
    
    Set Bron = Range("Bron2")
    Set Doel = ws.Range("Doel2")
    
    Call CopyPaste(Bron, Doel)
    
End Sub

Nu is mijn code een stuk leesbaarder, dus dat is goed.
Nog mooier zou wezen als ik niet elke keer als ik de Bron en Doel wou veranderen, Set moest gebruiken, maar dat ik gewoon iets van Call CopyPaste("Bron3", "Doel3") kon gebruiken. Maar volgens mij kan dat niet toch?

[dix-neuf]

Zijn Bron Bron2, Doel, en Doel2 vaste cellen die je een naam hebt gegeven?

Zonder aanpassing zal het ook wel werken, maar in deze regel:

code:

Dim Bron, Doel As Range

is Bron geen Range maar een Variant.
Correct is:

code:

Dim Bron as Range, Doel As Range

[waztone]

Ja dat zijn vaste cellen. Bedankt voor de tip, ga ik aanpassen. Ik dacht dat je op dezelfde lijn verschillende variabelen kon declareren.

[dix-neuf]

Maak van Bron Bron1 en van Doel Doel1
Als die namen gelden voor de gehele werkmap, hoef je in de macro geen bladnaam te specificeren
Je kunt dan dit gebruiken (1 macro):

code:

Sub kopieer()
Dim Bron1 As Range, Bron2 As Range, Doel1 As Range, Doel2 As Range
Set Bron1 = Range("Bron1"): Set Doel1 = Range("Doel1")
Set Bron2 = Range("Bron2"): Set Doel2 = Range("Doel2")
Bron1.Copy Doel1
Bron2.Copy Doel2
End Sub

Staan er in Bron1 en Bron2 formules, gebruik dan dit:

code:

Sub kopieer()
Dim Bron1 As Range, Bron2 As Range, Doel1 As Range, Doel2 As Range
Set Bron1 = Range("Bron1"): Set Doel1 = Range("Doel1")
Set Bron2 = Range("Bron2"): Set Doel2 = Range("Doel2")
Bron1.Copy
Doel1.PasteSpecial Paste:=xlValues
Bron2.Copy
Doel2.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End Sub

[Lustucru]

waztone schreef op maandag 10 oktober 2022 @ 19:52:
[...]Nu dacht ik dat ik de code iets kon inkorten door een eigen functie te maken, waarbij ik dus alleen de variabelen (BronCel en DoelCel) hoef aan te passen.

Echt korter wordt het allemaal niet. als het puur gaat om de waarde van een cel in een andere cel weg te schrijven krijg je het niet korter dan dit:

code:

Private Sub CopyPaste_btn_Click()
    Range("celA") = Range("celB")
    Range("celC") = Range("celD")
    'etc
End Sub

celA etc. zijn in de werkmap gedefinieerde namen/

waztone schreef op maandag 10 oktober 2022 @ 19:52:
Als ik in andere fora zoek, zie ik alleen voorbeelden van hoe je een User Defined Function kunt gebruiken IN een cel, niet in een ander stuk code.

Een UDF is een publieke functie die je kunt gebruiken als formule-functie in een cel. Maar er is geen enkel bezwaar om een functie aan te roepen vanuit een ander stuk code.

waztone schreef op dinsdag 11 oktober 2022 @ 15:51:
Heb het iets anders gedaan, maar het is gelukt.
Ik ging abusievelijk uit van een function, maar ik had een Sub nodig.

Principieel is er weinig verschil tussen een functie en een subroutine, behalve dan dat een functie een korte schrijfwijze toestaat om één waarde te verwerken. Als je niets hoeft te doen met een retourwaarde is het wellicht mooier om Sub - End Sub te schrijven, maar Function-End function werkt net zo goed. In veel programmeertalen zie je dat verschil daarom ook niet meer terug in de naamgeving van subroutines.