Archive for the ‘MS Office & VBA’ Category.
2012-04-15, 10:48
Eine Mathematik-Klausur an der Uni Köln hat einigen Staub aufgewirbelt (http://www.spiegel.de/unispiegel/studium/0,1518,827375,00.html), die Aufgaben sind im Spiegel-Artikel verlinkt.
Aufgabe 3 fand ich interessant: Die Anzahl von Posaunen und Trompeten so zu maximieren, dass das Budget von 10’000 Euro eingehalten wird.
Auch ohne groß herum zu rechnen, lässt sich dies leicht in Excel lösen. Man bestimmt einfach die Kosten der verschiedenen Kombinationen aus $x$ Trompeten und $y$ Posaunen und schaut, welche Kombination am nächsten an den 10’000 Euro dran ist. Mit wenigen Zeilen VBA-Code und bedingter Formatierung lässt sich auch leicht eine Planungshilfe gestalten, die bei unterschiedlichen Preisen und anpassbarem Budget die optimale Lösung ausspuckt.
![klausur](http://uweziegenhagen.de/wp-content/uploads/2012/04/klausur-300x158.jpg)
Klausur.xlsm
Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined.
Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.
More Posts - Website
2012-04-04, 21:59
Excel stellt leider keine Funktion bereit, um das Vorhandensein einer Formel in einer Zelle zu prüfen. Behelfen kann man sich aber mit einer selbst gestrickten Excel-Funktion (gefunden bei http://www.vbaexpress.com/kb/getarticle.php?kb_id=324):
Option Explicit
Function ISFORMULA(cel As Range) As Boolean
ISFORMULA = cel.HasFormula
End Function |
Option Explicit
Function ISFORMULA(cel As Range) As Boolean
ISFORMULA = cel.HasFormula
End Function
isFormel.xlsm
Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined.
Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.
More Posts - Website
Schlagwörter:
Excel,
VBA Category:
MS Office & VBA |
Kommentare deaktiviert für Excel: Prüfen, ob eine Zelle eine Formel enthält
2012-03-23, 20:58
Aus Interesse heraus hab ich heute versucht, per Excel VBA alle Diagramme eines Excel-Sheets als PDF zu exportieren. Google brachte als ersten Treffer einen Blogeintrag, der mich auf die richtige Fährte brachte: http://cschleiden.wordpress.com/2009/09/28/howto-export-excel-charts-as-pdf-to-include-in-latex-document/. Dieser Eintrag, zusammen mit einem aufgezeichneten Makro eines manuellen Export-Vorgangs und einige Codeschnipsel von http://www.vbaexpress.com/kb/getarticle.php?kb_id=482 haben mich innerhalb von 20 Minuten zu dem folgenden VBA Code gebracht:
Sub ExportAllCharts()
If ActiveSheet.ChartObjects.Count > 0 Then
For Each Diagram In ActiveSheet.ChartObjects
ActiveSheet.ChartObjects(Diagram.Name).Activate
Filename = ActiveChart.Name
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Uwe\Desktop\" & Filename, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next Diagram
End If
End Sub |
Sub ExportAllCharts()
If ActiveSheet.ChartObjects.Count > 0 Then
For Each Diagram In ActiveSheet.ChartObjects
ActiveSheet.ChartObjects(Diagram.Name).Activate
Filename = ActiveChart.Name
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Uwe\Desktop\" & Filename, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next Diagram
End If
End Sub
Was macht dieser Code? Wenn überhaupt Diagramme im aktuellen Worksheet vorhanden sind, dann wird für jedes gefundene Diagramm der Dateiname bestimmt (als Namen des Diagramms, z.B. „Tabelle1 Diagramm 1.pdf“ oder „Tabelle1 Diagramm 2.pdf“) und die Datei auf dem Desktop als PDF abgelegt. Der Code ist noch rudimentär und kann noch beliebig erweitert werden.
Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined.
Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.
More Posts - Website
2012-02-05, 21:06
Den ersten und letzten Tag eines Monats bzw. Quartals kann man recht einfach in Excel bestimmen.
Sei in A1
ein Datum, für das die Werte bestimmt werden sollen.
- Monatserster: =DATUM(JAHR(A1);MONAT(A1);1)
- Monatsletzter: =DATUM(JAHR(A1);MONAT(A1)+1;1)-1
- Quartalserster: =DATUM(JAHR(A1);MONAT(A1)-REST(MONAT(A1)-1;3);1)
- Quartalsletzter: =DATUM(JAHR(A1);MONAT(A1)+REST(3-MONAT(A1);3)+1;)
Hier noch ein paar Datumsformeln:
- Quartal =AUFRUNDEN(MONAT(A1)/3;0)
Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined.
Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.
More Posts - Website
Schlagwörter:
Excel Category:
MS Office & VBA |
Kommentare deaktiviert für In Excel den Monats- und Quartalsersten/-letzten bestimmen
2012-01-20, 23:43
Zu meinen aktuellen Aufgaben gehört es gelegentlich, Aufträge an den IT-Dienstleister in einem bestimmten Format zu verfassen. Nachdem ich einige Male die notwendigen Texte mühevoll zusammenkopiert hatte, kam der Wunsch nach der Automatisierung des ganzen auf. Mit ein wenig VBA Code und Google (http://www.exceltip.com/show_tip/Applications_-_Word,_Outlook_in_VBA/Control_Outlook_from_Excel_using_VBA_in_Microsoft_Excel/464.html) ist es recht einfach, die E-Mails zusammenzubauen.
Hier ein einfaches Beispiel: Aus der jeweils aktiven Zeile sollen E-Mail Adresse, Betreff und Body ausgelesen und in eine E-Mail gesetzt werden, die aktive Zeile ermittele ich dabei über currentrow = ActiveCell.Row
.
![office1](http://uweziegenhagen.de/wp-content/uploads/2012/01/office1.png)
Der Code, der unter dem Link verfügbar ist, benötigt eine Referenz zur Microsoft Outlook Object Library, diese lässt sich über Extras => Verweise einfügen. Wenn wir das Beispiel auf unser Szenario anpassen, erhalten wir:
Sub SendAnEmailWithOutlook()
currentrow = ActiveCell.Row
Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
Dim ToContact As Outlook.Recipient
Set OLF = GetObject("", _
"Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
Set olMailItem = OLF.Items.Add ' erstellt neue Mail
With olMailItem
.Subject = Cells(currentrow, 3).Value ' Betreff
Set ToContact = .Recipients.Add(Cells(currentrow, 2).Value) ' Empfänger
.Body = Cells(currentrow, 4).Value ' Body
.Save ' nur Speichern, nicht sofort senden
End With
Set ToContact = Nothing
Set olMailItem = Nothing
Set OLF = Nothing
End Sub
Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined.
Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.
More Posts - Website
2011-12-25, 21:11
Hier ein kurzes Makro, um den Wert der aktuellen Zelle in der Zeile darunter zu duplizieren.
Sub Makro1()
'
' Makro1 Makro
'
'
ActiveCell.Offset(1, 0).Select
Application.CutCopyMode = False
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
End Sub |
Sub Makro1()
'
' Makro1 Makro
'
'
ActiveCell.Offset(1, 0).Select
Application.CutCopyMode = False
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
End Sub
Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined.
Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.
More Posts - Website
2011-12-10, 10:18
Externe Tools mögen zwar deutlich performanter sein, manchmal reicht es aber auch, MD5 Prüfsummen in Excel selbst zu bestimmen. Unter http://www.tutorials.de/visual-basic-6-0/275442-md5-per-vba.html findet man entsprechenden VBA Code, der die entsprechende Funktion als Formel bereitstellt. Es empfiehlt sich, den kompletten Thread zu lesen, da in diesem Code noch Referenzen auf eine Progressbar entfernt oder auskommentiert werden müssen.
Informationen zu MD5 selbst finden sich zum Beispiel in der Wikipedia: http://de.wikipedia.org/wiki/Message-Digest_Algorithm_5.
![md5](http://uweziegenhagen.de/wp-content/uploads/2011/12/md5.jpg)
Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined.
Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.
More Posts - Website
2011-11-27, 17:47
Gelegentlich ist es hilfreich, die Namen aller Arbeitsblätter in einer Excel-Mappe zu kennen. Der folgende VBA Code gibt genau dies aus:
Sub SheetNames()
For i = 1 To Sheets.Count
Cells(i, 1) = Sheets(i).Name
Next i
End Sub
Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined.
Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.
More Posts - Website
Schlagwörter:
Excel Category:
MS Office & VBA |
Kommentare deaktiviert für Mit VBA die Namen aller Excel Arbeitsblätter ausgeben
2011-07-22, 22:44
Vor kurzem musste ich Pfadangaben in Excel auf Zeichen prüfen, die nicht der Vorgabe (Buchstaben, Ziffern, Leer- und Unterstrich) entsprechen. Folgende VBA Funktion war recht schnell geschrieben und läuft auch recht performant.
Function ContainsBadChar(source As String) As String
Dim char As String
For i = 1 To Len(source)
char = Mid(source, i, 1)
If ((Asc(char) > 64 And Asc(char) < 91) Or (Asc(char) > 96 And Asc(char) < 123) Or (Asc(char) > 47 And Asc(char) < 58) Or (Asc(char) = 95) Or (Asc(char) = 45))
Then
ContainsBadChar = "0"
Else
ContainsBadChar = "1"
Exit For
End If
Next
End Function |
Function ContainsBadChar(source As String) As String
Dim char As String
For i = 1 To Len(source)
char = Mid(source, i, 1)
If ((Asc(char) > 64 And Asc(char) < 91) Or (Asc(char) > 96 And Asc(char) < 123) Or (Asc(char) > 47 And Asc(char) < 58) Or (Asc(char) = 95) Or (Asc(char) = 45))
Then
ContainsBadChar = "0"
Else
ContainsBadChar = "1"
Exit For
End If
Next
End Function
Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined.
Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.
More Posts - Website
2011-03-16, 06:30
Auf http://www.herber.de/forum/archiv/616to620/t617816.htm habe ich heute eine einfache Möglichkeit gefunden, die Fundstellen für ein bestimmtes Zeichen in einer Zelle zu zählen.
=LÄNGE(A1)-LÄNGE(WECHSELN(A1;".";""))
Die Funktion ermittelt die Länge des Strings ohne Vorkommen von ‚.‘ und zieht diese von der Länge mit ‚.‘ ab. Was übrig bleibt ist die Anzahl der ‚.‘ in Zelle A1.
Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined.
Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.
More Posts - Website
Schlagwörter:
Excel Category:
MS Office & VBA |
Kommentare deaktiviert für Excel: Zahl von bestimmten Zeichen in einer Zelle