Archive for the ‘MS Office & VBA’ Category.

Eine IN Funktion für Excel VBA

Hier basierend auf einem Beispiel von Microsoft eine IN-Funktion. Mit dieser lässt sich prüfen, ob ein String in einem zusammengesetzten String enthalten ist.

Option Explicit
 
Function Contains(needle As String, haystack As String, separator As String) As Boolean
 
Dim rv As Boolean, lb As Long, ub As Long, i As Long, field() As String
 
field = Split(haystack, separator)
 
    lb = LBound(field)
    ub = UBound(field)
    For i = lb To ub
        If field(i) = needle Then
            rv = True
            Exit For
        End If
    Next i
    Contains = rv
End Function

Nachtrag: Möchte man prüfen, ob ein Wert in einer Range vorhanden ist, kann man die folgende User-Defined Function nutzen:

Function InRange(needle As Variant, haystack As Range) As Boolean
Dim rv As Boolean, cell As Range
 
    For Each cell In haystack
        If cell = needle Then
            rv = True
            Exit For
        End If
    Next cell
    InRange = rv
 
End Function

Uwe

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website

Bestellungen per E-Mail aus Excel heraus

Vor ein paar Wochen habe ich ein kleines Bestelltool in Excel gebaut, um die Bestellungen bei Danielz‘ Foodtruck zu vereinfachen.

Ausgangspunkt ist eine kleine Tabelle, in der die einzelnen Gerichte mit den Preisen aufgeführt sind. Über die Menge wird dann der jeweilige Preis ausgerechnet und die Summe gebildet. Es sind verschiedene Ranges definiert (für die Ziel-E-Mail-Adresse, den Standard-Betreff, etc.) die wichtigste ist aber die Auswahl-Range, die die gesamte Tabelle mit den Gerichten umfasst.

Mit ein wenig VBA wird dann die Outlook-Mail erzeugt.

