Software für Sie.

Bei der Eingabe von Zeitwerten wird von Excel sofort das entsprechende Format zugewiesen. Dabei geht Excel wie folgt vor:

Im Standard-System (1900-Datumssystem) wird dem Zeitpunkt 01.01.1900 der Zeitwert 1 zugewiesen. Für jeden weiteren Tag wird um 1 aufaddiert. So hat der 2. Januar 1900 den Wert 2.

Stunden/Minutenangaben folgen hinter dem Komma, eine Stunde hat also den Wert 1 Tag geteilt durch 24 Stunden = 0,0416666. Der Zeitpunkt 01.01.1900 01:00:00 hat z.Bsp. den Wert 1,04166666666667. Eine Minute wäre entsprechend 1 Tag/24 Stunden/60 Minuten.

Wichtig: trotz Eingabe eines Datumwertes (z. B. 01.01.1999) wandelt Excel dies sofort in eine Zahl (36161) um und es erscheint nur durch die Formatzuweisung als Datum. Der Unterschied zwischen Inhalt bzw. Formel der Zelle (Value/Formula) und Aussehen der Zelle (Text) ist hier also besonders wichtig - vor allem auch, wenn es um Zeiten und die berüchtigten #-Zeichen geht. Letztere werden nur angezeigt, während in der Zelle in diesem Fall der korrekte Inhalt steht.

Hier ist eine kleine Anleitung zum Probieren.
Nimm Dir am besten eine neue Mappe und vollziehe die folgenden Schritte nach.

• Wechsle mit Alt + F11 in den VBA-Editor und klicke dort im Projektexplorer (normalerweise oben links) in Deine Mappe.
• Wähle jetzt Einfügen - Userform. Zeichne in diese Userform oben eine Textbox (Textfeld). Die Eingaben in dieses Textfeld sollen dann sofort in die Zelle A2 der aktiven Tabelle übernommen werden.
• Klicke doppelt auf die Textbox. Daraufhin erscheint das Klassenmodul der Userform, in das die Prozeduren eingetragen werden, die bei der Arbeit mit den Userform-Elementen ausgeführt werden sollen.
• Jetzt siehst Du auch schon zwei Zeilen, die Du so vervollständigst (Eingaben in die Textbox werden damit sofort in A2 der aktiven Tabelle übernommen):

Private Sub TextBox1_Change() range("A2") = TextBox1.Text End Sub

• Wechsle nun über das Menü Fenster oder mit der Tastenkombination Strg + F6 wieder zur Userform. Zeichne dort eine weitere Textbox und eine Schaltfläche (Ã?bernehmen) unter dieser Textbox.
• Doppelklicke jetzt auf die Schaltfläche und Du landest wieder im Klassenmodul.
• Es sind zwei neue Zeilen hinzugekommen, die Du so ergänzt:

Private Sub CommandButton1_Click() Range("A3") = TextBox2.Text End Sub

Der zweiten Textbox ist diesmal keine Prozedur zugeordnet, also passiert beim Eintragen erstmal nichts. Der Inhalt dieser Textbox wird erst beim Klick auf die Schaltfläche in A3 übernommen.

• Eigentlich ist die Userform jetzt schon zur Arbeit bereit. Was wäre aber ein Dialogfeld ohne SchlieÃ?en-Schaltfläche? Also basteln wir schnell noch eine. Deshalb - wieder zurück zur Userform (Fenster oder Strg + F6).
• Male nun unten in die Userform eine Schaltfläche und doppelklicke darauf.
• Die Prozedur müÃ?te diesmal so vervollständigt werden, sie ist dafür zuständig, daÃ? die Userform geschlossen wird:

Private Sub CommandButton2_Click() Unload Me End Sub

• Die Beschriftungen der Schaltflächen kannst Du noch ändern, indem Du wieder zur Userform zurückkehrst und zweimal auf die Schaltfläche klickst (kein Doppelklick!).
• So, jetzt müssen wir noch für den Aufruf sorgen, denn ein Anwender soll ja nicht jedesmal in den Editor wechseln, um die Userform aufzurufen. Die Prozedur dafür schreibt man normalerweise in ein allgemeines Modul - also wähle Einfügen - Modul.
• In das leere Modul schreibst Du:

Sub Aufruf() UserForm1.Show End Sub

• Wechsle nun wieder zu Excel und zeichne aus der Formular-Symbolleiste eine Schaltfläche auf das Blatt (moderner wäre eine Schaltfläche aus der Steuerelemente-Toolbox, aber wir wollen es ja am Anfang nicht so schwer machen). Jetzt öffnet sich ein Dialogfeld, in dem Du der Schaltfläche ein Makro zuweisen kannst - also Doppelklick auf Aufruf - denn so hatten wir das Makro ja benannt.
• Das wars. Jetzt kannst Du auf die Schaltfläche klicken, die Userform erscheint und Du wirst sehen, daÃ? Eintragungen in die erste Textbox sofort übernommen werden, Eintragungen in die zweite Textbox erst beim Klick auf die Schaltfläche.

Zusammenfassung:

In Deiner Mappe hast Du jetzt:

• ein Modul, auf dem die Prozedur zum Aufruf der Userform enthalten ist,
• eine Userform mit zwei Textboxen und zwei Schaltflächen (Commandbuttons),
• das Klassenmodul der Userform, auf dem die Routinen enthalten sind, die bei der Arbeit mit den Userform-Elementen aufgerufen werden.

Und nochmal der Tip: Im Editor kann man ganz einfach mit Strg + F6 zwischen den einzelnen Komponenten wechseln. Mit Strg + Tab ist es zwar auch möglich, bereitet aber Probleme, wenn Multipage-Elemente enthalten sind.

Der Code zum Kopieren:

Private Sub TextBox1_Change() Range("A2") = TextBox1.Text End Sub Private Sub CommandButton1_Click() Range("A3") = TextBox2.Text End Sub Private Sub CommandButton2_Click() Unload Me End Sub Sub Aufruf() UserForm1.Show End Sub

Eigentlich gar nicht.

Man kann aber mit einer Formel Bezug auf eine geschlossene Mappe nehmen; deren Ergebnis erscheint dann. Dies kann man dann mit VBA verwenden. Also kann man auch mit VBA die Formel eintragen und dann das Ergebnis abgreifen. Die Formel kann zum Schluss wieder gelöscht wreden.

Beispiel:
Im Ordner C:\Eigene Dateien befindet sich die Datei DBAdressen.xlsx. Aus dieser Datei sollen vom Blatt Allgemein die Daten aus A1 bis A25 in eine Listbox eingelesen werden, ohne diese Datei zu öffnen.

Const strBezug As String = "='C:\Eigene Dateien\[DBAdressen.xlsx]Allgemein'!$A$" 'Folgende Scheife schreibt nacheinander die Formel in B2 und fügt der Listbox 'dann das Ergebnis der Formel hinzu. Private Sub CommandButton1_Click() Dim intI As Integer ListBox1.Clear Application.DisplayAlerts = False For intI = 1 To 25 Range("B1").Formula = strBezug & intI If Not IsError(Range("B1")) Then ListBox1.AddItem Range("B1").Text Next Range("B1") = "" Application.DisplayAlerts = True End Sub

Das Ganze kann (und sollte) natürlich mit Errorhandlings verfeinert werden.

=INDEX(A1:A13;E1)

In E1 steht die Zellverknüpfung, A1:A13 ist die Liste.

Im Modul2 soll die Zeile Windows("Formulare").Activate ersetzt werden durch Windows("Formulare.xls").Activate.

Sub ZeileInCodeErsetzen() Dim strSuchtext As String, strNeuertext As String Dim intI As Integer strSuchtext = "Windows(" & """" & "Formulare" & """" & ").Activate" strNeuertext = "Windows(" & """" & "Formulare.xlsx" & """" & ").Activate" 'Jede Zeile in Modul2 wird durchsucht: With ActiveWorkbook.VBProject.VBComponents("Modul2").CodeModule For intI = 1 To .CountOfLines 'Wenn die Zeile gleich dem Suchtext ist, ... If .Lines(intI, 1) = strSuchtext Then '... Zeile löschen: .DeleteLines intI '... neue Zeile einfügen: .InsertLines intI, strNeuertext End If Next End With End Sub

