Excel

Hier werden so nach und nach ein paar kleine und vermutlich ungeordnete Tipps & Tricks zu Excel ergänzt...

Tabellenfunktionen vs. VBA-Befehle

(16.10.2014) Ich komme immer durcheinander - wie heißt denn nun eine bestimmte Funktion und wie ist die Reihenfolge der Argumente? Brauche ich die VBA-Funktion, fällt mir natürlich nur die entsprechende Excel Tabellenfunktion ein. Brauche ich aber die Funktion in einer Exceltabelle, gibt es diese erst gar nicht oder sie fällt mir nicht ein...

Folgende Tabelle soll diesem Missstand etwas Abhilfe schaffen, wem es geht wie mir, kann vielleicht einen Nutzen daraus ziehen. Es ist zumindest ein Anfang.

Eine Übersicht
Tabellenfunktion	VBA-Funktion	Erklärung
FINDEN(Suchtext;Text;1)	InStr(1,Text,Suchtext,vbBinaryCompare)	case-sensitive Suche (Groß- und Kleinschreibung wird beachtet)
SUCHEN(Suchtext;Text;1)	InStr(1,Text,Suchtext,vbTextCompare)	nicht case-sensitive Suche (Groß- und Kleinschreibung ist egal)
TEIL	Mid	Stringverarbeitung
RECHTS	Right	Stringverarbeitung
LINKS	Left	Stringverarbeitung
ZEICHEN(Zahl)	Chr$(Zahl)	Stringverarbeitung
CODE(Zeichen)	Asc(Zeichen)	Stringverarbeitung

Datentyp Variant

(15.09.2014) Er ist schon recht praktisch - der Datentyp Variant. Er passt sich quasi von allein an die zu speichernden Werte an. Man muss sich also im Vorhinein, bei der Dimensionierung nicht allzu viele Gedanken machen, was für Werte an die Variable diesen Datentyps übergeben werden. Allerdings ist das auch ein gravierender Nachteil des Datentyps, denn man hat nun keine Kontrolle mehr wie die Werte gespeichert werden. Da kann es durchaus vorkommen, dass Werte, die eigentlich als Integer oder Double gespeichert werden könnten intern als String behandelt werden. Das kann zu Problemen bei Vergleichen führen. Abhilfe schafft hier die Funktion TypeName(NameDerVariablen), die den aktuell intern genutzten Variablentyp für die Variant-Variable zurückgibt.

Zeichen maskieren

Manchmal ist es notwendig Zeichen zu maskieren, da sie sonst falsch ausgegeben werden oder den Programmlauf stören. Ich musste mal wieder per Excel-Makro eine HTML-Seite generieren. Dabei werden eine ganze Menge HTML-Tags per VBA in eine Datei geschrieben. Blöderweise wollen die Tags lauter Parameter in Anführungszeichen. Aber das Anführungszeichen markiert Anfang und Ende eines Strings. Nun braucht man das Anführungszeichen aber im String. Wie macht man das? Hier meine Lösung:

Sub Zeichen_Maskieren()

   'Anführungszeichen mit Anführungszeichen maskieren.
    a = "Eine ""tolle"" Idee."
   'ergibt: Eine "tolle" Idee.
    
   'Anführungszeichen mit Chr() erzeugen. Das Maskieren wird umgangen.
    b = "Auch eine " & Chr(34) & "super" & Chr(34) & " Möglichkeit."
   'ergibt: Auch eine "super" Möglichkeit.    

    
   'Das ' ist das Kommentarzeichen, so geht das in Excel VBA natürlich nicht:
    c = 'Eine "blöde" Idee'
   'führt zu einem Fehler, da der Variable c nichts zugewiesen wird.
  
   'Allerdings kann man das Kommentarzeichen in Strings nutzen.
    d = "Geht's noch?"
   'ergibt: Geht's noch?

End Sub

Einen Fehlerwert aus einer Funktion zurückgeben

Schreibt man eigene Funktionen mit VBA möchte man ab und an auch einen Excel - Fehler zurückgeben können, beispielsweise #NV, #WERT! oder bei rechnenden Funktionen vielleicht auch #DIV/0! als Rückgabewert.

Da ich es nicht allzu oft brauche, vergesse ich die Syntax immer wieder und komischerweise findet man über die großen Suchmaschinen diese Informationen nicht wirklich leicht. Daher hab ich es nun hier aufgeschrieben und für mich und alle Interessierten immer parat.

Function MeineFunktion()
'
' viel Platz für einen tolle Funktion :)
'
' wenn ein Fehler aufgetreten ist oder die übergebenen Werte nicht stimmig sind, dann
'
MeineFunktion = CVErr(2042)  ' Anzeige in im Excel-Sheet => #NV
'
' #NULL!  => 2000 (Tritt auf, wenn ein Leerzeichen, statt eines Semikolons beim Trennen von Bereichen verwendet wurde)
' #DIV/0! => 2007 (Eine Zahl wird durch Null (0) dividiert)
' #WERT!  => 2015 (Für ein Argument oder einen Operanden wird der falsche Typ verwendet)
' #BEZUG! => 2023 (Tritt bei einem ungültigen Zellbezug auf)
' #NAME?  => 2029 (Die Formel wird nicht erkannt, falsch geschrieben)
' #ZAHL!  => 2036 (Formel oder Funktion enthält ungültige numerische Werte)
' #NV     => 2042 (Wert für eine Funktion oder Formel ist nicht verfügbar)
'
End Function

Tabellenfunktion MONATSENDE

In Excel gibt es eine Tabellenfunktion MONATSENDE. Normalerweise kann man aus VBA heraus mit Application.WorksheetFunction.NameOfTheFunction auf die Tabellenfunktionen zugreifen. Man muss nur die englische Entsprechung der Tabellenfunktion kennen. In diesem Falle wäre das die WorksheetFunction EOMONTH.

Leider klappt das nicht, aber es gibt eine einfache Alternative, um trotzdem mit VBA ohne große Rechnerei das Ende eines Monats feststellen zu können.

Function last_day(einDatum As Date)
    last_day = DateSerial(Year(einDatum), Month(einDatum) + 1, 0)
End Function

(Update 25.02.2015) Keine Ahnung, warum ich beim ersten Versuch das Monatsende in VBA zur ermitteln solche Probleme hatte. Die Funktion EoMonth hat zwei Argumente: das Ausgangsdatum als Variant und ein Offset in Monaten, wobei eine 0 (Null) für den aktuellen Monat zu verwenden ist und negative Zahlen in die Vergangenheit rechnen.

Function last_day(einDatum As Date)
    last_day=CDate(Application.WorksheetFunction.EoMonth(DateValue(einDatum), 0))
End Function

Schreib was...

Nach oben