Den Namen des Benutzers könnte man theoretisch per VBA.Environ("Username") ermitteln, dies liefert aber nur das Login und nicht den Klarnamen. Dazu ist eine Abfrage des Active Directory (siehe https://technet.microsoft.com/en-us/library/2007.08.heyscriptingguy.aspx) notwendig:

Function GetUsername()
     Set objAD = CreateObject("ADSystemInfo")
     Set objUser = GetObject("LDAP://" & objAD.UserName)
     GetUsername = objUser.DisplayName
End Function

Über die getOrder() Funktion erstellen wir den String für den jeweiligen Besteller aus der Tabelle.

Function getOrder()
    auswahl = Range("Auswahl")
    bestellstring = "* " & GetUsername() & ": "
    Order = 0
 
    For i = 1 To UBound(auswahl)
        If (auswahl(i, 3)) > 0 Then
            If Order > 0 Then
                bestellstring = bestellstring & ", "
            End If
            bestellstring = bestellstring & auswahl(i, 3) & " x " & auswahl(i, 1)
            Order = 1
        End If
    Next i
 
    Debug.Print bestellstring
    getOrder = bestellstring
 
End Function

Im letzten Schritt gilt es nur noch, den erzeugten String per Mail zu versenden. Hier waren die Code-Schnipsel von http://www.rondebruin.nl/win/winmail/Outlook/tips.htm sehr hilfreich. Hinweis: Der Button im Bestellformular ist an diese Sub gebunden.

Sub Mail_small_Text_Outlook()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
 
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
 
    strbody = "Hier ist meine Bestellung:" & vbNewLine & vbNewLine & vbNewLine
 
 
    body = getOrder()
 
    If Len(Range("kommentar") > 0) Then
        body = body & vbNewLine & Range("kommentar")
    End If
 
 
 
    On Error Resume Next
    With OutMail
        .To = Range("receiver")
        .CC = ""
        .BCC = ""
        .Subject = Range("subject")
        .body = strbody & body
        .Display
    End With
    On Error GoTo 0
 
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Uwe

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website

Mit VBA den Namen des Nutzers ermitteln

Ich arbeite momentan an einer kleinen Excel-Anwendung, um die wöchentliche Bestellung von meinen Kollegen und mir bei „Danielz Foodtruck“ (https://www.facebook.com/Danielz-Food-Truck-709172459137209/) zu optimieren. Mit reinem VBA kommt man leider nur an den Login-Namen, nicht aber an den eigentlichen Namen des Nutzers:

Sub showUsername()
   MsgBox VBA.Environ("Username")
End Sub

Die Information steht jedoch im Active Directory, mit ein paar Zeilen VBA (Quelle: https://community.spiceworks.com/topic/361258-using-vba-to-report-user-s-full-name-maybe-from-ad) kann man sie abfragen:

Function GetUsername()
     Set objAD = CreateObject("ADSystemInfo")
     Set objUser = GetObject("LDAP://" & objAD.UserName)
     GetUsername = objUser.DisplayName
End Function

Uwe

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website

Gesamtfolienzahl in Powerpoint einfügen

Als TeXie mag man es nicht glauben, aber in Powerpoint gibt es keine eingebaute Möglichkeit, die Gesamtzahl der Folien auf der Folie selbst auszugeben. Über den Umweg VBA geht es (gefunden unter https://superuser.com/questions/130489/insert-total-number-of-slides-in-powerpoint-2007)


Sub numberSlides()
' https://superuser.com/questions/130489/insert-total-number-of-slides-in-powerpoint-2007
' run with F5

Dim s As Slide
Dim shp As Shape

For Each s In ActivePresentation.Slides
s.DisplayMasterShapes = True
s.HeadersFooters.SlideNumber.Visible = msoTrue

For Each shp In s.Shapes
If Left(shp.Name, 12) = "Slide Number" Then
shp.TextFrame.TextRange.Text = s.SlideNumber & " von " & ActivePresentation.Slides.Count
End If
Next
Next
End Sub

Uwe

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website

Excel: Formel nur auf die x-te Zeile anwenden

Hier ein kurzes Beispiel für die Nutzung der Rest() Funktion, um Formeln in Excel nur auf jede x-te Zeile anzuwenden.

  1. Mit zeile() erhält man die Zeilennummer des aktuellen Bezugs
  2. Rest() gibt den Rest bei der ganzzahligen Teilung zurück
  3. Wenn() prüft einfach die Bedingung, ob Rest() einen bestimmten Wert hat

Man kann nicht nur gerade/ungerade prüfen (oberes Beispiel), auch bei anderen Zeilensprüngen klappt das.

excel

Uwe

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website

Excel VBA: Blatt in Liste von Excel Dateien kopieren

Eine Aufgabe für zwischendurch: Wie kann man ein bestehendes Excel-Blatt in eine Anzahl von anderen Excel-Dateien kopieren?

  • Definiere eine benannte Zell-Range, hier „Workbooks“ genannt
  • In dieser Liste trage alle Excel-Dateien ein, in die das Muster-Blatt (hier „Template“ genannt) kopiert werden soll.
    Hinweis: Ich habe diese Liste mit dir /b *.xlsx erzeugt.
  • Setze einen Button in das Sheet und hinterlege als Code das folgende
  • Wichtig: Die aktuelle Arbeitsmappe mit dem Button und der Liste liegt im selben Verzeichnis wie die Ziel-Dateien. Wenn nicht, dann muss der Pfad angepasst werden.
Sub Schaltfläche1_Klicken()
 
Dim c As Range
For Each c In Range("Workbooks")
    MsgBox (c.Value)
    Set kopiereWas = ThisWorkbook.Sheets("Template")
    Set kopiereWohin = Workbooks.Open(Application.ActiveWorkbook.Path + "\\" + c.Value)
    kopiereWas.Copy kopiereWohin.Sheets(1)
Next c
End Sub

Hinweis: Die Ziel-Arbeitsmappe wird hier nicht geschlossen, werde ich zusammen mit Screenshots nachliefern.

Uwe

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website

Excel Keyboard Shortcuts

A PDF with the most important Excel shortcuts: http://www.thecompanyrocks.com/wp-content/uploads/2011/02/CR-Updated-Chart-of-Popular-Excel-Keyboard-Shortcuts.pdf

Uwe

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website

Excel Funktion zum Zerlegen eines Strings

Die folgende Excel-Funktion ist nützlich, um innerhalb von Excel Strings in ihre Bestandteile zu zerlegen.


Function SplitteString(zeichenkette, separator, vorkommen) As String
Dim feld() As String
feld = Split(zeichenkette, separator)
SplitteString = feld(vorkommen - 1)
End Function

split

Uwe

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website

Using Document Properties in Microsoft Word 2007

What I really like about LaTeX is the way one can easily define own commands that get expanded in the text whenever needed. Word has a similar feature, called document variables. Unfortunately these can only be set by VBA macros, there is (according to my knowledge) to way to manage them via GUI. Fortunately Word has another feature which provides a more or less similar functionality with the name „Document properties“.

Here’s a brief tutorial how to define and use a new variable:

1) Open the properties of the document

01

2) Open the extended properties

02

3) Create a new variable, here I used „Projekt“ with the value „MeinProjekt“

03

4) To use this new variable simply insert a new field:

04

05

5) and select the corresponding variable

06

6) The internal syntax is the following (when you press Alt-F9 you get this „internal“ view)

07

Uwe

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website

MS Excel: Komplexe Wenn()-Formeln durch Vergleich ersetzen

Vor kurzem hatte ich eine kleine Herausforderung in Excel: Beträge in Spalte C mussten mit negativem Vorzeichen versehen werden, in Abhängigkeit einer Zahl in Spalte B (gelb markiert im Bild). Lösen lässt sich dies per verschachtelter WENN() Funktion, bei vielen Verschachtelungen wird die Formel aber schnell unübersichtlich und fehleranfällig. Deutlich einfacher und übersichtlicher geht es über eine Mischung aus VERGLEICH() und ISTNV(), die ich bei stackexchange gefunden hatte.

=WENN(ISTNV(VERGLEICH([@Unterkonto];unterkonten;0));[@Amount];-1*[@Amount])

  • für die Zahlen, die den Vorzeichenwechsel auslösen, habe ich einen benannten Bereich ‚unterkonten‘ festgelegt
  • VERGLEICH() schaut, ob ein Wert (der erste Parameter) in einer Liste (zweiter Parameter) vorhanden ist. Wird ein Wert nicht gefunden, wird #NV zurückgeliefert. Der zweite Parameter kann auch ein benannter Bereich sein.
  • Um die #NV Werte zu behandeln, prüfen wir mit ISTNV() die Rückgabe der VERGLEICH() Funktion
  • Die Rückgabe von ISTNV() wird dann per WENN() geprüft. Wenn ISTNV() WAHR zurückliefert, dann hat VERGLEICH() das Konto nicht in der Liste gefunden, sonst ist der Werte aus Spalte „Amount“ mit -1 zu multiplizieren.

Download der Excel-Datei

Excel Vergleich ISTNV

Uwe

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website