Falls die Meldung Der programmatische Zugriff auf das Visual Basic-Projekt ist nicht sicher erscheint, müssen folgende Einstellungen vorgenommen werden:

Eigentlich gar nicht.

Man kann aber mit einer Formel Bezug auf eine geschlossene Mappe nehmen; deren Ergebnis erscheint dann. Dies kann man dann mit VBA verwenden. Also kann man auch mit VBA die Formel eintragen und dann das Ergebnis abgreifen. Die Formel kann zum Schluss wieder gelöscht wreden.

Beispiel:
Im Ordner C:\Eigene Dateien befindet sich die Datei DBAdressen.xlsx. Aus dieser Datei sollen vom Blatt Allgemein die Daten aus A1 bis A25 in eine Listbox eingelesen werden, ohne diese Datei zu öffnen.

Const strBezug As String = "='C:\Eigene Dateien\[DBAdressen.xlsx]Allgemein'!$A$" 'Folgende Scheife schreibt nacheinander die Formel in B2 und fügt der Listbox 'dann das Ergebnis der Formel hinzu. Private Sub CommandButton1_Click() Dim intI As Integer ListBox1.Clear Application.DisplayAlerts = False For intI = 1 To 25 Range("B1").Formula = strBezug & intI If Not IsError(Range("B1")) Then ListBox1.AddItem Range("B1").Text Next Range("B1") = "" Application.DisplayAlerts = True End Sub

Das Ganze kann (und sollte) natürlich mit Errorhandlings verfeinert werden.

Dazu gibt es verschiedene Möglichkeiten. Manche haben Nachteile, zum Beispiel Probleme mit UTF-8 oder dass gesendete Daten in den Server-Logs erscheinen.

Empfehlenswert ist das MSXML2.XMLHTTP-Objekt, mit dem per POST Formulardaten ganz einfach gesendet und Antworten empfangen werden können. Die Daten werden einfach als key=value-Kombinationen an die Adresse des Server-Scripts (Beispiel: PHP) übergeben. Ein einfaches Beispiel dazu:

Sub Post_mit_Response() Dim strURL As String, strPostDaten As String strURL = "https://example.org/phptest.php" strPostDaten = "" strPostDaten = strPostDaten & "name=Müller" strPostDaten = strPostDaten & "&vorname=Hans" strPostDaten = strPostDaten & "&strasse=Blumenweg 22" strPostDaten = strPostDaten & "&ort=Musterort" With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten) If .ResponseText <> "" Then 'Response je nach Anforderung prüfen MsgBox .ResponseText 'Antwort des Servers ausgeben Else MsgBox "Schiefgegangen.", vbOKOnly + vbCritical, "Fehler" End If End With End Sub

Ein Beispiel für ein zugehöriges PHP-Script:

$adresse = "Die Adresse ist:\n\n"; $adresse .= $_POST['vorname'] . " " . $_POST['name'] . "\n"; $adresse .= $_POST['strasse'] . "\n"; $adresse .= $_POST['plz'] . " " . $_POST['ort'] . "\n"; echo $adresse;

In diesem Beispiel würde PHP aus den einzelnen Angaben eine formatierte Adresse gestalten und diese an VBA senden.

Dadurch können u. a. Daten aus Zellen oder Userformen ganz einfach per Mausklick, Tastendruck oder auch per Event an ein Script auf dem Server gesendet werden, das dann die weitere Verarbeitung erledigt. Somit können Mails versendet oder Eintragungen in Datenbanken (z. B. mySQL) vorgenommen werden usw.

Die Antwort des Servers ist hier natürlich nur reiner Text. Für komplexere Vorgänge sollten Sie sich ansehen, was in der Zusammenarbeit von VBA mit JSON möglich ist.

Download: excel_php_json.xlsm

Ab Excel 365 kann einfach die Funktion verwendet werden:

=FORMELTEXT(K3)

Die Formel mit dieser Funktion gibt die Formel zurück, die in K3 steht.

Für ältere Versionen kann die benutzerdefinierte Funktion verwendet werden:

Prüfen, ob in der Zelle eine Formel vorliegt und anschlie�end die Formel (ohne Gleichheitszeichen) mit dem Ergebnis ausgeben lassen:

Function Bezug(Zelle) If Zelle.HasFormula Then Bezug = Right(Zelle.Formula, Len(Zelle.Formula) - 1) & " = " & Zelle Else Bezug = "" End Function

In die Zelle kommt dann einfach die Formel =Bezug(D7), wobei hier in D7 die eigentliche Formel steht.

Noch eine benutzerdefinierte Funktion dazu, die einfach das Gleichheitszeichen ersetzt (also löscht):

Function Bezug1(Zelle) If Zelle.HasFormula Then Bezug = Replace(Zelle.FormulaLocal, "=", "") Else Bezug = "" End If End Function

In die Zelle kann dann z. B. eingegeben werden: =Bezug1(A1)

Oder für die Freunde gepflegter regulärer Ausdrücke (Microsoft VBScript Regular Expressions-Objektbibliothek muss eingebunden sein!):

Function Bezug2(Zelle) Dim Regex As New RegExp, regMatches As MatchCollection, regMatch As Match Bezug1 = 0 Regex.Pattern = "^(=)(.*)$" Set regMatches = Regex.Execute(Zelle.FormulaLocal) If regMatches.Count > 0 Then Bezug1 = regMatches(0).SubMatches(1) End Function

Angenommen in Spalte A und B stehen Zahlen, in Spalte C sollen jeweils die Summenformeln stehen. Die Zeilenanzahl wird durch die Spalte B bestimmt.

Variante mit englischer Syntax:

Sub Formeleinfuegen() Dim lngI As LongPtr For lngI = 1 To Cells(Rows.Count, 2).End(xlUp).Row ActiveSheet.Range("C" & lngI).Formula = "=SUM(A" & lngI & ",B" & lngI & ")" Next End Sub

Variante mit deutscher Syntax:

Sub Formeleinfuegen1() Dim lngI As LongPtr For lngI = 1 To Cells(Rows.Count, 2).End(xlUp).Row ActiveSheet.Range("C" & lngI).FormulaLocal = "=SUMME(A" & lngI & ";B" & lngI & ")" Next End Sub

Die integrierten Dialogfelder von Excel können auch mit VBA aufgerufen werden. Dies geschieht einfach mit:

Application.Dialogs(Konstante).Show

Bei den Dialogfeldern können verschiedene Argumente mitgegeben werden, die natürlich bei jedem Element anders sind. Hierzu am besten einfach in die Hilfe sehen.

Manchmal muss man sich entscheiden, ob ein integriertes Dialogfeld oder ein herkömmliches Dialogfeld für die Aufgabe besser geeignet ist. Mit z. B. Application.GetOpenFilename kann schön der Pfad abgefragt werden, was mit xlDialogOpen schon nicht mehr so einfach ist.

Download: integrierte_dialogfelder.xlsm

Möglichkeit mit Application.Undo:

Wichtig ist, den Bereich festzulegen, in dem das Löschen verhindert werden soll und um welche Daten es sich handelt, die nicht gelöscht werden sollen. Im Beispiel geht es um die Spalte 7 (G), in der sich Formeln befinden.

Dazu eignet sich Worksheet_Change, was �nderungen auf der Tabelle überwacht. Wenn dort in Spalte 7 etwas gelöscht oder durch einen konstanten Wert überschrieben wurde, soll rückgängig gemacht werden:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 7 And Target.HasFormula = False Then Application.Undo End Sub

Worksheet_Change-Ereignis

Die Routine wird im VBA-Editor in das Modul eingetragen, das durch Doppelklick auf die Tabelle, in der der Code wirken soll, geöffnet wird. Es werden hier zwei Varianten aufgezeigt: In Spalte B wird eine Formel eingetragen, die das Ergebnis liefert. Falls ein Ergebnis ohne Formel gewünscht wird, wird dies noch in Spalte C eingetragen.

