Hallo zusammen,
was für nette Ideen hättet Ihr als Beispiele, um anderen Menschen das Thema selbstdefinierte Excel-Funktionen beizubringen und sich somit an VBA anzunähern?
So was wie Sinus im Gradmaß interessiert ja nur Schüler oder vielleicht Mathematiker.
Gruß JK
Hallo Joku,
klingt interessant. Was soll denn die Zielgruppe sein? Was ist bei denen bereits vorhanden? Was sind ihre Anwendungsfälle/Einsatzgebiete? Alter? Hintergrund?
Gruß, Manfred
selbstdefinierte Excel-Funktionen
und sich somit an VBA anzunähern?
Hallo JK
Ich hätte einige, aber ob sie so anregend sind?
Grüsse
Niclaus
Function rund(zahl) 'Kaufmännische Fünferrundung
’ VRUNDEN ergibt gleiches Resultat - aber bei negativen Zahlen,
’ muss auch das zweite Argument negativ sein.
r = 0.05
rund = Application.Round(zahl / r, 0) * r
End Function
Function Kodierung(ByVal Anzahl As Integer) As String
’ zB für passwörter
’ die Zahl in der Klammer = Länge des auszugebenden Strings
Dim Anz As Integer
Const strWahl As String = „0123456789%?.-ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijk_usw“
For Anz = 1 To Anzahl
Kodierung = Kodierung & Mid(strWahl, Int(Rnd() * 36) + 1, 1)
Next Anz
End Function
Function Quersumme(zahl As Long) As Long
’ If Fix(Zahl) Zahl Then Zahl = 0
’ Nicht-ganzzahlige Zahlen werden nicht berechnet
’ Klappt aber nicht - wohl aber in einem sub-makro
zahl = Fix(Abs(zahl))
While zahl > 0
Quersumme = Quersumme + zahl Mod 10
zahl = zahl \ 10
’ \ = Division zweier Zahlen; gibt ein ganzzahliges Ergebnis zurück
Wend
End Function
Function DINKW(datum As Date) As Integer
’ DIN-Kalenderwoche: Erste Woche gilt als erste Wo. wenn der 1. Januar
’ mindestens ein Mo, Di, Mi oder Do ist. Mo gilt als erster Wochentag.
Dim T As Long
T = DateSerial(Year(datum + (8 - Weekday(datum)) Mod 7 - 3), 1, 1)
DINKW = ((datum - T - 3 + (Weekday(T) + 1) Mod 7)) \ 7 + 1
End Function
’
’ Das gleiche als xls-formel:
’ =(ZAHL-WOCHENTAG(ZAHL;3)+3-DATUM(JAHR(ZAHL-WOCHENTAG(ZAHL;3)+3);1;4) _
+WOCHENTAG(DATUM(JAHR(ZAHL-WOCHENTAG(ZAHL;3)+3);1;4);3)-3)/7+1
’
'ODER: =KÜRZEN((A1-DATUM(JAHR(A1+3-REST(A1-2;7));1;REST(A1-2;7)-9))/7)
Function Schaltjahr(zahl)
’ zahl = Jahreszahl oder Datum
’ Schaltjahr = J / N
If IsDate(zahl) Then zahl = Year(zahl)
If Not IsNumeric(zahl) Then zahl = 0
zahl = Abs(zahl)
sj = zahl Mod 4
If zahl Mod 100 = 0 And zahl Mod 400 0 Then sj = 1
If zahl = 0 Then sj = 1
If sj = 0 Then Schaltjahr = „J“
If sj > 0 Then Schaltjahr = „N“
‚‘’’ Andere Variante: SJ = 1 Nicht-SJ = 0
‚‘'If sj = 0 Then Schaltjahr = 1
‚‘'If sj > 0 Then Schaltjahr = 0
End Function
Hallo Joku,
ergänzend zu meinen Fragen von gestern: Mach irgendwas, was einen Bezug zu denen hat, denen Du es beibringen willst. Zeige ihnen den Nutzen. Dann lernen sie auch was.
Grundsätzlich würde ich allerdings Makros eher vermeiden, weil sie im Zweifelsfall mal irgendwo deaktiviert sind, und dann geht dem nicht… Wenn ich also was mit einer Formel hinkriege, dann nehme ich die.
Was mir gerade einfällt (und was ich mir demnächst bauen werde), ist das Worksheet.UsedRange. Denn irgendwie finde ich dazu nichts brauchbares als Excel-Funktionen.
Man bekommt zwar mit ANZAHLLEEREZELLEN in einer Matrixformel was raus, doch das geht nur bis Excel 2003. Unter Excel 2007 wird durch die enorme Ausweitung eines Tabellenblattes bis zur Zelle $XFD$1048576 die maximale Formellänge überschritten. Und dann rechnet es nicht mehr…
Ansonsten würde ich eher alltägliche Aufgaben, die Dein Adressatenkreis erledigen muss, per Makro durchlaufen lassen. Dazu kann man schnell den Makro-Rekorder hernehmen. Das ist das Rohskelett. Und dann erklärt man, was Schleifen sind, und baut das aufgezeichnete Makro in eine entsprechende Lösung aus, die über alle Zeilen, Spalten, Tabellenblätter das gewünschte macht.
Beispiele: Datensammlung, wird per Copy&:stuck_out_tongue_winking_eye:aste oder manuelles Eintragen von Werten ergänzt. => Per Makro bestimmte Berechnungsformeln automatisch ergänzen, die man sonst per Ausfüllen manuell machen müsste.
Wenn diese auch Datenquelle für Pivot-Tabellen sind: Alle Pivot-Tabellen aktualisieren. (Mit einer entsprechenden Namensvergabe sind die Datenquellen der Pivots konstant, dennoch muss ein Refresh aufgerufen werden.)
Ich habe als weiteres Beispiel eine To-Do-Liste. Da gibt’s eine laufende Nummer, ein Erfassungsdatum, … Aber warum muss ich die eintragen? Also trage ich nur gleich mein To-Do ein, und das Makro ergänzt automatisch die laufende Nummer und das aktuelle Datum.
Orientiere Dich, wie gesagt, am besten an den konkreten Anforderungen Deiner Adressaten, dann sind sie auch um so motivierter, weil sie ihren direkten Nutzen erkennen können und eben nicht irgendwelche abgehobenen mathematischen Geschichten treiben, deren Sinn sie nicht verstehen.
Gruß, Manfred
Danke für die vielen Ideen! 
An „Runden in beliebigen Schritten“ war mr gestern auch schon eingefallen.
Gruß JK
Hallo Manfred,
Was mir gerade einfällt (und was ich mir demnächst bauen
werde), ist das Worksheet.UsedRange. Denn irgendwie finde ich
dazu nichts brauchbares als Excel-Funktionen.
ja, mir ist sehr unklar warum Excel da so Blödsinn macht.
Über Namensvergabe, dann Datei.Zuordnen(9) kriege ich die erste benutzte Zeile herasu.
Über 10, 11, 12 dann letzte benutzte Zeile, erste benutzte Spalte, letzte benutzte Spalte.
Daraus kann ich mir dann UsedRange zusammensetzen, klappt auch.
Leider, deshalb unbrauchbar, „merkt“ sich Excel bestimmte Zellen als zu UsedRange zugehörig.
D.h., man hat in A1 etwas stehen, dann löscht man den Inhalt oder sogar die ganze Zeile 1, bringt nix, für Excel gehört A1 auf ewig zum benutzten bereich.
Auch wenn die wahren Daten nur in C5:F38 stehen, und Zeile 1 gelöscht wurde, man kriegt als Ergebnis des UsedRanges: A1:F38 
Why?
Auch die letzte zeile ist falsch, ich denke alle Ergebnisse sind falsch
Ein Beispiel dafür folgt unten.
Man bekommt zwar mit ANZAHLLEEREZELLEN in einer Matrixformel
was raus, doch das geht nur bis Excel 2003. Unter Excel 2007
wird durch die enorme Ausweitung eines Tabellenblattes bis zur
Zelle $XFD$1048576 die maximale Formellänge überschritten. Und
dann rechnet es nicht mehr…
Sorry, verstehe ich grad nicht. Wieso wird die Formellänge überschritten wenn ich AnzahlleereZellen() benutze?
Orientiere Dich, wie gesagt, am besten an den konkreten
Anforderungen Deiner Adressaten, dann sind sie auch um so
motivierter, weil sie ihren direkten Nutzen erkennen können
und eben nicht irgendwelche abgehobenen mathematischen
Geschichten treiben, deren Sinn sie nicht verstehen.
Genau so ist es.
Gruß
Reinhard
Tabellenblatt: [Mappe1]!Tabelle1
│ A │ B │ C │ D │ E │
───┼───┼───┼────────────┼───┼───┤
1 │ │ │ │ │ │
───┼───┼───┼────────────┼───┼───┤
2 │ │ │ │ v │ │
───┼───┼───┼────────────┼───┼───┤
3 │ │ │ │ │ │
───┼───┼───┼────────────┼───┼───┤
4 │ │ │ │ │ v │
───┼───┼───┼────────────┼───┼───┤
5 │ │ s │ │ │ │
───┼───┼───┼────────────┼───┼───┤
6 │ │ │ │ │ v │
───┼───┼───┼────────────┼───┼───┤
7 │ │ │ │ │ │
───┼───┼───┼────────────┼───┼───┤
8 │ │ │ $A$1:blush:E$13 │ │ │
───┼───┼───┼────────────┼───┼───┤
9 │ │ │ │ v │ │
───┼───┼───┼────────────┼───┼───┤
10 │ │ │ │ │ │
───┼───┼───┼────────────┼───┼───┤
11 │ │ │ a │ │ │
───┼───┼───┼────────────┼───┼───┤
12 │ │ │ │ │ │
───┼───┼───┼────────────┼───┼───┤
13 │ │ │ │ │ │
───┴───┴───┴────────────┴───┴───┘
Benutzte Formeln:
C8 : =UR
Festgelegte Namen:
ES: =DATEI.ZUORDNEN(11), unbenutzt in Selektion.
EZ: =DATEI.ZUORDNEN(9), unbenutzt in Selektion.
LS: =DATEI.ZUORDNEN(12), unbenutzt in Selektion.
LZ: =DATEI.ZUORDNEN(10), unbenutzt in Selektion.
UR: =ADRESSE(EZ;ES)&":"&ADRESSE(LZ;LS)
A1:E13
haben das Zahlenformat: Standard
Tabellendarstellung erreicht mit dem Code in FAQ:2363
Hallo Reinhard,
Sorry, verstehe ich grad nicht. Wieso wird die Formellänge
überschritten wenn ich AnzahlleereZellen() benutze?
Hmmm. Vermutlich, weil ich mich irgendwo bei der Formel verhaspelt habe; und die teilweise Auswertung hat mir dann gesagt, dass die maximale Formellänge von 8xxx Zeichen überschritten wurde.
Habe zum Beispiel die letzte benutzte Zeile rausbekommen mit
{=MAX(WENN(ANZAHLLEEREZELLEN(BEREICH.VERSCHIEBEN(1:1;ZEILE(5:1048575):wink:)
und die letzte Spalte mit
{=MAX(WENN(ANZAHLLEEREZELLEN(BEREICH.VERSCHIEBEN(B:B;;SPALTE(A:XFB):wink:)
Und, wie gesagt, irgend ein Problem hatte ich. Irgendwo ein Tipfehler oder sonstwas... Aber jetzt geht dem. Weiß zwar nicht warum. Aber danke Deiner Nachfrage. Hab's deswegen noch mal versucht, und: Jubel bricht ins Land!
Gruß,
Manfred
Hallo Manfred,
Was soll denn die Zielgruppe sein?
Teilnehmer eines Volkshochschulkurses für Ex-Fortgeschrittene
Es soll halt auch mal in VBA reingeguckt werden.
Was ist bei denen bereits vorhanden?
Ex-Grundkenntnisse
Was sind ihre Anwendungsfälle/Einsatzgebiete? Alter? Hintergrund?
Keine Ahnung
Wahrscheinlich Kaufleuts.
Ich werde ihne auch mal zeigen, wie man ein Makro aufzeichnet und ändert; z. B. mit meiner „Lieblings-Rahmensorte“
oder alternativen Drucker auswählen, drucken, vorigen Drucker wieder auswählern.
Gruß, JoKu
Mach irgendwas, was
einen Bezug zu denen hat, denen Du es beibringen willst. Zeige
ihnen den Nutzen. Dann lernen sie auch was.
Stimmt. Deshalb mache ich mir ja vorher mal Gedanken und werde sie auch in der ersten Stunde fragen wo der Schuh drückt.
Grundsätzlich würde ich allerdings Makros eher vermeiden, weil
sie im Zweifelsfall mal irgendwo deaktiviert sind, und dann
geht dem nicht… Wenn ich also was mit einer Formel
hinkriege, dann nehme ich die.
Das sehe ich genauso und werde ich auch so sagen.
Aber gelegentlich macht man in Excel Sachen immer wieder gleich oder umständlich und könnte zumindest für sich selbst die Sache mit einem Macro oder einer benutzerdefinierten Funktion vereinfachen.
Ansonsten würde ich eher alltägliche Aufgaben, die Dein
Adressatenkreis erledigen muss, per Makro durchlaufen lassen.
Dazu kann man schnell den Makro-Rekorder hernehmen. Das ist
das Rohskelett. Und dann erklärt man, was Schleifen sind, und
baut das aufgezeichnete Makro in eine entsprechende Lösung
aus, die über alle Zeilen, Spalten, Tabellenblätter das
gewünschte macht.
Ja, so etwa habe ich mir das auch gedacht.
Danke für Deine weiteren Ideen!
Orientiere Dich, wie gesagt, am besten an den konkreten
Anforderungen Deiner Adressaten, dann sind sie auch um so
motivierter, weil sie ihren direkten Nutzen erkennen können
und eben nicht irgendwelche abgehobenen mathematischen
Geschichten treiben, deren Sinn sie nicht verstehen.
Ja, sehe ich auch so.
Wobei ein Kind, das den Eltern etwas vorjammert, weil es mit Excel was Bestimmtes darstellen soll, auch „motivierend“ sein kann. 
Gruß, Jochen
Letzte Zeile, Letzte Spalte ermitteln Formel
Hallo Manfred,
habe mal den Betreff für das Archiv geändert.
Ermittlung der letzten benutzten Zeile:
bis vor Excel2007:
=MAX(WENN(ANZAHLLEEREZELLEN(BEREICH.VERSCHIEBEN(1:1;ZEILE(5:65535):wink:)
(Matrix-) Formeln ohne die Spitzklammern rechts und links eingeben. Eingeben mit Shift+Strg+Enter, Excel setzt dann die Spitzklammern selbst.
Gruß
Reinhard
was für nette Ideen hättet Ihr als Beispiele, um anderen
Menschen das Thema selbstdefinierte Excel-Funktionen
beizubringen und sich somit an VBA anzunähern?So was wie Sinus im Gradmaß interessiert ja nur Schüler oder
vielleicht Mathematiker.
Hallo Joku,
welche technischen Möglichkeiten hast du da in deinem VHS-Kurs?
Du hast einen PC, alle anderen auch einen. Ist da ein Beamer o.ä., der deinen Bildschirminhalt für alle sichtbar auf die Wand oder eine Leinwand wirft?
Gruß
Reinhard
Du hast einen PC, alle anderen auch einen. Ist da ein Beamer
o.ä., der deinen Bildschirminhalt für alle sichtbar auf die
Wand oder eine Leinwand wirft?
So ist es.
Gruß
JK
Du hast einen PC, alle anderen auch einen. Ist da ein Beamer
o.ä., der deinen Bildschirminhalt für alle sichtbar auf die
Wand oder eine Leinwand wirft?
So ist es.
Hallo Joku,
nur mal so kurz angedacht, ich habe so Schulungen noch nie gemacht.
Ih würde paar einfache UDFs vorbereiten, z.B. Verdoppeln(A1), EuroInDollar(A1) usw.
Nix dolles, erstmal dadurch checken ob auch alle wissen wo sie so einen Funktionscode in welches Modul einbauen sollen und wie man das in Excel einbindet.
Wenn das bei allen sitzt, würde ich reihum fragen welche Funktionen denn da jeder in Excel vermisst und gern als UDF schreiben/haben würde.
Dann werden dann 1-3 Funktionen für die die meisten sind ausgewählt und WIR entwickeln diese Funktion gemeinsam, indem ich meine Eingaben per Beamer an die Wand werfe.
Diese Eingaben beruhen auf Zurufen, gesteuert durch meine Erläuterung/Hilfe, warum das jetzt nicht passt und wir was anderes brauchen usw.
*seufz*, schwierig zu beschreiben wie ich das meine
Und, das mit eine Funktion zu sagen die man bräuchte, kann ja auch am nächsten Unterichtstag erfolgen.
Die von dir angesprochene Umsetzung von aufgezeichnetem Code in guten Code ist sehr wichtig.
Da kommt man automatisch zu genauem referenzieren, unbedingtes Vermeiden von Select usw.
Aber da ist es reht leicht daß du dir Makros aufzeichnest, aufzeichnen läßt, die bei der Aufzeichnung problemlos klappen, beim nächsten Aufruf aber nicht mehr weil die Aufrufsituation eine Winzigkeit anders ist.
Und auch Feinheiten wie das z.B. die Benutzung von ActiveCell problematisch sein kann, also zu einem Fehler führt, wenn z.B. grad ein Diagramm auf dem Blatt selektiert ist oder das aktive Blatt ein Diagrammblatt ist.
Aber insgesamt gesehen gibt es da m.E. viele Ansätze so einen Kurs zu gestalten. Ich finde es wichtig, daß du nicht irgendwas runterleierst sondern je nach Kurszusammensetzung flexibel auf unterschiedliche leistungen regierst.
Und zu deiner Ausgangsfrage, Beispiele für schöne UDFs habe ich nicht. Ich glaub kaum daß da kaufleute an einer selbstgebastelten UDF interessiert sind die z.B. die Funktion Split() bzw. InstrRev() ersetzt. Diese UDFs habe ich mir mal gebastelt weils die Fktn. in XL97 nicht gibt, für den Aufbau einer UDF, Argument/parameter-Übergabe usw. schon lehrreich, aber interessierter werden die „Schüler“ schon sein, wenn es eine UDF ist die sie auch brauchen/wollen.
Gruß
Reinhard
Diese Eingaben beruhen auf Zurufen, gesteuert durch meine
Erläuterung/Hilfe, warum das jetzt nicht passt und wir was
anderes brauchen usw.
*seufz*, schwierig zu beschreiben wie ich das meine
Hab Dich verstanden.
Ich will es auch möglichst „publikumsgesteuert“ veranstalten.
Mal schaun, was dann so an Wünschen kommt. …
Ggf. kann ich ihnen ja auch (da der Kurs nicht sehr lange läuft) einen
der Wünsche per Homepage oder Mail nachreichen.
Die von dir angesprochene Umsetzung von aufgezeichnetem Code
in guten Code ist sehr wichtig.
Ja, da habe ich schon selbst so meine Erfahrungen gemacht. ;-(
Aber da ist es recht leicht daß du dir Makros aufzeichnest,
aufzeichnen läßt, die bei der Aufzeichnung problemlos klappen,
beim nächsten Aufruf aber nicht mehr weil die Aufrufsituation
eine Winzigkeit anders ist.
So ist es.
Gruß
Jochen
Hallo,
was für nette Ideen hättet Ihr als Beispiele, um anderen
Menschen das Thema selbstdefinierte Excel-Funktionen
beizubringen und sich somit an VBA anzunähern?
gib den Leuten die Aufgabe eine Formel zu entwickeln, die eigentlich eine ganz simple Berechnung durchführen soll, allerdings nur unter ganz bestimmten Bedingungen.
Excel-Freaks ohne VBA-Kenntnisse werden Dir möglicherweise eine Formel mit sovielen WENN’s „zusammenschachteln“, die zwar funktioniert, aber kein Mensch später mehr nachvollziehen kann.
Du zeigst ihnen dann später, wie man das Problem in VBA z.B. mit Select Case lösen kann, und wie man aus dem Code aus „Formel“ machen kann.
Glaub mir die Leute werden erstaunt und dankbar sein, wie sehr man sich das Leben vereinfachen kann. Aus eigener Erfahrung kenne ich Formel-Konstruktionen, die bei einem 20 Zoll-Bildschirm in fünf Zeilen gebrochen werden. Excel macht die Klammern und verwendeten Zellbereiche in den Funktionen dann auch nicht mehr farbig. Selbst wenn man das selbst mal programmiert hat, blickt man nicht mehr durch. Mit einer Public Function geht das alles wesentlich entspannter. Außerdem kann ich in VBA noch Kommentare hinterlegen, um der Nachwelt zu sagen, was dort überhaupt geschieht bzw. warum so und nicht so gerechnet wird.
MfG
Stephan
*seufz*, schwierig zu beschreiben wie ich das meine
Hab Dich verstanden.
Ich will es auch möglichst „publikumsgesteuert“ veranstalten.
Mal schaun, was dann so an Wünschen kommt. …
Ggf. kann ich ihnen ja auch (da der Kurs nicht sehr lange
läuft) einen
der Wünsche per Homepage oder Mail nachreichen.
Hallo Jochen,
aus deiner Antwort sehe ich daß du mich genau verstanden hast.
Beim Überlegen meiner Antwort, also mit dem Beschäfigen damit, was würde ich versuchen „ihnen“ beizubringen kam ich sehr schnell vom hunnersten ins tausenste.
Es gibt also so viele verschiedene Dinge in Vba, die alle irgendwie wichtig sind für zukünfige Eigenprogrammierungen der VHSler das sprengt den Zeitrahmen.
Wenn es also 100 wichtige Dinge in Vba gibt, sogenannte basics, aber du hast realistisch nur Zeit für 30, muß man halt leider Prioritäten setzen
Aber besser, viel besser, man hat 30 richtig beigebracht bekommen als 100 und die nur so daß mman in 2 Wochen nix mehr weiß.
Weniger ist mehr. Ich habe einmalig im Leben einen Tanzkurs gemacht, 10 Doppelstunden, laut Buchung war der für vollkommene Anfänger, naja, ich und noch einer mit 46er Schuhgröße, die gefährlich für zarte Damenfüßchen ist , waren wahre Anfänger, alle anderen konnten tanzen.
Dann haben wir an den 10 Tagen 10 Tänze „gelernt“. Erfolg bei mir, Null, ich konnte danach keinen einen.
Gruß
Reinhard
Hallo
Wenn es also 100 wichtige Dinge in Vba gibt, sogenannte
basics, aber du hast realistisch nur Zeit für 30, muß man halt
leider Prioritäten setzen
Aber besser, viel besser, man hat 30 richtig beigebracht
bekommen als 100 und die nur so daß mman in 2 Wochen nix mehr weiß.
Wenn Sie sich nach N Monaten erinnern: „Da war doch was Praktisches, das mir jetzt das Leben erleichtern könnte“ und dann die richtigen Suchwörter bei google eingeben, ist schon mal viel erreicht.
Wenn sie sich dann selbst ein Makro aufzeichnen oder ein Funktiönchen basteln, wäre es richtig gut.
Gruß
JK