Die Routine wird nur ausgeführt, wenn die Eingabezelle in Spalte 1 (A) ist. Dann werden zunächst die Zielzellen daneben in B und C geleert.

Da intern mit Punkt statt Komma als Dezimaltrenner gerechnet wird, wird ein eventuell vorhandenes Komma zuerst ersetzt. AnschlieÃ?end wird mit Evaluate versucht, zu berechnen. Wird die Berechnung erkannt, wird ein Ergebnis geliefert, sonst der Fehler #NAME?. Letzteres kommt zum Beispiel vor, wenn ein Text in A eingetragen wurde.

Tritt kein Fehler auf, wird in B die entsprechende Formel eingetragen, in C direkt das Ergebnis.

Private Sub Worksheet_Change(ByVal Target As Range) Dim varTemp, varErg If Target.Column > 1 Then Exit Sub Range("B" & Target.Row & ":C" & Target.Row).ClearContents varTemp = Replace(Target, ",", ".") varErg = Application.Evaluate(varTemp) If Not IsError(varErg) Then Cells(Target.Row, 2).Formula = "" & "=" & varTemp & "" Cells(Target.Row, 3) = varErg End If End Sub

UDF - benutzerdefinierte Funktion

Es ist (in diesem Fall ab Excel 365) auch möglich, das Ergebnis der Berechnung ohne Gleichheitszeichen per Formel zu erhalten. Notwendig ist dazu eine solche benutzerdefinierte Funktion in einem Standardmodul:

Function Evaluate_String(ByVal strString As String, Optional intWas As Integer = 0) Dim varTemp, varErg Evaluate_String = "" varTemp = Replace(strString, ",", ".") varErg = Application.Evaluate(varTemp) If Not IsError(varErg) Then Evaluate_String = IIf(intWas <> 0, "=" & varTemp & "", varErg) End Function

In die Zelle, in der das Ergebnis der Formel ohne Gleichheitszeichen erscheinen soll, muss dann nur:

=Evaluate_String(C10)

Wenn die Formel nicht das Ergebnis, sondern die Formel (also mit Gleichheitszeichen) anzeigen soll, kann als zweiter Parameter etwas anderes als 0 verwendet werden, zum Beispiel:

=Evaluate_String(C10;1)

Sie hat dann ein vergleichbares Verhalten wie die integrierte Funktion FORMELTEXT().

Eine Lösung per Zahlenformatierung (00":"00) ist nicht empfehlenswert. Die Zahlen sehen zwar wie Uhrzeiten aus, ein Weiterrechnen mit diesen "Zeiten" ist aber nicht möglich. Besser ist eine Makrolösung, die automatisch eine eingegebene Zahl in eine Uhrzeit umwandelt.

Ein Klassenmodul »Klasse1« einfügen, dort diesen Code eingeben:

Public WithEvents Anwendung As Application Private Sub Anwendung_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim bolEvents As Boolean, intI As Integer, varZeit As Variant, arrTemp If Target.Cells.Count > 1 Then Exit Sub If Target = "" Then Exit Sub If Target.HasFormula Then Exit Sub If InStr(1, Target, "+") = 0 Then Exit Sub arrTemp = Split(Target, "+") If UBound(arrTemp) > 2 Then Exit Sub varZeit = "" For intI = 0 To UBound(arrTemp) varZeit = varZeit & arrTemp(intI) & IIf(intI < UBound(arrTemp), ":", "") Next If IsDate(varZeit) Then Target = varZeit Application.EnableEvents = bolEvents End Sub

In »DieseArbeitsmappe« diesen Code einfügen:

Dim Anwendungsobjekt As New Klasse1 Private Sub Workbook_Open() Set Anwendungsobjekt.Anwendung = Application End Sub

Immer dann, wenn diese Mappe offen ist, können Sie Zeiten mit dem Pluszeichen eingeben. Empfehlenswert ist, diese Mappe als Add-In zu speichern. Ein fertiges finden Sie hier: Downloads