Ich habe seit langen ein Problem, den ich nicht lösen kann.
Ich habe einen Code und der läuft bis zu einem Punkt durch. Danach kommt ein Debugg Fehler
Wenn ich dann auf Debuggen gehe , danach mit F5 weiter gehe, dann läuft der Code durch.
Hallo Herr S.,
drei Bemerkungen zu Ihrer Frage:
1. Das sind zu wenige Informationen.
Mir fehlt:
* wsKore_Import ist eine Variable, die auf eine Datei verweist?
* wie ist die Variable r deklariert?
* wie ist die Variable i2 deklariert?
* wenn Sie mit dem Mauszeiger über strBLATTNAME fahren – welchen Wert hat diese Variable?
* Gibt es das Blatt strBLATTNAME in der Datei wsKORE_IMPORT?
* Mit was ist strVON gefüllt?
2. Mich wundert:
Set r = …
Sie verweisen auf einen Bereich.
Anschließend:
For each r in r.cells
Hier beißt sich die Katze in den Schwanz:
Sie verwenden die Variable r (genauer: alle Zellen dieses Bereichs), die Sie gefüllt haben und weisen ihr ständig neue Zellen zu, verwenden aber immer noch r.cells. Autsch!
Verwenden Sie hier bitte eine andere Variable:
Dim r2 as Range
For each r2 in r.Cells
Der Punkt vor Cells ist sicherlich weiter oben zugewiesen, oder? With irgendetwas …?
3. Ich hatte auch schon Fälle, in denen ein Makros sehr merkwürdige Dinge machte (ich glaube, das hatte auch mal, dass es beim Debuggen funktionierte und beim Durchlauf (bei mir) IMMER abstürzte. Lösung: Ich habe den Code verändert! SO lange, bis es lief. Und dann mit den Schultern gezuckt.
In Ihrem Beispiel:
Arbeiten Sie mit einer „Zwischenvariable“. Beispielsweise
Dim xlBlatt As WorkSheet
Set xlBlatt = wsKORE_IMPORT.WorkSheets(strBlattname)
Du als Excel Spezialist kannst mir vielleicht einen Tip geben wie ich folgende Aufgabe löse:
In einem Excel basierten Tool mit VBA Makros soll erkannt werden, wenn die Entwicklertools / Visual Basic aufgerufen werden.
Gibt es dafür eine Möglichkeit eben den Vorgang in einem Makro abzufangen?
Hallo Bernhard,
ich wüsste nicht, wie man das abfangen kann.
Gegenfrage: warum willst du das abfangen?
Wenn du VBA verhindern willst, kannst es über die Gruppenrichtlinien sperren.
Eigene Projekte schütze ich manchmal mit Kennwort gegen Zugriff/Einsicht (ich weiß – das kann man knacken)
Du könntest den Anwender das Symbol und die Tastenkombination [Alt] + [F11] wegnehmen – aber pfiffige Anwender finden sicherlich einen Weg …
Liebe Grüße
Rene
####
Hallo Rene,
ich habe doch noch ein wenig experimentiert, nachdem mir ChatGPT doch noch einen brauchbaren Hinweis gegeben hat.
Die Tastenkombination ALTF11 wird hierbei abgefragt.
Ich schließe mal daraus, dass man auch den Klick im Menü erfassen kann, aber wie ……………. Das habe ich noch nicht gefunden.
####
Hallo Bernhard,
du wirst scheitern!
Das habe ich auch für einen Moment überlegt – die Tastenkombination [Alt] + [F11] zu verbiegen und mitzuprotokollieren. (Stichwort: OnKey)
Und ja: du könntest das Symbol Entwicklertools / Visual Basic durch ein anderes ersetzen oder den Befehl abfangen.
Allerdings gelangt man auch in VBA-Editor über das Kontextmenü des Tabellenblattes:
und über Ansicht / Makros / Makros anzeigen / Bearbeiten.
Oder Entwicklertools / Makros.
Oder du fügst dir das Symbol „Makros anzeigen“ in die Symbolleiste für den Schnellzugriff.
Oder: du öffnest Excel mit einer leeren Arbeitsmappe. Wechselst nach VBA. Dann öffnest du eine Datei mit Makros. Und kannst jetzt mit [Alt] + [Tab] oder dem zweiten Excelsymbol in der Taskleiste nach VBA wechseln.
Das heißt: du müsstest diese Aktionen generell in Excel abfangen. Also: Excel überwachen.
Auch hier wirst du an Grenzen stoßen, weil es Anwender gibt, die Excel über das Kontextmenü auf dem Desktop öffnen: Neu / Microsoft Excel Arbeitsblatt.
Du wirst an Grenzen stoßen … Maximal 90% der Fälle erreichen …
Liebe Grüße
Rene
####
Hallo Rene,
nochmal vielen Dank. Ich habe das Thema at Acta gelegt. Ich brauche es ja auch nicht, mir kam halt nur so eine Idee.
Wünsche Dir weiterhin alles Gute und viel Spaß mit Excel & Co.
Die benutzerdefinierten Listen in Excel sind bekannt:
Umso erstaunlicher gestaltet sich das Objekt, wenn man mit VBA darauf zugreifen möchte:
Die Anzahl der Elemente erhält man mit dem Befehl CustomListCount :
MsgBox Application.CustomListCount
Ein neues Element wird hinzugefügt mit einem Array mit dem Befehl addCustomList:
Dim meineListe As Variant
meineListe = Array("a", "b", "c")
Application.AddCustomList meineListe
Sehr angenehm: existiert der Eintrag bereits, ist weder Fehlermeldung noch ein zweiter Listeneintrag die Folge – alles bleibt beim Alten.
Auf ein Element kann per Nummer zugegriffen werden. Das Element wird als Array ausgegeben. Die Elemente können per Schleife ermittelt werden (Achtung: das erste Element trägt die Nummer 1!):
In einer Pivottabelle sollen die Kalenderwochen als Spaltenbeschriftung verwendet werden. Eigentlich kein Problem. Jedoch beim Jahreswechsel soll natürlich gezählt werden: 51, 52, 53, 1, 2, 3, …
Würd man die Standardsortierung der Pivottabelle verwenden, wäre die Zählung: 1, 2, 3, … 52, 53.
Aber man kann doch benutzerdefiniert sortieren. Fügt man eine benutzerdefinierte Liste per Programmierung in Excel ein, beispielsweise so:
Sub BenutzerdefinierteListeEinfuegen()
Dim customList As Variant
Dim i As Integer
' Definiere die benutzerdefinierte Liste von 40 bis 53 und dann 1 bis 13
customList = Array("40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14")
' Füge die benutzerdefinierte Liste in Excel ein
Application.AddCustomList ListArray:=customList
End Sub
wird die Liste immer nach laufender Kalenderwoche sortiert.
Allerdings frage ich mich: Was passiert, wenn dieses Makro zwei Mal aufgerufen wird? Ein Fehler, weil die Liste schon vorhanden ist? Eine weitere Liste?
Die Antwort: gar nichts: Excel übergeht diese Liste – sie ist schon da. Sehr schön – Excel hat heute mal nicht genervt.
Gestern erreichte mich folgende Mail, über die geschmunzelt habe – meine Hilfe war wohl ein Anschubsen (es ging um Visio-Programmierung):
Hallo Rene,
nein, nicht Du, sondern ich stand auf dem Schlauch!… Da ich mir von Mittendrin meines Programms ein Stück genommen hatte, gab es keine Zuordnung zur aktiven Page. Leider war auch die Fehlermeldung wenig hilfreich….
Problem gelöst – auf Deinen Seite hatte ich geschaut – war ja alles klar…., aber eben…
Danke trotzdem ganz toll! Aber das ist so was. Wenn ich mich an meine Dienstzeit erinnere – so haben wir viele Probleme so gelöst, dass man den Anderen alles erklärt hat. Und das hat oft gereicht, um das Problem zu lösen. Der Gesprächspartner musste oft gar nichts sagen, oder irgend eine dumme Frage stellen. Das hat die Gehirnwindungen zu anderen Pfaden veranlasst… Deine Sätze haben mir eben verraten, dass „fast alles richtig ist“ …. Dann ging es ganz schnell.
schon mal versucht per VBA eine bedingte Formatierung zu setzen. Das geht ohne Probleme, solange man keine Formeln eingibt, die eine Funktion enthalten, z.B.
JAAA, das funktioniert … aber schick die Datei mal an jemanden, der englische Sprache eingestellt hat. Das funktioniert das dann nicht mehr!
Im VBA-Code muss ich, wenn als Sprache Deutsch eingestellt habe, die Formel auch mit dem deutschen Funktionsnamen eingeben, ansonsten ignoriert Excel die Bedingung . WTF
Schon vor dem gleichen Problem gestanden/gesessen? Ich habe aktuell das Problem, dass ich für eine Firma arbeite, deren Mitarbeiter unterschiedliche Sprachen in Office eingestellt haben, eigentlich habe ich keine Lust jetzt immer prüfen zu müssen welche Sprache gerade aktuell ist und entsprechend die Formeln zu setzen….
Allerdings: wenn die Amis Komma als Trennzeichen eingeschaltet haben, dann läuft das Teilchen auch an die Wand. Übrigens: ebenso die Formeln in der Datenüberprüfung.
Aber mit welchem Zeithorizont muss ich rechnen, dass Excel nur noch als Online-Version ausgeführt werden kann oder VBA ganz aus Excel verschwindet. Ich denke auch wenn ich mit VB.Net arbeite, wird das bei der Online-Version nicht gehen, da ich eine Lokal-Installation der Exe benötige.
Wenn ich VB.net innerhalb der Excel schreibe erhalte ich wieder ein Excel mit Makro.
Aktuell kann ich bei lokal-Installationen sagen, dass alle Online-Excel lokal geöffnet werden kann. Somit ist es mir egal, ob die Excel-Datei online oder lokal ist.
Die Programmiersprache JavaScript /Type Script, M, Dax kenne ich nicht.
Welche Sprache lässt zu, dass ich eine «chaotische» Excel-Datei habe (keine Strukturierten Daten) und bei Änderungen innerhalb von Zellen oder aktivieren eines Arbeisblattes, oder doppelklick usw. aktiv eingreifen kann.
Ich interpretiere die Aktion eines Users und leite diesen.
Somit schaffe ich immer intelligentere Lösungen innerhalb vom Excel und habe, das Ziel, dass der Anwender in der Schweiz immer weniger Fehler machen kann uns somit seine Daten an das Bundesamt für Statistik besser werden.
Ein Beispiel ist, wenn ein User Sagt, dass ich ein Gebäude besitze, dann werden die Abschreibung und Zinsen geprüft und mit der Grösse des Betriebs in relation gesetzt. Ebenso werden die Mieten in relation der Betriebsgrösse gesetzt und alles interpretiert. Dann wird analysiert in welcher KST die Anlagen erfasst sind und welche übrigen Anlagenutzungskosten diese Kostenstellen tragen und wie die Umlagen gesteuert sind. Wenn irgend was in dieser Konstellation nicht korrekt ist, dann wird der Anwender entsprechend hingeführt, was falsch sein könnte. Das ist zu komplex dies innerhalb einer Formel im Excel durch zu führen. Im VBA ist das ein grosser Code welcher viele logische Prüfungen durchläuft, und dann merkt, wo der Fehler sein könnte. Das ganze wird bei einigen Zellenänderungen angesteuert, damit diese Prozedur nicht immer durchläuft. So muss der User nicht daran denken ein Knopf zu drücken, sondern der Export an das Bundesamt für Statistik wird gesperrt wenn noch Fehler vorhanden sind.
Und ehrlich gesagt, ich habe keine Ahnung wie ich das anders Lösen sollte.
Wenn ich eine saubere Excel-Datei als Quelle nutze, die Daten automatisch systematisch auslesen, interpretieren, umwandeld, darstellen uws möchte sehe ich keine Herausforderung mit den anderen Programmeirsprachen, wenn man sie behersrscht (ist bei mir nicht der Fall) Freundliche Grüsse
###
Hallo Herr S.,
die Frage nach dem “wie lang“ müssen Sie an Microsoft stellen. Aber ich fürchte, dass Sie auch dort keine verlässliche Antwort erhalten.
Ich verlasse auf die Aussage eines Microsoft-Mitarbeiters auf der letzten Excelkonferenz in Sofia, der uns bestätigte, dass Excel NICHT VBA entfernen wird. Ich kann mir auch nicht vorstellen, dass das Desktop-Excel verschwinden wird.
Ein großer Teil meiner Einnahmen bestreite ich mit VBA – ich lebe davon und werde es wohl auch in Zukunft.
Ich habe gerade Ende letzten Jahres dem Gartenbaureferat der Stadt München vorgeschlagen, eine Lösung, die auf VBA basiert, einzuführen, damit sie ihr Bestellwesen vereinheitlichen können. Da die Stadt München in puncto Makros sehr restriktiv ist und prüfen müssen, dauert der Prozess wohl noch ein bisschen. Alle anderen Programmiersprachen würde nicht das Ergebnis erzielen, das sie haben wollen.
Und: so wie mir geht es vielen anderen auch. Ich bin zuversichtlich, dass wir noch sehr, sehr lange Excel als Desktop-Lösung haben werden und dass und VBA erhalten bleibt.
VBA-Schulung. Eine Teilnehmerin möchte mehrere Diagramme per VBA nach PowerPoint von Excel kopieren:
Wir beginnen.
Wir setzen einen Verweis auf die PowerPoint-Objektbibliothek.
Wir greifen auf PowerPoint zu und lassen das Programm anzeigen:
Sub PowerPointZugriff()
Dim ppApp As PowerPoint.Application
Dim ppPräsentation As PowerPoint.Presentation
Dim ppFolie As PowerPoint.Slide
Dim xlBlatt As Worksheet
Dim i As Integer
Set xlBlatt = ActiveSheet
Set ppApp = New PowerPoint.Application
ppApp.Visible = msoTrue
Klappt.
Wir erstellen eine neue Präsentation:
Set ppPräsentation = ppApp.Presentations.Add
Klappt.
Wir fügen eine neue Folie ein – eine Fehlermeldung ist die Folge:
Ich erinnere mich: VBA schlägt zwar die Methode AddSlide mit zwei Parametern vor:
Korrekt wäre jedoch die Methode Add:
Set ppFolie = ppPräsentation.Slides.Add(1, ppLayoutTitleOnly)
Und damit funktioniert das Programm:
Sub PowerPointZugriff()
Dim ppApp As PowerPoint.Application
Dim ppPräsentation As PowerPoint.Presentation
Dim ppFolie As PowerPoint.Slide
Dim xlBlatt As Worksheet
Dim i As Integer
Set xlBlatt = ActiveSheet
Set ppApp = New PowerPoint.Application
ppApp.Visible = msoTrue
Set ppPräsentation = ppApp.Presentations.Add
For i = 1 To xlBlatt.ChartObjects.Count
Set ppFolie = ppPräsentation.Slides.Add(i, ppLayoutTitleOnly)
ppFolie.Shapes(1).TextFrame.TextRange.Text = "Überschrift " & i
xlBlatt.ChartObjects(i).Copy
ppFolie.Shapes.Paste
Next i
' Achtung: nicht AddSlides sondern Add
Excelstammtisch. Wir diskutieren ChatGPT und copilot. Ich zeige, dass die Lösungen manchmal nicht korrekt sind.
Problem: ich möchte mit VBA ermitteln, ob in einer Zelle eine Datenprüfung liegt. ChatGPT antwortet mir:
Sub CheckDataValidation()
Dim rng As Range
Dim validationType As XlDVType
' Definiere die Zelle, die du überprüfen möchtest
Set rng = Worksheets("DeinBlatt").Range("A1")
' Überprüfe, ob die Zelle eine Datenprüfung hat
If rng.Validation.Type <> xlValidateNone Then
' Erhalte den Typ der Datenprüfung
validationType = rng.Validation.Type
' Zeige den Typ der Datenprüfung in der Konsole an (kann angepasst werden)
MsgBox "Die Zelle hat eine Datenprüfung vom Typ: " & validationType
Else
' Wenn keine Datenprüfung vorliegt
MsgBox "Die Zelle hat keine Datenprüfung."
End If
End Sub
Ich teste:
Ich beschwere mich:
ChatGPT schlägt
xlValidateCustom
vor. Das Ergebnis, wenn keine Datenprüfung in der Zelle vorhanden ist:
Ich frage copilot. Die Antwort:
If Not Cells(1, 1).Validation Is Nothing Then
MsgBox "Die Zelle hat eine Datenüberprüfung."
Else
MsgBox "Die Zelle hat keine Datenüberprüfung."
End If
Das Ergebnis:
Das Makro meldet immer, dass die Zelle eine Datenüberprüfung hat.
Sämtliche Antworten sind falsch oder fehlerhaft.
Alexander lässt nicht locker. Er findet nach mehreren Versuchen eine Lösung bei ChatGPT:
Sub CheckNoDataValidationCriteria2()
Dim targetCell As Range
Set targetCell = Selection ' Hier die Zelle angeben, die du überprüfen möchtest
' Überprüfen, ob die Zelle eine Datenprüfung hat
If Not targetCell.Validation Is Nothing Then
' Überprüfen, ob spezifische Kriterien festgelegt wurden
Dim validationFormula As String
On Error Resume Next
validationFormula = targetCell.Validation.Formula1
On Error GoTo 0
If validationFormula = "" Then
MsgBox "Die Zelle hat keine spezifischen Datenprüfungskriterien (jeder Wert ist zugelassen)."
Else
' Kriterien in Zelle D1 schreiben
Range("B2").Value = "Datenprüfungskriterien: " & validationFormula
MsgBox "Die Zelle hat Datenprüfung mit spezifischen Kriterien. Die Kriterien wurden in Zelle D1 geschrieben."
End If
Else
' Wenn keine Datenprüfung vorhanden ist, Zelle D1 löschen
Range("B2").ClearContents
MsgBox "Die Zelle hat keine Datenprüfung."
End If
End Sub
Sie funktioniert: für Zellen mit und für ohne Datenüberprüfung:
Die Lösung funktioniert; der Denkansatz ist jedoch nicht korrekt:
kann ausgeführt werden oder nicht. Würde sie einen Fehler liefern (also keine Datenüberprüfung ist vorhanden), wird sie übergangen und validationFormula bleibt leer („“). Ich hätte es mit err.number überprüft.
Aber okay – ich gebe zu: man kann mit ChatGPT / copilot eine funktionierende Lösung finden. Wenn man beharrlich ist.
Danke an Alexander Vogelmann für seine Beharrlichkeit.
Ich habe genau dieses Problem, mit dem Löschen verhindern von einzelnen Tabellenblättern.
Die hier gezeigte Lösung erscheint recht logisch und einfach und wollte es auch in meiner Datei ausprobieren.
Habe den Code so übernommen (im Case-Bereich dann die Tabellenblätter die Benennungen angepasst)
Also:
Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
On Error Resume Next
Select Case Sh.CodeName
Case "tbl_Unternehmen", "tbl_Standorte", "tbl_Zuordnung1", "tbl_Organisationseinheit", "tbl_Zuordnung2", "tbl_Geschaeftsprozesse", "tbl_Zuordnung3", "tbl_Uebungstyp", "tbl_Szenario", "tbl_Verantwortlich", "tbl_Uebungsplanung", "tbl_Zuordnung4"
MsgBox "Bitte löschen Sie nicht das Tabellenblatt """ & Sh.Name & """!", vbCritical
ThisWorkbook.Protect
End Select
End Sub
Nur bei Sh.CodeName gibt es mir für CodeName immer „DieseArbeitsmappe“ an, statt den den eigentlichen Tabellenname.
Was mache ich falsch?
Vielen Dank für einen Tipp
Freundliche Grüsse
####
Hallo Herr W.,
Sie sind in „DieseArbeitsmappe“?
Sie sind im Ereignis: SheetBeforeDelete?
Testen Sie mal am besten in einer anderen, leeren Datei):
Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
MsgBox Sh.CodeName
MsgBox TypeName(Sh)
End Sub
Bei mir wird IMMER der Codename des Blattes angezeigt.
habe was „Schönes“ in den Excel – Dateieigenschaften gefunden:
Als ich ein Addin an mene Kollegen verteilt habe, dem ich über den Windows – Dialog (Rechtsklick) „Dateieigenschaften“ > „Details“ einige zusätzliche Infos wie Titel, Betreff, Kommentare, etc. verpasst hatte, konnten diese das Addin nicht laden oder aktivieren. Beim direkten Start des Addins per Doppelklick kamen seltsame Fehlermeldungen wie „Excel kann auf die Datei ‚C:\Users\awa\AppData\Roaming\Microsoft\AddIns\Pro….xlam‘ nicht zugreifen. Dies kann mehrere Gründe haben:
– Name/Pfad nicht vorhanden
– Dokument wird von anderem Programm verwendet
– Name der Arbeitsmappe die gespeichert werden soll, ist identisch zu dem Namen eines anderen Dokuments, welches schreibgeschützt ist“
Kann nicht sein – als ich das Addin zuletzt getestet hatte, lief alles noch?!
Also habe ich mal dumm die von mir eingetragenen Dateieigenschaften Stück für Stück wieder rausgelöscht – und kam der Sache auf die Spur:
Bei „Revisionsnummer“ dürfen nur Zahlen eingetragen werden, keine Buchstaben oder Kombinationen mit Buchstaben!
Auch bei „normalen“ .XSLX – Dateien funktioniert diese Blockade (durch Eingabe von Buchstaben in die Eigenschaft „Revisionsnummer“) zuverlässig, nur die Fehlermeldungen sind dann andere:
„Wir haben ein Problem bei einigen Inhalten in ….XLSX erkannt. Sollen wir soviel wie möglich wiederherstellen? Wenn Sie der Quelle dieser Arbeitsmappe vertauen, klicken Sie auf ‚Ja'“
Wenn man das macht und die „Reparatur“ durchführt, hat die Datei anschließend die Revisionsnummer 1. Offensichtlich wird diese Eigenschaft von Excel selbst verwaltet / genutzt.
Nur schade, dass Windows ein Editieren durch den user zulässt, was zu den o.a. irritierenden Meldungen führt…
Liebe Grüße aus dem hohen Norden – andreas
####
Vielen Dank, Andreas, für den wertvollen Hinweis – bei XLAM-Dateien erhalte ich auch die Fehlermeldung – XLSX-Dateien kann ich jedoch problemlos öffnen. Dennoch: Vorsicht vor der Revisionsnummer.
Ein HALLO und DANKESCHÖN für deinen Support über die Alpen,
bei uns bläst der Wind bei ca. 19 Grad und im WWW finde ich nicht die korrekte Lösung, wie man in einem Listenfeld eines Formulars die Spaltenkopfinformationen setzt.
Also unter:
Private Sub UserForm_Initialize()
findest du schon Mal den VBA, auch die Zeile ColumnHeads = True
aber das i-Tüpfelchen nicht, wie man die Daten aus dem Bereich A1:D1 des Tabellenblattes „Material Data“ übernimmt, fehlt mir noch. Die Lösungen des WWW führten leider nicht zum Erfolg. Dankeschön & Gruß Jürgen
Hallo Jürgen,
so geht es:
du musst einen Bereich definieren (mit Überschrift).
Und die Adresse (also $A$1:$D$14) an die RowSource übergeben.
Leider verlangt VBA die Adresse vom aktiven Blatt. Also muss man wechseln – möglicherweise am Ende wieder zurück:
With Me.LB_Entries
.ColumnHeads = True
Worksheets(„Material Data“).Activate
.RowSource = xlBereich.Address
End With
Den kompletten Code in deinem Beispiel findest du in der zweiten Userform:
Set xlBereich = Worksheets("Material Data").Range("A1").CurrentRegion
Set xlBereich = xlBereich.Offset(1, 0).Resize(xlBereich.Rows.Count - 1, xlBereich.Columns.Count)
With Me.LB_Entries
.ColumnHeads = True
Worksheets("Material Data").Activate
.RowSource = xlBereich.Address
End With
Ich zeichne mit dem Befehl „Aktionen aufzeichnen“ mit OfficeSkript den Befehl „benutzerdefiniertes Zahlenformat #.##0,00 „km“ auf:
Das Skript sieht so aus:
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Set number format for range C2:C6 on selectedSheet
selectedSheet.getRange("C2:C6").setNumberFormatLocal("#,##0.00 \"\"km\"\"");
}
Ich lasse es abspielen:
Analog: ich zeichne eine Summe auf. Der Code:
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Set range C8 on selectedSheet
selectedSheet.getRange("C8").setFormulaLocal("=SUM(C2:C7)");
}
Das Ergebnis:
In die Zelle wird SUM statt SUMME eingetragen – Excel schafft die Lokalisierung noch nicht. Da muss Microsoft noch einmal ran …
Ich suche per VBA eine Kostenstelle in einer anderen Liste und benötige die Zeilennummer, weil ich aus dieser Zeile mehrere Informationen auslesen will:
So steht beispielsweise der Wert von A2 (4711) in Zeile 5.
Sub Kostenstellensuche()
Dim strKostenstelle As String
Dim lngZeile As Long
strKostenstelle = ActiveSheet.Range("A2").Value
If Application.WorksheetFunction.CountIf(ActiveSheet.Columns("D"), strKostenstelle) > 0 Then
lngZeile = Application.WorksheetFunction.Match(strKostenstelle, ActiveSheet.Columns("D"), 0)
MsgBox lngZeile
End If
End Sub
Das Programm läuft leider an die Wand:
Die Match-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden.
Der Grund: da ich nicht sicher bin, ob die Kostenstelle immer als Zahl vorliegt, habe ich sie in einer String-Variablen gespeichert.
Für CountIF (ZÄHLENWENN) stellt dies kein Problem dar; allerdings für Match (Vergleich). Also prüfen:
Sub Kostenstellensuche()
Dim strKostenstelle As String
Dim lngZeile As Long
strKostenstelle = ActiveSheet.Range("A2").Value
If Application.WorksheetFunction.CountIf(ActiveSheet.Columns("D"), strKostenstelle) > 0 Then
If IsNumeric(strKostenstelle) Then
lngZeile = Application.WorksheetFunction.Match(CLng(strKostenstelle), ActiveSheet.Columns("D"), 0)
Else
lngZeile = Application.WorksheetFunction.Match(strKostenstelle, ActiveSheet.Columns("D"), 0)
End If
MsgBox lngZeile
End If
End Sub
Der Auftrag hörte sich einfach an: Der Kunde wollte ein Add-In, welches alle Dateien aus allen Unterordnern vom firmeneigenen Sharepoint herunterlädt und in bestimmten Zellen Werte einfügt.
Der Knackpunkt war: Sharepoint!
Ich habe lange getüftelt, wie ich „auf den Sharepoint komme“, wie ich die Ordner und Unterordner und die dort befindlichen Dateien auslesen könne. Und herunterladen und bearbeiten.
Irgendwann kam mir die Idee: nicht mit VBA und DIR oder den FileScription-Objekt auf den Ordner losgehen, sondern mit Power Query! Damit kann man leicht alle Dateien aller Unterordner auslesen und auflisten. Der Befehl
SharePoint.Files
macht es möglich. Dieses Power Query-Skript kann leicht mit VBA aufgerufen werden (der Makrorekorder zeigt, wie das funktioniert:
Nein – das erfreut mich gar nicht. Den ganzen Tag habe ich VBA in Excel programmiert – getestet, angepasst, verändert, getestet, … Plötzlich funktioniert es nicht mehr. Ein Modul ist nicht mehr erreichbar. Ich versuche es zu retten; zu exportieren:
Modul nicht gefunden.
Keine Chance – kein Zugriff auf das Modul und den Code.
Also noch einmal von vorne … Zum Glück hatte ich Datei vom Vortag und zum Glück waren es nicht sehr viele Funktionen …
Man muss immer aufpassen! Ganz genau hinschauen! Mal eben schnell – das geht einfach nicht!
Ich wollte nur „mal schnell etwas probieren“. Mehrere Mails über Outlook aus Excelversenden an die Mailadresse, die in einer Liste stehen.
Also mal schnell etwas Code getippt (mit einem Verweis auf die Microsoft Outlook-Bibliothek):
Sub MailVersenden()
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim i As Integer
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
For i = 2 To Range("A1").CurrentRegion.Rows.Count
With olMail
.To = Range("B" & i).Value
.Subject = "Diese Mail ist völlig überflüssig"
.Body = "Hallo " & Range("A" & i).Value & "," & vbCr & vbCr & "Nicht wundern - das ist nur eine Testmail" & vbCr & vbCr & "Gruß"
.Send
End With
Next i
End Sub
Und der Test – liefert einen Fehler. Bei der ZWEITEN Mail:
Outlook kennt mindestens einen Namen nicht.
Seltsam! Hinschauen – überlegen – stimmt:
Ich muss INNERHALB der Schleife eine neue Mail erzeugen – das heißt: für jede Zeile wird eine neue Mail versenden:
Sub MailVersenden()
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim i As Integer
Set olApp = New Outlook.Application
For i = 2 To Range("A1").CurrentRegion.Rows.Count
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = Range("B" & i).Value
.Subject = "Diese Mail ist völlig überflüssig"
.Body = "Hallo " & Range("A" & i).Value & "," & vbCr & vbCr & "Nicht wundern - das ist nur eine Testmail" & vbCr & vbCr & "Gruß"
.Send
End With
Next i
End Sub
DANN funktioniert es auch. Beim nächsten Mal – halt nicht so schnell!
In einer Excelmappe (Prinz) befinden sich mehrere Verknüpfungen auf andere Dateien:
Da diese Datei per VBA weiter verarbeitet werden soll, müssen die Verknüpfungen gelöscht werden. Beispielsweise so:
Dim arrLinks As Variant
Dim i As Integer
arrLinks = ThisWorkbook.LinkSources(xlLinkTypeExcelLinks)
If Not VBA.IsEmpty(arrLinks) Then
For i = 1 To UBound(arrLinks)
'MsgBox ThisWorkbook.LinkSources(xlLinkTypeExcelLinks)(i)
ThisWorkbook.BreakLink Name:=arrLinks(i), Type:=xlLinkTypeExcelLinks
Next
End If
Die Verknüpfungen werden gelöscht – bis auf eine !?!
Ich schaue nach: diese Verknüpfung liegt auf einem geschützten Blatt.
Was mich irritiert ist, dass keine Fehlermeldung die Folge war: VBA übergeht einfach die Tatsache, dass die Verknüpfung nicht gelöst werden kann. Also hebt man den Blattschutz auf (und merkt ihn sich vorher):
Dim blnBlattStatus() As Boolean
i = ThisWorkbook.Worksheets.Count
ReDim blnBlattStatus(i)
For i = 1 To ThisWorkbook.Worksheets.Count
blnBlattStatus(i) = ThisWorkbook.Worksheets(i).ProtectContents
If ThisWorkbook.Worksheets(i).ProtectContents = True Then
ThisWorkbook.Worksheets(i).Unprotect
End If
Next
Und setzt ihn am Ende wieder:
For i = 1 To ThisWorkbook.Worksheets.Count
If blnBlattStatus(i) = True Then
ThisWorkbook.Worksheets(i).Protect
End If
Next
Der Auftrag: der Kunde möchte in eine Excelliste Informationen eintragen, beispielsweise die Namen der Tabellenblätter, die mit einem Klick auf einen Button erzeugt werden. Auf den Blättern werden Verknüpfungen zu den anderen Zellen hergestellt, auf einem weiteren Blatt wird eine Formel aktualisiert. So weit so gut – ich teste – klappt:
Der Kunde testet und schickt meine Mail mit dem Hinweis, dass „Nicht genügend Speicher“ vorhanden sei.
Seltsam – bei mir nicht.
Doch – wenn er 200 Tabellenblätter erzeuge, meldet Excel diesen Fehler nach Blatt Nummer 117.
Seltsam. Bei mir auch:
Der Fehler trat beim Erstellen der Formel auf. Zuerst dachte ich an Schwierigkeiten des Prozessors beim Erstellen so vieler Formeln. Oder vielleicht hatte ich die Objektvariablen nicht sauber „geputzt“. Oder es gab ein Geschwindigkeitsproblem:
Die Ursache war eine andere: Die Formel war schlicht zu lang. Mit der Funktion SUMMEWENNS sollten Berechnungen für jedes Tabellenblatt ausgeführt werden und diese Werte addiert werden. Ein teil der Formel (bei Blatt Nummer 116) ist hier zu sehen:
Also haben wir eine andere Lösung gesucht.
Und: mit Verlaub: ich bin nicht sicher, ob die fast 100 Monster-SUMMEWENNS auf dem Tabellenblatt die Datei mit den 200 Blättern nicht in die Knie gezwungen hätte …
Hallo Hr. Martin. Wie kann ich in VBA prüfen, ob ein Textfeld als Formularsteuerelement das mit einer Textmarke versehen ist (z Bsp.“TMThemaPos16″) leer ist. Laut Lokal-Fenster soll es ein String mit 5 Leerzeichen sein. Diese Prüfung wird aber ignoriert. Auch auf Empty prüfen bringt keinen Erfolg. Ich muss den Inhalt der Textmarken in eine Excel überführen, die stetig erweitert wird. Es können 30 Themen ins Formular eingetragen werden. Ab einer leeren Textmarke der Kategorie „Thema“, soll die Prozedur beendet werden. Vielen Dank für ihre Unterstützung.
###
Hallo Herr W.,
Wenn ich Ihr Problem richtig verstanden habe, würde ich das folgendermaßen lösen: Formularsteuerelemente haben keinen Namen und können deshalb nicht direkt angesprochen werden. Hinterlegen Sie bei der Eigenschaft „Tag“ einen Text. Durchlaufen Sie mit einer Schleife alle ContentControls und überprüfen Sie, ob das ContentControls(i) den Tag = „yxz“ hat. Wenn ja, dann können Sie den Text auslesen: ActiveDocument.ContentControls(1).Range.Text Ist das die Antwort auf Ihre Frage?
LG und ein schönes Wochenende
Rene Martin
###
Hallo Hr. Martin.
Ich hab das jetzt über die ASC-Funktion gelöst. Frage somit ab, welches ASCII Zeichen die Textmarke hat und wenn diese (in meinem) Fall 32 ist, dann ist sie leer.
Eigentlich wollte ich nur einen Befehl mit Office Skript aufzeichnen: formatiere den Text einer Zelle fett:
Geht nicht:
„Leider ist ein Problem aufgetreten. Wir können Ihr Skript nicht speichern. Unerwarteter Fehler beim Generieren und Speichern des Skripts. Klicken Sie auf „Aktionen aufzeichnen“, um erneut aufzuzeichnen.
Erstaunt starre ich auf den Bildschirm. Ich habe keine Ahnung, warum und woher das Problem rührt.
Ich habe ein Add-In für einen Kunden erstellt. Per Knopfdruck sollen Daten (eine Liste der Debitoren) in eine bestehende Datei importiert werden.
Ich entscheide mich für VBA, weil ich so die Datei und die Daten prüfen kann.
Klappt:
Ich erhalte die Rückmeldung:
„Wir haben nun das File beim ersten Kunden getestet und dabei ist uns aufgefallen, dass aus den Debitor Stammdaten leider nur Einträge mit Kürzel exportiert werden und jene ohne nicht.“
Ich wundere mich. Ich importiere doch alle Daten:
xlBlattDebitoren.Range("A1").CurrentRegion.Copy Destination:=ThisWorkbook.Worksheets("Debitoren").Range("A1") ' -- kopiere die Daten
Ich ahne wo das Problem ist.
Wenn die ursprüngliche Liste gefiltert war, werden nur die sichtbaren Daten kopiert.
Also muss ich vorher prüfen, ob ein Filter eingeschaltet war. Falls ja, wird er entfernt:
' -- schalte mögliche gesetzten Filter aus
If xlBlattDebitoren.FilterMode = True Then
xlBlattDebitoren.Range("A1").AutoFilter
End If
Könne Sie mir nochmals sagen, wo ich die Funktion finde wie gross eine Excel-Datei ist
Anzahl Register
Benötigte Zellen
Anzahl Formeln
Danke
Freundliche Grüsse
####
Hallo Herr S.,
Sie meinen die VBA-Befehle, mit denen man das herausbekommt?
– Anzahl der Tabellenblätter (Register):
ThisWorkbook.Sheets.Count
– Benötigte Zellen:
UsedRange
oder:
Range(„A1“).SpecialCells(xlCellTypeLastCell)
ist die letzte Zeile. Sie hat die Eigenschaften Row und Column – also die Nummer der Zeile und die Nummer der Spalte. Noch weiter rechts, bzw. unten gibt es nichts.
– Anzahl Formeln
dafür gibt es keinen einfachen Befehl. Entweder Sie durchlaufen alle verwendeten Zellen in UsedRange
Option Explicit
Sub AnzFormeln()
Dim xlBlatt As Worksheet
Dim xlZelle As Range
Dim lngAnzahl As Long
Set xlBlatt = ActiveSheet
For Each xlZelle In xlBlatt.UsedRange
If xlZelle.HasFormula Then
lngAnzahl = lngAnzahl + 1
End If
Next
MsgBox „Anzahl der Zellen mit Formeln: “ & lngAnzahl
Für einen SAP-Upload soll eine Textdatei erzeugt werden. Mehrere Zeilen mit unterschiedlichen Informationen sollen zusammengebaut werden und in eine Textdatei geschrieben werden. Jede Zeile wird mit einem Zeilenumbruch beendet. Ich verwende vbCrLf.
Da nun die letzte Zeile auch einen Zeilenumbruch hat, überlege ich ihn zu löschen. Ich überprüfe das letzte Zeichen – falls es nicht um vbCrLf handelt, wird es gelöscht. Jedoch:
' -- entferne den letzten Zeilenumbruch
If Right(strExportString, 1) = vbCrLf Then
strExportString = Left(strExportString, Len(strExportString) - 1)
End If
funktioniert nicht! Klar doch: vbCrLf sind ZWEI Zeichen: Wagenrücklauf + Zeilenschaltung. Klar: ich muss überprüfen, ob die letzten BEIDEN Zeichen vbCrLf entsprechen. Und dann löschen.
Folgender Befehl funktioniert dann:
' -- entferne den letzten Zeilenumbruch
If Right(strExportString, 2) = vbCrLf Then
strExportString = Left(strExportString, Len(strExportString) - 2)
End If
Manchmal bin ich erstaunt, was Firmen (beziehungsweise Menschen, die dort arbeiten), sich so alles wünschen. Beispielsweise folgender Wunsch:
Firmenweit existiert ein Excel-Add-In:
Der XML-Code des Ribbons sieht folgendermaßen aus:
Nun – so lautete der Wunsch – soll ich ein zweites Add-In erstellen. Allerdings soll es sich in das erste einklinken. Also die gemeinsame Registerkarte „Schneewittchen und die sieben Zwerge“ verwenden.
Wie das?
Ich dachte nicht, dass das funktioniert.
Ein Dankeschön an Markus Hahner (www.hahner.de) – er hat mir die Lösung geliefert.
Das „Haupt-Addin“ muss mit einem Namespace im Element <customUI> versehen werden – hier:xmlns:x=“Zeichentrick“. Der Name (Zeichentrick) ist beliebig. Dieser Namespace (hier: „x“) wird im Element <tab> verwendet: idQ=“x:tabZwerge“. Dabei ist „tabZwerge“ natürlich auch beliebig.
Die andere Datei, also das andere Add-In, sieht dann folgendermaßen aus:
Also: Namespace in customUI und idQ sind identisch. Das Label, also die Beschriftung der Registerkarte, muss natürlich nicht wiederholt werden.
Und schon klinkt es sich ein:
Nachdem ich das Problem gelöst hatte, hatte ich doch glatt im Internet eine weitere Beschreibung gefunden:
Ich erstelle eine VBA-Funktion, bei der eine Zahlenreihe (normalerweise vier Ziffern) als Text gespeichert wird und mit Nullen davor aufgefüllt wird (meistens mit zwei Nullen). Der Funktion füge ich in VBA einen Kommentar hinzu, damit ich weiß, dass es sich um sechs Ziffern handelt:
' _ _ _ _ _ _
Die Funktion sieht folgendermaßen aus:
Ich teste und bin irritiert, dass das Ergebnis falsch ist.
Tatsächlich: Else ist grün! Der Unterstrich bewirkt einen Umbruch! Obwohl er im Kommentar steht!
Also verwende ich ein anderes Zeichen, um diese Leerzeichen zu versinnbildlichen:
Ich programmiere wirklich gerne. Aber ich habe immer meine liebe Not mit ungenauen Arbeitsanweisungen.
Ich soll per Programmierung ein Tabellenblatt als PDF ablegen.
Kein Problem. Allerdings muss ich nachfragen, wie das PDF-Dokument heißen soll.
BatchRecord-Filename-Datum-Uhrzeit
lautet die Antwort.
Ich vermute, dass er nicht Filename, sondern Blattname meint.
Und: in welcher Form das Datum und die Uhrzeit geschrieben wird, erklärt er mir auch nicht. Nun, er wird es mir sicherlich noch sagen.
Ich probiere:
strBlattname = "BatchRecord-" & ThisWorkbook.Name & Format(Date, "DD.MM.YYYY") & "-" & Format(Now, "HH:MM") & ".pdf"
' -- Export als PDF - in den gleichen Ordner, in dem sich die Vorlagendatei befindet
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\" & strBlattname, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Eine Fehlermeldung ist die Folge:
Das Dokument wurde nicht gespeichert.
Ein bisschen suchen und ich werden fündig: Natürlich darf kein Doppelpunkt im Dateinamen auftauchen. Also raus damit (bei der Uhrzeit) und warten, wie denn der Dateiname korrekt aussehen soll:
ChatGPT polarisiert: es begeistert, verblüfft und irritiert. Es irritiert durch falsche Antworten. Ein Beispiel gefällig?
Ich frage CHatGPT:
Mit welchem VBA-Befehl kann ich den Autor der Arbeitsmappe ermitteln?
Und erhalte folgende korrekte Antwort:
Sie können den Autor einer Arbeitsmappe in VBA mit folgendem Befehl ermitteln:
Dim strAuthor As String strAuthor = ThisWorkbook.BuiltinDocumentProperties(„Author“)
In diesem Beispiel wird die Eigenschaft „Author“ aus den „BuiltinDocumentProperties“ des aktuellen Workbooks abgerufen und in der Variablen „strAuthor“ gespeichert. Die „BuiltinDocumentProperties“ enthält alle standardmäßigen Dokumenteigenschaften wie Autor, Betreff, Schlagworte, etc.
Auch die Frage
Mit welchem VBA-Befehl kann ich ein Bild (beispielsweise C:\Test.jpg) in die rechte Kopfzeile einfügen?
wird korrekt beantwortet:
Sie können ein Bild in die rechte Kopfzeile einer Arbeitsmappe in VBA mit folgendem Befehl einfügen:
In diesem Beispiel wird die aktive Arbeitsblattseite ausgewählt und die Eigenschaft „RightHeaderPicture.Filename“ verwendet, um das Bild „C:\Test.jpg“ in die rechte Kopfzeile einzufügen. Wenn das Bild bereits eingefügt wurde, wird es durch das neue ersetzt.
Jedoch:
Meine Frage
Mit welchem VBA-Befehl kann ich den Mauszeiger ein- und wieder ausblenden?
wird nicht verstanden:
Sie können den Mauszeiger in VBA ein- und wieder ausblenden, indem Sie folgenden Befehl verwenden:
In diesem Beispiel wird die Eigenschaft „Cursor“ des Application-Objekts auf den Wert „xlWait“ gesetzt, um den Mauszeiger auszublenden. Nach der Ausführung des Codes wird die Eigenschaft „Cursor“ auf den Wert „xlDefault“ zurückgesetzt, um den Mauszeiger wieder einzublenden.
Bemerkung: xlWait und xlDefault sind Konstanten in der Microsoft Excel-Object Library, die Teil von VBA sind.
Nein, liebes ChatGPT – das ist nicht richtig. Mit der Eigenschaft Cursor kann man den Mauszeiger verändern, aber nicht ein- und ausblenden.
In Word soll per VBA ein Bild in eine Kopfzeile eingefügt werden. Blöde nur, dass es nach dem Einfügen „hinter den Text“ eingefügt werden soll. Ich habe lange gesucht, bis ich eine Lösung dafür gefunden habe:
Man muss das Bild in eine Form verwandeln (ConvertToShape). Dann funktioniert es:
Boah, wie schrecklich: eine Tabelle in Word (in der Kopfzeile) soll bearbeitet werden: in einer Zelle sitzt ein Bild – das soll gelöscht werden.
Mit dem VBA-Befehl Cells kann man in der Form Cells(1,1) auf die einzelnen Zellen zugreifen. Jedoch: sind Zellen verbunden, dann liefert beispielsweise Cells(2,2) ein Fehler, weil diese Zelle nicht vorhanden ist!
Die Lösung ist nicht elegant, aber sie funktioniert: Man durchläuft einfach alle Zellen – mit On Error Resume Next wird die fehlerhafte Zelle übergangen …
On Error Resume Next
Set wdDatei = wdApp.Documents.Open(Datei)
For i = 1 To wdDatei.Sections.Count
For j = 1 To wdKopf.Range.Tables.Count
Set wdTabelle = wdKopf.Range.Tables(j)
intZeilenAnzahl = wdTabelle.Rows.Count
intSpaltenAnzahl = wdTabelle.Columns.Count
For intZeile = 1 To intZeilenAnzahl
For intSpalte = 1 To intSpaltenAnzahl
If wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes.Count > 0 Then
Set wdMyRange = wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes(1).Range
wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes(1).Delete
End If
Next intSpalte
Next intZeile
Next j
Der Kunde will nicht nur, dass in einer Reihe von Excelvorlagen das Logo automatisiert, das heißt: mit VBA, ausgetauscht wird, sondern auch in Wordvorlagen.
Und damit beginnt wieder die Fummelei: liegt das Logo in der Kopfzeile oder in einer Tabelle in der Kopfzeile?
Eine Kopfzeile kann zur ersten Seite gehören, zum Objekt „gerade und ungerade Seiten unterschiedlich“ oder „gerade und ungerade Seiten nicht unterschiedlich“. Und diese wiederum an verschiedenen Abschnitten. Also sind eine Reihe von Schleifen nötig, um das alte Logo zu finden und zu löschen:
For i = 1 To wdDatei.Sections.Count
Set wdKopf = wdDatei.Sections(i).Headers(wdHeaderFooterPrimary)
If wdKopf.Range.InlineShapes.Count > 0 Then
Set wdMyRange = wdKopf.Range.InlineShapes(1).Range
wdKopf.Range.InlineShapes(1).Delete
wdKopf.Range.InlineShapes.AddPicture Bildpfad, , , wdMyRange ' Kopf ohne Tabelle
End If
For j = 1 To wdKopf.Range.Tables.Count
Set wdTabelle = wdKopf.Range.Tables(j)
intZeilenAnzahl = wdTabelle.Rows.Count
intSpaltenAnzahl = wdTabelle.Columns.Count
For intZeile = 1 To intZeilenAnzahl
For intSpalte = 1 To intSpaltenAnzahl
If wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes.Count > 0 Then
wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes(1).Delete
Set wdMyInShape = wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes.AddPicture(Bildpfad, , , wdMyRange)
End If
Next intSpalte
Next intZeile
Next j
Der Kunde ist noch immer nicht zufrieden. Die Kopfzeile soll um zwei Zeilenschaltungen nach unten verschoben werden. Also nicht so:
sondern so:
Der Befehl ist schnell gefunden. Allerdings amüsiert mich, dass die Kopfzeile vbCrLf als zwei Zeilenschaltungen interpretiert. Nun ja – ist ja okay so:
With Datei.Worksheets(j).PageSetup
.LeftHeader = vbCrLf & .LeftHeader
Max fragt mich, ob ich ihm helfe könne, per VBA einen PowerBI-Bericht anzuzeigen.
Er möchte in Excel über eine Schaltfläche ein Makro aufrufen, das einen Bericht öffnet, so dass die Anwenderinnen und Anwender den Bericht sehen können:
Er hat den Befehl „Shell“ im Internet gefunden. Richtig: Mit Shell rufe ich Programme auf, die ich nicht direkt über einen eingebundenen Verweis starten kann. Wir machen uns auf die Suche. Wie denn der Pfad auf seinem SharePoint lautet, will ich wissen. Den finden wir heraus. Er hat etwa die Form:
Ich erstelle ein Add-In für eine Firma. Es soll eine große Liste per Knopfdruck in Einzelteile zerlegen und diese an bestimmten Stellen auf der Festplatte speichern.
Dazu benötige ich eine eindeutige Liste der Kategorien:
Ich überlege: da die Firma Excel 2016 einsetzt, hat sie noch nicht die Funktion EINDEUTIG. Also erzeuge ich per Programmierung eine Pivottabelle und erhalte so eine (sogar sortierte) Liste der einzelnen Kategorien:
Sub MachePivot()
Dim xlBlattAktiv As Worksheet
Dim xlBlattHilf As Worksheet
Dim xlPivotCache As PivotCache
Dim xlPivotTabelle As PivotTable
Dim lngZeilen As Long
Dim lngSpalten As Long
Set xlBlattAktiv = ActiveSheet
Set xlBlattHilf = ThisWorkbook.Worksheets.Add
lngZeilen = xlBlattAktiv.Range("A1").CurrentRegion.Rows.Count
lngSpalten = xlBlattAktiv.Range("A1").CurrentRegion.Columns.Count
Set xlPivotCache = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=xlBlattAktiv.Name & "!R1C1:R" & lngZeilen & "C" & lngSpalten, _
Version:=8)
Set xlPivotTabelle = xlPivotCache.CreatePivotTable( _
TableDestination:=xlBlattHilf.Range("A1"), _
TableName:="RenesPivot2", _
DefaultVersion:=8)
xlPivotTabelle.ColumnGrand = False
xlPivotTabelle.RowGrand = False
With xlPivotTabelle.PivotFields("Kategorie")
.Orientation = xlRowField
.Position = 1
End With
End Sub
Ich teste – und: padautz: Fehler!
Ich brauche eine Weile, bis ich die Ursache finde. In der Liste gab es zwei Mal eine Spalte mit der Bezeichnung „Kommentar“. Excel 2016 schafft es nicht, die Spalten für die Pivottabelle umzubenennen (wie neuere Excelversionen):
Irgendwie doof. Warum nennt Microsoft die Parameter xlToRight und xlToLeft, aber xlDown und xlUp in der Funktion End? Das führt leicht zu Tippfehlern, wenn man nicht genau bei IntelliSense hinschaut:
so wie du es geschrieben hast, habe ich das Makro aufgezeichnet: ändere die Skalierung der y-Achse. Allerdings erhalte ich einen Fehler, wenn ich das Makro laufen lasse:
Hallo Nadine,
wahrscheinlich liegt es daran, dass du das Diagramm nicht markiert hast? (ActiveChart)
VBA-Schulung. Wir üben den Makrorekorder. Ändert man die Schriftart über das Dropdownsymbol in der Registerkarte „Start“,
zeichnet der Makrorekorder auf:
Sub Schriftart()
'
' Schriftart Makro
'
'
With Selection.Font
.Name = "Algerian"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
End Sub
Also: der ganze Dialog (oder genauer: die Registerkarte „Schrift“ des Dialogs „Zeichen formatieren“) wird aufgezeichnet.
Ein Teilnehmer fragt, was denn aufgezeichnet wird, wenn man im Dialog „Zeichen formatieren“ die Schrift ändert und dies aufzeichnet. Peinlich: ich weiß es nicht. Wir zeichnen auf. Das gleiche Ergebnis – auch hier wird nicht der gesamte Dialog (wie beispielsweise beim „Seite einrichten“) aufgezeichnet, aber auch nicht nur der eine Befehl, der geändert wurde, sondern sämtliche Befehle des Registers:
VBA-Schulung. Ich lasse während der Schulung kleine „Lösungen“ programmieren. Ich schaue den Teilnehmern und Teilnehmerinnen über die Schulter. Bei einem der Teilnehmer finde ich die Codezeilen:
i = 3
MsgBox ActiveCell(i)
Die ActiveCell ist B1, ActiveCell(3) liefert den Inhalt der Zelle B3, hier: „Peking“:
Das heißt: ActiveCell(1) meint die Zelle selbst, ebenso wie ActiveCell(1, 1). Dies ist also keine Kurzschreibweise für ActiveCell.Offset(1, 1), denn damit wäre C2 gemeint. Auch nicht für Cells(1, 1) – das wäre A1.
Diese Schreibweise gehört dann zur Kategorie ActiveCell.Range(„A1“), womit B1, also die aktive Zelle gemeint ist (A1 ist hier ein relativer Bezug).
Ich würde schreiben:
Cells(3, 2)
oder
Range(„B1“).Offset(2, 0)
wenn ich B3 meine.
Und: die Eigenschaft „Value“ nicht vergessen!
Und: noch angeben, auf welchen Tabellenblatt die Zelle liegt.
Erstaunlich. In einem Bereich stehen Datumsangaben. In einer Zelle steht eine andere Datumsangabe. Sie soll „gesucht“ werden. Weder ZÄHLENWENN noch VERGLEICH haben ein Problem damit:
Wahrscheinlich ist die Ursache folgende: Excel kennt nur die Datentypen Text und Zahl, während VBA verschiedene Datentypen für Zahlen kennt (Integer, Long, Double, …), aber auch für Datum (Date).
Also: Flux den Wert der Variablen datDatum in eine Long-Zahl konvertieren (CLng(datDatum) ) – und schon klappt es wieder:
Klickt man in Excel auf Datei / Speichern unter, wählt einen Dateinamen einer anderen Datei, die bereits existiert, wird man gefragt, ob man die Datei ersetzen möchte. In VBA liefert der Befehl SaveAs die gleiche Meldung:
Jedoch nicht die Methode SaveCopyAs. Dort wird die vorhandene Datei (sofern nicht geöffnet) kommentarlos überschrieben.
Randbemerkung: Klickt man im Meldungsfenster auf die Buttons „Nein“ oder „Abbrechen“ ist eine Fehlermeldung die Folge …
ich hoffe das du bei diesen sommerlichen Temperaturen einen guten und entspannten Tag hast.
Zurzeit beschäftige ich mich mit der Ribbon-Programmierung in MS-Office. In Word gibt es ein Button Schließen/Alles Schließen, mit der MsoID = „FileCloseOrCloseAll“. Klickt man diesen Button an, wird das aktuelle Dokument geschlossen. Wird beim Anklicken die Umschalttaste gedrückt, werden alle offenen Dokumente geschlossen.
Wie bekomme ich eine solle Funktionalität bei einem benutzerdefinierten Button hin. Ich möchte zwei unterschiedliche Makros aufrufen, je nachdem ob beim Anklicken die Feststelltaste gedrückt oder nicht gedrückt ist.
Hast du eine Idee?
Liebe Grüße und
Salü
Ernst
Hallo Ernst,
ich glaube nicht, dass das geht. Die Steuerellemente haben folgende Ereignisse:
Ereignis
Beispiel
onAction (button)
Sub Prozedur(ByRef Control As IRibbonControl)
onAction (checkBox, toggleButton)
Sub Prozedur(ByRef Control As IRibbonControl, ByRef Pressed As Boolean)
onAction (dropDown, galley)
Sub Prozedur(ByRef Control As IRibbonControl, ByRef SelectedID As String, ByRef SelectedIndex As Integer)
onChange (editBox, comboBox)
Sub Prozedur(ByRef Control As IRibbonControl, ByRef Text As String)
Das heißt: für die Buttons gibt es beim Aufruf einer Prozedur kein Parameter, der eine weitere Taste abfangen kann.
Auch bei den Methoden:
Methode
Beschreibung
ExecuteMso
Führt das vom idMso-Parameter angegebene Steuerelement aus.
GetEnabledMso
Gibt True zurück, wenn das vom idMso-Parameter angegebene Steuerelement aktiviert ist.
GetImageMso
Gibt ein IPictureDisp-Objekt des vom idMso-Parameter angegebenen Steuerelementbilds zurück, wobei die Abmessung von Height und Width angegeben wird.
GetLabelMso
Gibt die Beschriftung des vom idMso-Parameter angegebenen Steuerelements als Wert vom Typ String zurück.
GetPressedMso
Gibt einen Wert zurück, der angibt, ob das vom idMso-Parameter angegebene Umschaltflächen-Steuerelement gedrückt wird.
GetScreentipMso
Gibt die QuickInfo des vom idMso-Parameter angegebenen Steuerelements als Wert vom Typ String zurück.
GetSupertipMso
Gibt eine MultiInfo zum vom idMso-Parameter angegebenen Steuerelement als Wert vom Typ String zurück.
GetVisibleMso
Gibt True zurück, wenn das vom idMso-Parameter angegebene Steuerelement sichtbar ist.
Kann man mit GetPressedMso nur bei Toggle-Buttons prüfen, ober ein- oder ausgeschaltet wurde, aber nicht wie gedrückt wurde.
Ich kann diese Frage mal nächste Woche auf meinem Blog veröffentlichen – vielleicht weiß jemand eine Antwort …
Liebe Grüße
Rene
Hallo Rene.
Nach deinen Ausführungen bin ich auf die Idee gekommen folgendes in WORD auszuprobieren.
Bei zwei geöffneten Dokumenten habe ich im Direktbereich des VBA-Editors den Befehl
CommandBars.ExecuteMso(„FileCloseOrCloseAll“)
einzugeben und danach nur die Enter-Taste gedrückt.
Es wird die Schließen-Routine aufgerufen.
Wird allerdings die Shift- und die Enter-Taste gleichzeitig gedrückt, wird die AllesSchließen-Routine aufgerufen.
Daraufhin habe ich folgendes versucht:
Ich habe in ein Word-Dokument mit Hilfe des Office RibbonX Editor diese Sequenz eingefügt.
und im VBA-Bereich des gleichen Dokumentes folgendes.
Option Explicit
'API zum feststellen des Keyboard-Status deklarieren.
#If VBA7 Then
Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#Else
Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#End If
Sub OnActionButton(control As IRibbonControl)
Select Case control.ID
Case "SaveAsDocxOrPdf": SpeicherAlsDocxOderPdf 'Neuer Umbruch
Case Else
MsgBox "Fehler in OnAction: '" & control.ID & "' nicht erkannt"
End Select
End Sub
Sub SpeicherAlsDocxOderPdf()
If Abs(GetKeyState(&H10) < 0) Then 'Abfrage ob Shifttaste gedrückt ist
CommandBars.ExecuteMso ("FileSaveAsPdfOrXps")
Else
CommandBars.ExecuteMso ("FileSaveAsWordDocx")
End If
End Sub
Und es klappt. Wird das benutzerdefinierte Icon „Als DOCX oder PDF speichern“ angeklickt, erscheint das Formular „Speichern als Dokument ohne Makros“. Wird während des Anklicken des Icon die Shift-Taste gedrückt, erscheint das Formular „Als PDF oder XPS veröffentlichen“.
Salü
Ernst
Das ist sehr, sehr clever! DARAUF wäre ich nie gekommen.
Die Probleme mit dem Titel und dem Vorwort habe ich jetzt erledigt.
Ich hänge aber immer noch am Druckauftrag.
Anbei mal die aktuellen Codes:
[… es folgen einige Dutzend Codezeilen]
Folgende Fehler treten aber bei mir auf: Nachdem ich diese ProtectBefehle eingebaut habe kommt folgende Fehlermeldung:
Hallo Herr R.,
mir fehlt noch eine Info:
* öffnen Sie mal bitte Ihr VBA-Projekt (also die Word-Datei
* Wechseln Sie nach VBA
* Heben Sie den Schutz auf
* Klicken Sie auf den Menüpunkt Debuggen / Kompilieren In welcher Zeile entsteht der Fehler? Könnten Sie mir bitte diese Zeile oder auch die Zeilen „außenrum“ schicken.
Hallo Herr Martin,
Sie sind aber schnell… Meinen Sie das?
*lach*
DAS ist Falsch.
Es muss heißen:
ActiveDocument.Protect NoReset:=True
Zwischen Protect und NoReset muss ein Leerzeichen statt des Punktes stehen
Liebe Grüße
Rene Martin
PS: und dann noch einmal Debuggen, bitte!
####
Aber immer noch J
stimmt, Herr R., da habe ich doch mal einen Artikel darüber geschrieben.
Die Methode Protect verlangt unbedingt den Parameter Type, obwohl IntelliSense dies nicht anzeigt:
VBA-Schulung. Ich zeige und erkläre den Makrorekorder.
Aufgabe: Zeichnen Sie ein Makro auf, das eine Zelle formatiert, beispielsweise: fett, kursiv, Schriftart, Schriftgröße, Ausrichtung, Hintergrundfarbe, … und testen es an einer anderen Zelle.
Eine Teilnehmerin beschwert sich, dass der Makrorekorder nicht „optimale Spaltenbreite“ aufzeichnet.
Was hat sie gemacht? Sie hat ihr Makro in einer anderen Zelle in einer anderen Spalte ausprobiert. Leider zeichnet der Makrorekorder „hart“ den Spaltennamen auf, beispielsweise:
Columns("D:D").EntireColumn.AutoFit
Später erkläre ich den Unterschied zwischen der relativen und der absoluten Aufzeichnung
Oh – man muss so aufpassen! Durchläuft man in VBA mit einem Zähler eine Sammlung an Objekten, greift sich Excel das erste, das zweite, das dritte, … Objekt. Aber welches ist das erste Objekt?
Bei Tabellenblättern ist das erste Objekt das linkeste. Dann das zweite von links. Dann das dritte.
Leider gibt es viele Sammlungen mit einer anderen „Reihenfolge“: die Reihenfolge, in der die Objekte erstellt wurden. Beispielsweise bei Diagrammen. Das erste ist nicht das linkeste, sondern das Diagramm, das zuerst erstellt wurde. Okay – das leuchtet ein.
Allerdings – wie sieht es bei Links aus. Wenn in einer Spalte mehrere Links stehen, ist der erste Link dann der oberste? Wird dann von oben nach unten weiter gezählt?
Nein! Auch hier gilt die Regel: die Reihenfolge des Erstellens entscheidet! Das heißt:
Das ist der erste Link:
Und nun wird der zweite und dann der dritte Link erstellt:
Und schließlich die übrigen 40.
Lässt man sie auslesen, erhält man:
Sub Links()
Dim i As Integer
Dim xlBlatt As Worksheet
Set xlBlatt = ActiveSheet
For i = 1 To xlBlatt.Hyperlinks.Count
xlBlatt.Range("C" & (i + 3)).Value = xlBlatt.Hyperlinks(i).SubAddress
Next i
End Sub
folgende Liste:
Zum Glück verfügt das Objekt Link über die Eigenschaften Range und damit über Range.Row und Range.Column. Natürlich auch über Range.Address. So kann man die Links lokalisieren.
Oh, Mann – wie kann man nur zwei Wörterbücher gleich nennen?
Genauer: in Word gibt es ein Wörterbuch. In Excel auch. Das VBA-Objekt heißt in Word „Dictionary“. In Excel „CheckSpelling“.
Zugleich gibt es neben Array und Collections in VBA eine Sammlung „Dictionary“. Sie kann verwendet werden, wenn man die „Microsoft Scripting Runtime“-Bibliothek einbindet. Und dann stellt sich die Frage: „welches Dictionary wird denn nun verwendet?“
Richtig: es tritt ein Fehler auf, wenn beide verwendet werden. Genauer: der Fehler tritt dann auf, wenn man ZUERST einen Verweis auf die Word-Bibliothek setzt und anschließend auf die „Microsoft Scripting Runtime“-Bibliothek :
Die besser Lösung (statt dem Ändern der Reihenfolge): Die Bibliotheken voll qualifizieren. Dann klappt es:
Dim wdApp As Word.Application
Dim wdWordWoerterbuch As Word.Dictionary
Dim wdWordBenutzerWoerterbuecher As Word.Dictionaries
' -- Dictionary
Dim wdListeDict As Scripting.Dictionary
Set wdListeDict = New Scripting.Dictionary
wdListeDict.Add "007", "Bond"
' -- das Word-Wörterbuch
Set wdApp = New Word.Application
Set wdWordBenutzerWoerterbuecher = wdApp.CustomDictionaries
For Each wdWordWoerterbuch In wdWordBenutzerWoerterbuecher
MsgBox wdWordWoerterbuch.Name
Next
regex.Pattern = strMuster1
regexRaus.Pattern = strMuster1_Raus
regexRaus.Global = True
For i = 1 To ThisWorkbook.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
If regex.Test(Range("I" & i).Value) = True Then
Set strTreffer = regexRaus.Execute(Range("I" & i).Value)
j = 0
For Each strFund In strTreffer
strTemp = strFund
If IsDate(strTemp) Then
If Len(Split(strTemp, ".")(2)) = 3 Or Len(Split(strTemp, ".")(2)) = 1 Then
Range("Q" & i).Offset(0, j).Value = strTemp
Range("Q" & i).Offset(0, j).Interior.Color = vbRed
Else
Range("Q" & i).Offset(0, j).Value = CDate(strTemp)
If Year(CDate(strTemp)) > Year(Date) Then
Range("Q" & i).Offset(0, j).Interior.Color = vbRed
End If
End If
Else
Range("Q" & i).Offset(0, j).Value = strTemp
Range("Q" & i).Offset(0, j).Interior.Color = vbRed
End If
j = j + 1
Next
End If
Next
Vier andere Varianten werden analog abgearbeitet. Klappt.
Warum hat VBA nicht als Standard Regex eingebunden?
Warum kennt PowerQuery keine regulären Ausdrücke?
Warum kann man keine regulären Ausdrücke beim Autofilter oder Spezialfilter eingeben?
Nachtrag: Ich habe etwas gewühlt. Imke Feldmann beschreibt, wie man über JavaScript einen Zugriff auf RegEx erhält:
Und: vor einigen Jahren hatte ich eine XML-Schulung, in der ich die regulären Ausdrücke vorgestellt hatte. Die Teilnehmerinnen kannten sie, waren damit vertraut, arbeiteten in „anderen Welten“ damit und waren begeistert. Sie wollten sich sogar T-Shirts mit dem Aufdruck „I ♥ RegEx“ drucken lassen. Haben sie aber doch nicht.
Ich bin gerade völlig perplex. Ich programmiere für eine Schweizer Firma ein Excel-Tool. Ich lasse alle Tabellenblätter, die mit Monatsnamen beschriftet sind, ausblenden. Nur das Blatt MRZ bleibt stehen. In der Schweiz bleibt es stehen.
Ich frage Tanja Kuhn. Sie hat die Schweizer Oberfläche von Excel:
Und ja: sie bestätigt es: seit einigen Versionen lautet die Abkürzung des dritten Monats in der Schweiz nicht Mrz, wie in der ISO 8601 (EN 28601:1992) festgelegt, sondern Mär. Das erkennt man schnell, wenn man den Text „Jan“ einträgt unter herunterzieht:
Und eben deshalb liefert der VBA-Befehl
Format(DateSerial(2022, 3, 1), "MMM")
in der Schweiz etwas anderes als in Deutschland (oder Österreich).
Zu DateSerial: Kennst du dich Excel-Funktion DATUM nicht? Sie wandelt eine JahresZAHL, eine MonatsZAHL und eine TagesZAHL in ein Datum um. Und eben das macht DateSerial in VBA.
Ich habe eine Arbeitsmappe mit sieben + ein, also acht Tabellenblattern. Sie heißen:
Chef, Brummbär, Schlafmütz, Hatschi, Pimpel, Happy, Seppel und Schneewittchen
Die ersten drei Blätter sind ausgeblendet, ebenso das letzte (Master-)Blatt:
Also auf Excel-Seite sieht das so aus:
Nun soll per VBA das erste Blatt sichtbar gemacht werden, umbenannt werden und an eine bestimmte Position verschoben werden. Das Masterblatt „Schneewittchen“ wird anschließend wieder unsichtbar gemacht.
Schritt für Schritt:
1.) Zugriff auf das (unsichtbare) Blatt „Schneewittchen:
Dim xlBlatt As Worksheet
Dim i As Integer
Dim strBlattname As String
Set xlBlatt = ThisWorkbook.Worksheets("Schneewittchen")
2.) Blatt wird eingeblendet:
xlBlatt.Visible = xlSheetVisible
3.) Blatt wird vor das erste Blatt geschoben:
xlBlatt.Copy Before:=ThisWorkbook.Worksheets(1)
4.) Mit Hilfe einer benutzerdefinierten Funktion GibtEsBlattname wird überprüft, welcher Blattname noch nicht existiert, beispielsweise „Schneewittchen 001“:
i = 0
Do
i = i + 1
strBlattname = "Schneewittchen " & Format(i, "000")
Loop Until GibtEsBlattname(strBlattname) = False
5.) Das erste Blatt wird umbenannt:
ThisWorkbook.Worksheets(1).Name = strBlattname
6.) Das Masterblatt „Schneewittchen“ wird ausgeblendet:
Die Move-Methode des Worksheet-Objektes konnte nicht ausgeführt werden.
Der Fehler liegt in folgender Zeile:
xlBlatt.Copy Before:=ThisWorkbook.Worksheets(1)
Welches ist das ERSTE Tabellenblatt? Blatt Nummer 1 oder das erste sichtbare Tabellenblatt. Die Antwort: letzteres: das Blatt wird vor das erste sichtbare Blatt kopiert. Damit ist es anschließend nicht das erste Blatt! Sondern die drei ausgeblendeten Blätter liegen noch weiter links von dem Blatt.
Leider besitzt die Methode Copy kein Objekt, an das das Ergebnis des Kopierens übergeben werden kann.
Also muss man sich „auf die Suche“ nach dem Blatt machen, beispielsweise: durchlaufe alle Blätter und such das Blatt, das „Schneewittchen(2)“ heißt, besser
ThisWorkbook.Worksheets(1).Name Like "Schneewittchen *"
deine Mails mit positiven Informationen und Anhang erreichten mich.
Themenwechsel 3 von 3:
Falls du an weiteren Fragen rund um EXCEL-VBA Interesse hast, informiere mich einfach. Dazu habe ich dir zwei Dateien mitgesendet. Die Lösung der Probleme ist kein MUSS.
Die Thematiken:
(1) ein Filterproblem ist bestimmt nicht so schwierig zu lösen. Eine Liste:
Perfide. Ich habe eine Excelmappe auf der Festplatte – das VBA-Projekt ist mit Kennwort geschützt. Ich kenne das Kennwort. Dennoch: es lässt sich nicht öffnen:
Ich brauche eine ganze Weile, bis es mir dämmert: ich habe die Sprache der Tastatur geändert:
Und richtig: dort sind Y und Z vertauscht und auch die Sonderzeichen sitzen an anderen Positionen …
Es ist schon eine Weile her, dass wir Kontakt hatten und ich hoffe es geht Dir in Anbetracht der Weltlage einigermassen gut?
Leider kämpfe ich wieder mit einem Problem, wobei ich mich ehrlich gesagt mehr darüber ärgere, dass ich das Problem nicht finde, als über den Fehler selbst. Es geht wieder um das VBA Script, welches die Excel Daten auswertet und je nach Zuweisung im Layer meine Shapes sichtbar macht, oder eben nicht.
Das Script macht eigentlich was es soll, doch nach dem Durchlaufen der letzten Zeile, kommt eben der genannte Fehler. Vielleicht hast Du etwas Zeit und bist willig mir zu helfen? Herzlichen Dank im Voraus für eine Reaktion.
Hallo H.,,
schau mal in deine Daten: der letzte Datensatz ist leer:
Du musst die überprüfen/abfangen, beispielsweise mit:
Das Arbeitsblatt oder die Arbeitsblätter, die in die Zielarbeitsmappe kopiert oder verschoben werden, besitzen möglicherweise angefügte Makros. Makrocode stellt ein potenzielles Sicherheitsrisiko dar. Sie sollten den Vorgang nur fortsetzen, wenn Sie sicher sind, dass der Makrocode von einer vertrauenswürdigen Quelle stammt. Möchten Sie den Vorgang fortsetzen?
Ich habe keine Ahnung, wo man das in Excel einstellen kann. Ich vermute, diese Option wurde in den Gruppenrichtlinien von der IT abgefangen. Oder? Kennt jemand diese Meldung?
Solche Meldungen erfreuen immer wieder Sinn und Gemüt:
Das Projekt kann nicht erstellt werden, weil das „Excel Visual Studio-Entwurfszeitadapter-Add-In“ nicht ordnungsgemäß ausgeführt wird. Das Add-In wurde in Excel möglicherweise deaktiviert oder für inaktiv erklärt oder in den Einstellungen im Sicherheitscenter sind alle Add-Ins deaktiviert. Überprüfen Sie den Add-In-Status in den Excel-Optionen. Wenn das Add-In aktiv und aktiviert ist, reparieren Sie Visual Studio Tools for Office, oder führen Sie eine Neuinstallation aus,
Lange, sehr lange sitze ich davor und versuche den Sinn zu begreifen …
Excel-VBA-Schulung. Wir programmieren eine eigene Funktion. Als Übung gebe ich auf eine Funktion zu schreiben, welche die Hypotenuse in einem rechtwinkligen Dreieck berechnet.
Also:
Ich tippe die Lösung, die ich mit einem „Quick & Dirty“ kommentiere:
Ich verlasse die Codezeile:
Ein Fehler ist die Folge. Darf man nicht die Funktion Sqr (Wurzel) aufrufen und im Aufruf rechnen?
Okay – dann „sauberer“ in zwei Zeilen:
Schon wieder ist die Zeile
c = a^2 + b^2
in der Funktion
Function Hypotenuse(a As Double, b As Double) As Double
Dim c As Double
c = a^2 + b^2
Hypotenuse = Sqr(c)
End Function
falsch. Es dämmert mir. Richtig: das Caret-Zeichen (^) – der Zirkumflex – darf nicht direkt hinter die Variable geschrieben werden, da a^ zum Erstellen von Long Long-Datentypen in einer 64-Bit-Umgebung verwendet wird. Man muss ein Leerzeichen zwischen Variable und dem „Dach“ ein Leerzeichen schreiben:
Also:
Function Hypotenuse(a As Double, b As Double) As Double
Dim c As Double
c = a ^ 2 + b ^ 2
Hypotenuse = Sqr(c)
End Function
oder in der Kurzform:
Function Hypotenuse(a As Double, b As Double) As Double
Hypotenuse = Sqr(a ^ 2 + b ^ 2)
End Function
Excel-VBA-Schulung. Wir üben das Programmieren von eigenen Funktionen (also function), die in Excel verwendet werden sollen. Ich zeige einen Fehler:
Und erkläre, dass man die Ursache gut finden kann, indem man einen Haltepunkt in Excel setzt:
Dann muss man die Funktion editieren (doppelklick oder [F2]) und sie wird erneut aufgerufen und berechnet:
Allerdings: nichts passiert. Ich brauche eine Weile, bis ich verstehe. Die Parameter sind vom Typ Double deklariert. Einer der Eingabewerte ist jedoch keine Zahl:
Und so wird die Funktion schon direkt nach dem Aufruf abgebrochen und liefert die Fehlermeldung #WERT, ohne dass die Zeile mit dem Haltepunkt erreicht wird. Also flugs den Wert der Zelle in eine Zahl ändern und schon wird der Haltepunkt erreicht.
Ich habe ein mächtiges Werkzeug mit Excel VBA erstellt, in welchem unter anderem Dateien geöffnet werden, Informationen ausgelesen und in die eigene Datei geschrieben werden. So nach dem Motto:
Dim xlDatei As Workbook
Set xlDatei = Application.Workbooks.Open("C:\BIA_contoso_IT.xlsm")
' -- tue etwas
xlDatei.Close SaveChanges:=False
Set xlDatei = Nothing
Ich teste mehrere Male.
Mit Erstaunen stelle ich im Projekt-Explorer fest, dass die Datei mehrmals geöffnet ist:
Ein Blick in Excel unter Ansicht / Fenster wechseln kann dies nicht bestätigen. Und schließlich: eine Datei mit einem bestimmten Namen kann in Excel nur ein Mal geöffnet sein.
Beim sechsten Test erhalte ich folgende wunderliche Meldung:
Ich beende das Programm Excel, öffne – der Spuk ist verschwunden. Ich starte das Programm – die verwunderliche Meldung kommt erneut. Die Ursache ist gefunden: Excel behauptet plötzlich, dass das Tabellenblatt Nummer 3 nicht vorhanden ist!?! In der Datei befinden sich zirka 34 Tabellenblätter, die ich alle „prüfe“.
Automatisierungsfehler: Ungültige Vorreferenz oder Referenz zu unkompiliertem Typ
Ich verstehe es gerade nicht. Habe ich – in meiner aktuellen Version 2201 ein neues Feature entdeckt?
In einer Excelschulung wollte ich zeigen, dass zwei gesetzte Filter einem logischen UND entsprechen und somit eine Schnittmenge darstellen:
Ich visualisiere das mit zwei Ellipsen:
Und wollte die Schnittmenge deutlich hervorheben, indem ich beiden Formen kombinieren. Aber das Zusammenführen der Formen ist in Excel leider nicht möglich! Ich habe darüber geschrieben:
Ernst hat sich nun die Frage gestellt, ob man so ein Problem nicht mit VBA lösen kann. Konkret: ob man die Formen nicht nach PowerPoint kopieren kann, dort zusammenführen kann und anschließend wieder zurück kopieren kann. Die Antwort lautet: „ja“. Allerdings: einige Dinge gibt es hierbei zu beachten:
Im ersten Schritt wird geprüft, ob mindestens zwei Formen markiert wurden.
Anschließend wird ein Auswahldialog aufgerufen:
Er prüft, ob PowerPoint bereits geöffnet ist:
On Error GoTo Fehler
Set PP_app = GetObject(Class:="PowerPoint.Application")
PP_app.Visible = True 'Stellt die Sichtbarkeit auf an.
PPObjektaufrufen = True 'und setzt die Funktionsrückmeldung auf True.
Exit Function 'Funktion wird verlassen.
Fehler:
PPObjektaufrufen = False 'Die Funktionsrückmeldung wird auf False gesetzt.
Falls nicht, wird es geöffnet:
Set PP_app = CreateObject(Class:="Powerpoint.Application")
PP_app.Visible = True 'Stellt die Sichtbarkeit auf an.
Wichtig hierbei ist, dass PowerPoint sichtbar ist!
Die Formen werden kopiert und eingefügt:
Selection.Copy 'Die selektierten Formen werden in die Zwischenablage kopiert.
Application.ActiveWindow.Selection.ShapeRange(1).Name = OriginalName 'Danach wird der Originalname der erstselektierten Form wieder hergestellt
PP_app.Presentations.Add.Slides.Add Index:=1, Layout:=ppLayoutBlank 'Es wird eine neue Präsentation mit einer leeren Folie erstellt.
'Die Slide.Add-Methode ist eine verborgene PowerPoint-Methode
PP_app.ActivePresentation.Slides(1).Shapes.Paste 'Die Formen werden aus der Zwischenablage in die PP-Präsentation eingefügt.
PP_app.ActivePresentation.Slides(1).Shapes.SelectAll 'und selektiert.
wichtig – die Methode Add der Sammlung Slides ist dies ein verborgenes Element: Diese werden von Intellisense nur dann angezeigt, wenn die Option „verborgene Elemente anzeigen“ bestätigt wurde. Ich habe einmal darüber geschrieben:
Wichtig ist hierbei herauszufinden, welches die zentrale Form ist, also die Form, von welcher die anderen die Formatierung erben:
'PrimaryShape:= Die Form, von der die resultierende Form ihre Formatierung erbt.
'Wenn Fehler auftritt, wird dieser abgefangen
On Error GoTo Fehler1
PP_app.ActiveWindow.Selection.ShapeRange.MergeShapes MergeCmd:=Formform, _
PrimaryShape:=PP_app.ActiveWindow.Selection.ShapeRange("PrimaerForm")
'Fehlerroutine zurückstellen auf Fehler allgemein
On Error GoTo FehlerAll
PP_app.ActivePresentation.Slides(1).Shapes.SelectAll 'die neu erstandene Form wird selektiert.
'Wurde eine Form selektiert, wird diese nach Excel übertragen. Es könnte, beispielsweise bei der Aktion
'Schnittmenge bilden, der Fall auftreten, dass keine Form erzeugt wird.
Das Ergebnis wird zurück nach Excel kopiert:
'Wieviel Formen sind selektiert?
AnzahlFormen = 0
AnzahlFormen = PP_app.ActiveWindow.Selection.ShapeRange.Count 'Anzahl selektierte Formen
If AnzahlFormen > 0 Then 'Ist keine Form selektiert erfolgt keine Aktion.
PP_app.ActiveWindow.Selection.Cut 'Ansonsten werden die selektierten Formen ausgeschnitten und in die Zwischenablage kopiert.
If Not MergeFehler Then ActiveSheet.Paste 'und werden dann aus der Zwischenablage nach Excel kopiert.
End If
PowerPoint wird geschlossen:
If PPschonGestartet Then 'Wurde keine neue PP-Instanz gestartet.
PP_app.ActivePresentation.Close 'wird nur die neu erzeugte PP-Präsentation geschlossen
Else
If NeuePPwiederSchliessen Then
PP_app.Quit 'Steht dieser Schalter auf True wird PP wieder geschlossen.
End If
End If
Set PP_app = Nothing ' Objektvariable wird auf Nothing gesetzt
Exit Sub
Ein beachtliches Werk mit vielen Fallstricken – vielen Dank an Ernst Börgener.
Der Urheber behauptet dass das korrekt ist und keinen Fehler darstellt.
Ich würde mich herzlich freuen, wenn wir kurz miteinander sprechen können.
Schon jetzt herzlichen Dank.
Mit freundlichen Grüßen
####
Hallo Herr P.,
welchen Wert hat denn „MenueName“? Wenn Sie mit der Maus über diesen Variablennamen beim Debuggen fahren – was wird denn angezeigt? Und was bei „SubMenueName“?
Umgekehrt: Arbeiten Sie noch mit Menüs? Die sind doch seit Excel 2007 verschwunden? Ich verwende seit vielen Jahres das Ribbon und füge dort Symbole ein.
danke für den Code. Mir fehlt allerdings immer noch ein Befehl: ein anderes Makro ruft dieses Makros auf und löscht das Symbol in der Symbolleiste. Ich weiß nicht welches, weil ich diese Variable nicht „sehe“.
Im Makro
Sub Menue_Loeschen_ZV()
lautet die erste Zeile
On Error Resume Next
also auf Deutsch: sollte ich – das Makro – das Symbol nicht löschen können (Controls(MenueName).Delete) – beispielsweise weil es schon gelöscht wurde … na: dann mach halt gar nichts! Auch keine Fehlermeldung! Ist völlig okay so!
Jedoch im Makro
Sub Menueeintrag_loeschen_ZV()
fehlt diese Zeile.
Tipp: Fügen sie nach der Sub-Zeile einfach die On-Error-Zeile ein (wurde wahrscheinlich vergessen), also so:
Dir zum Jahresanfang alles Gute – bleib gesund und für die Arbeit (notwendiges Kleingeld) viel Erfolg!!!
…und ich stelle mich im Neuen Jahr ganz schön doof an. So würde ich gern Deine Hilfe annehmen.
In meinen Programmen will ich etwas Neues probieren und es gelingt mir nicht.
Bisher funktioniert alles prima […] Alles ist gut!
Nun: Mein Wunsch wäre eine etwas komplexere Auswertung, die ich vorher erstellt habe und der in einer Datei Auswertung.xlsx liegt. Diese Arbeitsmappe kann ich nun per Programm über Application.Workbooks.Open „C:\Pfad\Auswertung.xlsx“ öffnen – das funktioniert.
Aber jetzt: Jetzt möchte ich diese Mappe für die weitere Benutzung zuweisen (ich will diese Mappe ja per Programm bearbeiten) Set xlsDatei(i) = ?????? und da geht es nicht weiter…
Es ist bestimmt nur eine Kleinigkeit, aber ich habe mich irgendwie festgebissen…
Ich danke Dir schon im voraus für Deine Hilfe!
Liebe Grüße Wolfgang
####
Äh ….
Set xlsDatei(i) = Application.Workbooks.Open(„C:\Pfad\Auswertung.xlsx“)
Moin Wolfgang,
DAS erschüttert mich! Das weißt du doch selbst: Methoden haben in VBA zwei Schreibweisen: Leerzeichen, wenn etwas ausgeführt wird (zöffne die Datei) und Klammer, wenn etwas an eine Variable übergeben wird (… und speichere es als xlsDatei).
Kennst du Anrufe, die beginnen mit einem „hast du mal einen Moment Zeit?“ oder: „störe ich gerade?“
Richtig: Angelika rief am Wochenende an. Meine Kollegin Angelika. Sie bereitet gerade einen Excel-Makro-Kurs vor und suchte einige gute Gedanken für Befehle, die man mit dem Makrorekorder aufzeichnen könne. Dabei stieß sie auf die benutzerdefinierte Kopfzeile.
„Sag mal“, fragte sie, „wenn ich in der Kopfzeile aufzeichne: Seitennummerierung, Datum, Dateiname und Tabellenblattname passiert etwas ganz Komisches.“
Zuerst glaubte ich es nicht. Also – auch aufgezeichnet:
Eigentlich alles okay. Ich habe keine Ahnung, was hier passiert!
Ich rate Angelika, im Makrokurs nur den Firmennamen als Text aufzuzeichnen. Das klappt!
Okay – für Datum und Uhrzeit stehen die VBA-Befehle Date und Time zur Verfügung; für den Dateiname ActiveWorkbook.Name (oder Fullname), für den Blattnamen ActiveSheet.Name, aber für die Seitennummer? Ich weiß keine Lösung.
Danke an Angelika Meyer für diesen Hinweis!
Und nur wenig nach der Veröffentlichung des Artikels erreicht mich ein Kommentar von Ernst. Großartig – DAS ist des Rätsels Lösung! Hier sein Kommentar:
Hallo Rene,
dieses eigenartige Verhalten tritt bei mir (Excel 2019) nur dann auf,
wenn vor dem Festlegen der PageSetup-Eigenschaften die
Application.PrintCommunication-Eigenschaft auf False gesetzt wird. Wenn
die Kommunikation mit dem Drucker nicht abgeschaltet wird, tritt dieses
Verhalten nicht auf.
Bei der Makroaufzeichnung werden die Befehle
Application.PrintCommunication = False und Application.PrintCommunication = True
verwendet.
Bei Microsoft steht wohl folgender Hinweis:
Legen Sie die PrintCommunication-Eigenschaft auf False fest, um die
Ausführung von Code zu beschleunigen, mit dem PageSetup-Eigenschaften
festgelegt werden.
Legen Sie die PrintCommunication-Eigenschaft nach dem Festlegen der
Eigenschaften auf True fest, um alle im Cache vorhandenen
PageSetup-Befehle auszuführen.
In einem Formular steht eine Formel. Okay – es befinden sich eine Reihe an Formeln dort – aber einige sind besonders lang. Manchmal soll die Zelle mit einem Wert überschrieben werden und nicht mehr variablen sein (wenn bestimmte Voraussetzungen erfüllt werden, welche die Formel nicht abbildet); dann wiederum soll die Formel zurückgesetzt werden. Also gehe ich auf die Suche – wie heißt der VBA-Code der Formel. Die Berechnung lautet:
Sicherlich hätte ich sehr, sehr lange gesucht. Und mich gewundert. Zum Glück hat mir Tanja Kuhn geholfen. Beziehungsweise mich schon vorher aufmerksam gemacht.
Die Aufgabe: in einem Wordformular soll dynamisch, das heißt per VBA, die Kopfzeile (und auch die Fußzeile) ausgetauscht werden:
Obwohl weder das Dokument noch einer der Abschnitte geschützt ist, erhalte ich eine Fehlermeldung:
Die Antwort: das Bild befindet sich in einem Inhaltssteuerelement und das wurde im Entwurfsmodus in den Eigenschaften geschützt. Folglich kann auch nicht die Kopfzeile gelöscht werden …
Könntest du dir das mal durchlesen, eventuell kennst du eine Lösung.
Ich öffne aus einer Exeltabelle heraus eine weitere Exceldatei. Dies ist ein Bestellformular.
Dort trage ich dann bestimmte Werte ein in die entsprechenden Felder.
Ich öffne diue Datei folgendermassen:
Jetzt ist es aber so das beim Oeffnen der Datei eine MsgBox aufgeblendet wird die sagt man muss einen Vorgang auswählen.
Das ist auch sinnvoll wenn man die BANF(Bestellanforderung) händisch ausfüllt.
Aber nicht bei einem Automatismus.
Ausgelöst wird die MsgBox durch das Event „Worksheet_Change“
Ich frage mich ob es eine Mögliichkeit gibt, die Tabelle so zu öffnen das alle Makros deaktiviert sind und bleiben.
Ich hatte das was gelesen das es so gehen sollte:
VBA-Schulung. Eine Teilnehmerin sieht ihren Fehler nicht und bittet mich zu helfen. Ich schaue mir den Code an:
Böse Leerzeichen! Der Fehler ist schnell gefunden: Am Ende des Tabellenblattnamens hatte sie aus Versehen ein Leerzeichen geschrieben, also statt Set xlTabelle = xlDatei.Sheets(„Almodovar“)
Hinter den Zellen liegt eine bedingte Formatierung. Color und ColorIndex liefern die voreingestellten Zellfarben und nicht die durch die Datenüberprüfung angezeigten … Es ist übrigens recht mühsam, das Ergebnis der bedingten Formatierung zu ermitteln.
Kennt ihr das? Eigentlich sollte es nicht so sein. Aber einer der Kunden bestellt unbedingt darauf. Hat auch einen guten Grund dafür.
Der Kunde ist König!
Nun – gut – soll er seinen Willen haben!
In einem sehr umfangreichen Projekt, das mit VBA realisiert wurde, soll eine Auswahl über eine Auswahlliste getroffen werden. Aber eben ein Kunde möchte Freitext haben. Eigentlich widerspricht dies dem Workflow.
Also füge ich unter der Liste ein Textfeld (!) ein und formatiere es so, dass es aussieht als wäre es ein Bezeichnungsfeld. Man muss einige der Eigenschaften ändern:
Das verraten wir natürlich nur einem Kunden. Damit DER Freitext eingeben kann. Weil er es will. Weil er es braucht. Eben: weil der Kunde König ist:
Guten Abend René, ich grüße zur späten Abendstunde und erlaube mir um diese Uhrzeit noch eine Frage mitzusenden: Ich möchte lediglich in Erfahrung bringen, welche Möglichkeiten es (nicht) gibt, bzgl. des EXCEL-Solvers: (1) Funktionieren sollte … der Einsatz „Solver“ über Ribbon „Daten“/“Solver“, bei geschütztem Tabellenblatt, wenn zusätzlich VBA genutzt wird und beim Makrolauf das Passwort (Tabellenblatt) am Anfang aufgehoben und gegen Makroende wieder gesetzt wird das kann man dem www entnehmen (2) Nicht funktionstüchtig ist: Aktivierter Tabellenblattschutz, keine VBA-Nutzung. „Solver“-Einsatz über Ribbon „Daten“/“Solver“ meine heutige Erfahrung Mir geht es vor allem um Position (2). Die Argumentation beim Kunden: Da (2) nicht funktionstüchtig ist, muss ich (1) realisieren. Ein ok zu (1) und (2) wäre nett, sicherlich kennst du die Thematiken von deinen Kunden. Ein Dankeschön für dein ok & Gruß Jürgen
Moin, Jürgen,
was macht der Solver? Er liest Werte aus einem Tabellenblatt, rechnet und schreibt Werte zurück. Beziehungsweise schreibt Zwischenergebnisse zurück und prüft, ob sich die Ergebnisse dem gewünschten Ziel annähern.
Das kann auf einem geschützten Blatt nicht funktionieren.
Ich mache es (bei anderen) VBA-Programmen immer so, dass ich den Blattschutz aufheben, Werte eintrage und dann den Schutz wieder setze.
Übrigens: auch ohne zu schreiben – es gibt einige VBA-Befehle, die man auf einem geschützten Blatt nicht ausführen kann, beispielsweise CurrentRegion!?!
Liebe Grüße
Rene
Amüsiert. Ich erstelle in Excel mit VBA eine Eingabemaske, in der verschiedene Begriffe stehen:
Wenn aus allen drei Listenfeldern etwas ausgewählt wird, werden die drei Begriffe in die entsprechenden Spalten eingetragen.
Klappt.
Danach wird die Auswahl entfernt
ListIndex = -1
Klappt nicht. Der Grund: das Ereignis Click deselektiert die drei Listen und DANN wird der Klick durchgeführt; das heißt: NUN ist ein Eintrag markiert.
Doof!
Ich mache mich auf die Suche, ob eines der Ereignisse ein Parameter Cancel besitzt, mit dessen Hilfe man ihn abbrechen könnte.
Fehlanzeige.
Also noch einmal schauen und probieren. Dann finde ich die Lösung: ich muss Click durch MouseUp ersetzen – DANN funktiert es: zuerst wird der Mausklick durchgeführt und DANACH der Code abgearbeitet (nicht umgekehrt wie beim Ereignis Click:
Private Sub lstRechts_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Mit VBA wird eine Userform (eine Maske) erstellt zur bequemen Dateneingabe. Der Wert eines Textfeldes wird als String interpretiert und als solcher bei Dezimalzahlen in eine Excelliste eingetragen. Man erkennt es, weil die Zahlen linksbündig in der Zelle stehen:
Dummerweise wird ein Text immer größer als eine Zahl definiert, so dass eine Formel
anbei die beiden Files (Visio und Excel Tabelle)
Bei der Tabelle handelt es sich um eine Copy aus einem sehr umfangreichen Excel Workbook, welches unter anderem eben die Tabelle produziert, welche die enthaltenen Services (Visio Shapes) steuert. Falls ein Kunden einen Service in einer Ausprägung bekommt, soll das entsprechend Shape auf dem Visio erscheinen.
Ein komischen Phänomen oder Verhalten ist mir bei der Fehlersuche aufgefallen. Wenn ich im Excel File in der Spalte Layer die Formel mit fixen Werten ersetze, dann läuft es irgendwie deutlich besser. Kann es sein, dass Visio im trotz dem vorher benötigten Datenabgleich im Hintergrund noch irgendetwas mit den Daten macht?
Ich habe keine andere Erklärung, wieso es ohne Formeln im Excel in Visio besser funktionieren sollte ???
Wenn ich Deine Bücher zu VBA und Visio Programmierung durchgearbeitet habe, bin ich sicher in der Lage den Visio Update direkt von Excel aus zu steuern
Freundliche Grüsse Herby
Hallo Herby,
wenn du bei bestimmten Fällen die Variable shapeOnLayer auf True setzt:
If LCase(shp.Layer(iLyr).Name) = LCase(lyrName) Then
shapeOnLayer = True
Else
und dies später abfragst:
If shapeOnLayer = False Then
lyr.Add shp, 0
End If
muss du am Anfang der Schleife die Variable wieder „zurück“ auf False setzen:
For iRow = 0 To UBound(rowIDs)
data = drs.GetRowData(rowIDs(iRow))
shapeId = data(idColumn)
lyrName = data(lyrColumn)
shapeOnLayer = False
Guten Abend René
Zuerst vielen Dank
Meine Programmier Skills sind leider zu
bescheiden um richtig folgen zu können.
Ich werde aber versuchen dies im Script
anzupassen und hoffe, dass es dann funzt. Auf jeden Fall ist es super, dass Du
die Probleme gefunden hast.
Lieber Gruss und einen schönen Abend (soweit man
das mit unserem tollen Somner überhaupt noch wünschen kann)
Herby
hoffe, Du hast einen schönen Urlaub ohne großen Regen, aber vielleicht Zeit für eine kleine Knobel-Aufgabe in VBA
Ich habe eine Tabelle, in der alles getan werden darf, d.h. auch gefiltert, aber nur nicht sortiert.
Schutz geht leider nicht, da sich dann leider die Tabelle nicht dynamisch erweitert.
In diesem Artikel steht, dass man mit Hilfe des Events „BeforeSort“ die Sortierroutinen abfangen kann.
Aber leider kriege ich das nicht hin und im Internet habe ich auch nichts gefunden. Weißt Du, wie man dieses Event in Excel platzieren kann? Würde mich freuen.
Hallo Johannes,
ich habe mal ein bisschen gewühlt und probiert:
1. Das SortObjekt existiert – allerdings besitzt es keine
Ereignisse (wie MS behauptet): Das sieht man, wenn man versucht in einem
Klassenmodul einzutragen:
Public WithEvents SO As so…
2. Ich habe überlegt, ob man die Symbole wegnehmen kann. Das
Problem: Man kann über die Registerkarte Start und Daten den Sortierbefehl
aufrufen; über das Kontextmenü oder über die Pfeilchen, die der Filter, die
intelligente Tabelle, die Pivottabelle filtern. Das heißt: es wird sehr mühsam,
dem Anwender die Symbole wegzunehmen.
3. Ich würde alle Zellen auf „nicht gesperrt“ setzen, das Blatt schützen – außer der Sortieroption. Dann kann der Anwender (fast) alles – was er nicht kann, ist beispielsweise einen AutoFilter einschalten.
danke noch einmal für Deinen Hinweis!, allerdings gelingt mit solch ein Bild (siehe Deine EMail „Verweise VBAProject“ ) nicht. Da kommt kein „Solver“ vor. Und das Laden der „SOLVER32.DLL funktioniert zwar, aber Visio kann damit nichts anfangen (wie in meiner ersten Mail) schon geschrieben.
Nun habe ich ein 64-Bit -System. Leider ist aus der kargen Fehlermeldung die Ursache, dass Visio die SOLVER32.DLL nicht mag, nicht zu erklären. Und eine SOLVER64.DLL gibt es wohl nicht (soweit ich meine Suche auch ausdehnte).
Nun werde ich mich wohl mit der nächsten Microsoft-Unzulänglichkeit auch zufrieden geben müssen. So ein großer „Laden“ und soviel Mangel (es gibt ja auch so viel Versionen…)
Vergangenheit: Hätte ich im Steuerungsbereich meinen Kunden soviel „Nichtfunktion“ zugemutet – ich glaube, da wäre ich arm dran…
der Kunde hat ein sehr „altes“ Excel (Excel 2013? Excel 2016?). Ich verwende die Funktion TEXTKETTE (in VBA: CONCAT). Genauer: mit dem Befehl
Do Until Len(Application.WorksheetFunction.Concat(xlBlatt.Range(xlTabelle.Range.Cells(1).Offset(lngAnzahl + 1, 0), xlTabelle.Range.Cells(1).Offset(lngAnzahl + 1, 8)))) = 0
lngAnzahl = lngAnzahl + 1
Loop ' -- wie viele Zeilen sind gefüllt (wird in der Variable lngAnzahl gespeichert)
überprüfe ich, in wie vielen Zeilen der Liste etwas steht, beziehungsweise, ich suche die Zeile, in der die ersten neun Spalten leer sind oder ob die Formeln, die darin stehen, „“ ergeben.
Ich habe es geändert. Könntest du ihnen bitte diese Version schicken
„Kompilierungsfehler im ausgeblendeten Modul. Dieser Fehler tritt häufig auf, wenn der Code nicht mehr mit der Version, Plattform oder Architektur dieser Anwendung kompatibel ist. Klicken Sie auf „Hilfe“, um Informationen dazu zu erhalten, wie Sie diesen Fehler beheben können.“
Der Fehler entsteht, wenn ich im Code im Modul basKonstanten die Versionsnummer ändern will.
Nach Änderung und Neustart der Tabelle kommt o.g. Fehler. Danach kann kein Dashboard mehr ohne Fehler aufgerufen werden!
Wenn das so wieder wie vorher funktioniert, dann wäre es
perfekt.
Mit freundlichen Grüßen
Hallo Herr L.,
der Grund des Fehlers ist Folgender:
Das Datum war als Datum definiert in der Form #Monat/Tag/Jahr#. Ich weiß nicht, was Sie eingetragen haben – aber vielleicht nicht als Datum. Da dieses Datum nur einmal als Text verwendet und angezeigt wird, habe ich einen Text daraus gemacht (somit wird nicht mehr impliziert konvertiert – hier lief wohl etwas schief).
Public Const p_cdatAppStand As String = "08.07.2021"
Ich muss per Programmierung den Inhalt einer Formel mit einem Bezug auswerten. Ich überlege:
Jede Bezugsformel innerhalb einer Arbeitsmappe hat in Excel die Form:
=Blattname!Zellbezug
Prima! Liegt auf dem Blatt „BMW“ in der Zelle „Z8“ ein Wert, kann man die Formel
=BMW!Z8
gut auswerten, indem man an dem Ausrufezeichen trennt. Vor dem Ausrufezeichen: Blattname; hinter dem Ausrufezeichen: Zellbezug. Gesagt – getan.
Jedoch: mir fällt auf, dass mein Programm manchmal einen Fehler produziert. Beispielsweise beim Bezug auf das Blatt „Alfa Romeo“, „Aston Martin“, „Rolls-Roycs“, und so weiter. Klar, ein Bezug auf das Blatt „Alfa Romeo“ wird dargestellt als:
=’Alfa Romeo‘!P1
Da der Blattname ein Leerzeichen (oder Gedankenstrich) enthält, muss ich den Apostroph aus dem Blattnamen löschen. Wirklich? Sollte ich ihn nicht besser von links und rechts löschen? Also: Wenn das erste Zeichen = ‚, dann entfernen. Wenn das letzte Zeichen = ‚, dann entfernen? Kann ein Blattname ein Apostroph enthalten? Ich probiere aus:
Tatsächlich: Mercedes‘ Benz funktioniert! Excel verbietet bei Namen von Tabellenblättern am Anfang und am Ende ein Apostroph, aber innerhalb des Namens ist es erlaubt.
Und wie sieht der Bezug auf dieses Blatt aus?
='Mercedes'' Benz'!W117
Erstaunlich! Der Apostroph wird entwertet, indem das Zeichen zwei Mal geschrieben wird! Das ist mir noch nie aufgefallen!
Das heißt: ich muss zwei Hochkommata (‚ ‚) durch eines ersetzen und muss den Apostroph am Anfang und am Ende löschen. Perfide!
Übrigens: Bevor Sie jetzt erboste Kommentare schreiben: die in Sindelfingen produzierende Automobilfirma heißt MERCEDES BENZ – ohne Apostroph!
Ich öffne eine Testdatei, die mir ein Anwender zugeschickt hat, und wundere mich, wo das Menüband (das Ribbon) ist.
Dann fällt es mir ein: es gab da doch … Richtig:
Es gab einen Excel 4.0-Makrobefehl, mit dem man die Symbolleiste ausblenden konnte. Dieser funktioniert im aktuellen Excel noch immer und blendet hier das Menüband aus. Der Befehl lautet:
mein Bekannter hat
sich die neue Version angeschaut.
Jetzt lässt sich der Datensatz zwar anlegen, aber es treten neue Fehler auf, die bisher noch nicht vorhanden waren.
In der Tabelle erscheint nach dem Löschen des Datensatzes ein Fehler.
Hallo Herr L.,
öffnen Sie mal bitte eine ältere Version und klicken Sie dort auf Datenverwaltung und löschen Sie einen Datensatz.
Klicken Sie anschließend auf den Datensatz direkt über dem Datensatz, den Sie gelöscht haben.
Sie erhalten den Fehler.
Heißt: DIESER
Fehler war schon lange drin, bevor Sie mir die Datei geschickt haben.
Woher kommt er?
Beim Löschen einer Zeile liefert die Zeile darüber einen Fehler:
Sie greifen auf den
VALUE dieser Zelle zu – das knallt!
Die Ursache des
Fehlers:
In der Spalte K (Membership) greift die Formel für den Wert „Silber“ auf die Zeile darunter zu! Die Formeln dieser Spalte sind falsch! Schon bevor Sie mir die Datei geschickt haben!
=WENN(UND(H2="Spain";J2>200);"Black";WENN(UND(H2="Spain";J2>180);"Platinum";WENN(UND(H2="Spain";J2>150);"Gold";WENN(UND(H3="Spain";J2>140);"Silber";WENN(UND(H2="Spain";J2>130);"Standard";WENN(UND(H2="Spain";J2>50);"Blue";"out of order"))))))
Fazit: es sind nicht neue Fehler – in Ihrem Programm sind einige alte Fehler, für die ich mich nicht verantwortlich zeichne.
An dieser Maske dlgKundenverwaltung habe ich nichts geändert.
schöne Grüße
Rene Martin
*) An Gott glauben wir; alle anderen müssen Daten zeigen!
mir ist ein Fehler
aufgefallen, der vorher nicht vorhanden war.
Die Excel-Tabelle
in Excel 365 funktioniert tadellos, soweit ich getestet habe.
Ein Freund von mir
hat leider aus der alten Version die Daten nicht in die neue Version reinbekommen
und so hat er sich entschlossen, diese neu einzugeben.
Wenn er ein Konto
anlegt – das geht noch.
Dann will er
Einzahlungen in das Einzahlformular hinzufügen, dies scheint erst mal nicht zu
funktionieren. In der Liste zeigt er nichts an!
Wenn ich mir die
Tabelle außerhalb des Formulars anschaue, sind die Daten angelegt – Die
Paketnummer wird nicht mehr hochgezählt.
Auch ein speichern
und erneuter Start der Tabelle bringt keine Abhilfe.
In der alten Version (bei mir 6.11) funktioniert es noch tadellos. Mein Bekannter setzt Excel 2016 (neuste Updates sind installiert) ein. Bei mir geht es, bei ihm nicht, mit der letzten Version.
#####
Hallo Herr L.,
in Ihrem Programm finde ich die Codezeile:
If rngI.Value = Me.cmbAuswahlKontoAlleInvestments.Value Then
Sie prüfen, ob eine Kontonummer ausgewählt wurde. Da Kontonummern Zahlen sein können (4711) vergleichen Sie diese Zahl mit dem TEXT aus der Combobox (alle Steuerelemente liefern immer Texte).
Deshalb kann es
nicht funktionieren! Der Fehler war vorher schon vorhanden; ist Ihnen in IHRER
Liste nicht aufgefallen, weil dort alle Konten alphanumerisch ist.
Ich habe es korrigiert:
If CStr(rngI.Value) = Me.cmbAuswahlKontoAlleInvestments.Value Then
Ich erstelle eine Userform in Excel VBA. Darin sollen Werte „nach unten“ weitergegeben werden, wenn ein Kontrollkästchen angeklickt wird. Die Kästchen heißen chkFamilie02, chkFamilie03, chkFamilie04, … chkFamilie12
Ich prüfe, ob das letzte ausgewählt wurde oder ob noch weitere unten ausgewählt wurden:
Do Until frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Visible = False Or frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Value = False Or intZeile > 12
Das läuft an die Wand – eine Fehlermeldung ist die Folge. Mein Denkfehler:
wenn intZeile > 12, dann wird das geürft. Beispielsweise: intZeile hat den Wert 13. Allerdings: es wird auch geprüft, ob das Control chkFamilie12 sichtbar ist. Und das gibt es nicht!
OR (und auch AND) in VBA prüft (leider!) immer alle Teile. Und stoppt nicht, wenn einer der beiden Zweige falsch ist. Also anders gelöst – nicht ganz elegant – aber okay:
Do Until frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Visible = False frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Value = False
strZeile = Format(intZeile, "00")
frmAuswahl.Controls("lblInfoZeile" & strZeile).Caption = strText
intZeile = intZeile + 1
If intZeile > 12 Then Exit Do
Loop
Wenn man mit VBA programmiert und wissen möchte, ob in einem Text ein anderer vorhanden ist, kann man mit den Funktionen Left, Right, Mid oder Instr arbeiten. Oder den Vergleichsoperator Like verwenden. Also beispielsweise
If txtIBAN.Value Like „DE*“ Then …
Die Aufgabe: ich will Namen in Excel prüfen. Ich möchte wissen, ob sie auf intelligente Tabellen verweisen, also einen Aufbau haben, wie beispielsweise:
=tbl_Feiertage![#Alle]
oder:
=tbl_Feiertage[2021]
Also prüfe ich:
For i = 1 To ThisWorkbook.Names.Count
If ThisWorkbook.Names(i).RefersToLocal Like "=*[*]" Then
MsgBox ThisWorkbook.Names(i).Name & " bezieht sich auf: " & ThisWorkbook.Names(i).RefersToLocal
End If
Next
Und wundere mich, warum die If-Verzweigung nichts findet.
Okay – noch ein Versuch – ich lasse die letzte eckige Klammer weg und prüfe erneut:
For i = 1 To ThisWorkbook.Names.Count
If ThisWorkbook.Names(i).RefersToLocal Like "=*[*" Then
MsgBox ThisWorkbook.Names(i).Name & " bezieht sich auf: " & ThisWorkbook.Names(i).RefersToLocal
End If
Next
Verwundert reibe ich mir die Augen. Was klappt nicht? Die Prüfung
If ThisWorkbook.Names(i).RefersToLocal Like "=*" Then
Bernhard hat mich darauf aufmerksam gemacht. Ist mir bislang nicht aufgefallen.
Die Funktion DATEDIF in Excel und die VBA-Funktion DateDiff rechnen unterschiedlich.
Trägt man in zwei Zellen die Datumsangaben 20.05.2021 und 01.06.2021 ein, so beträgt bei der Excelfunktion DATEDIF mit dem Parameter „M“ das Ergebnis 0, bei der VBA-Funktion dagegen 1.
Ich habe eine kleine Tabelle aufgebaut: im oberen Teil einige Datumsdifferenzen auf Basis des Monats:
Im unteren Teil verwende ich ein kleines VBA-Makro:
Sub BerechneDateDIFF()
Dim intZeile As Integer
Dim intSpalte As Integer
For intZeile = 21 To 30
For intSpalte = 2 To 20
ActiveSheet.Cells(intZeile, intSpalte).Value = DateDiff("M", ActiveSheet.Cells(intZeile, 1).Value, ActiveSheet.Cells(20, intSpalte).Value)
Next intSpalte
Next intZeile
End Sub
Das Ergebnis:
Die Unterschiede habe ich mit einer bedingten Formatierung farblich hervorgehoben.
Ein Dankeschön für den wertvollen Hinweis an Bernhard Ramroth.
In verschiedenen Programmiersprachen gibt es verschiedene Konventionen für die Benennung der Dinge. Beispielsweise die Variablen oder Parameter. Zu den Namenskonventionen gehören beispielsweise die Reddick-Namenskonventionen, in denen eine String oder Long-Variable mit strNachname oder lngZeile benannt wird. Oder die ungarische Notation, in welcher diese variablen stNachname und lZeile genannt würden.
Wer in VBA programmiert stellt schnell fest, dass die Kombinationsfelder, Listenfelder, Register und Multiseiten eine andere Struktur haben als die anderen Steuerelemente auf den Dialogen (Userformen):
Nicht nur, dass sie Null-basiert sind (anders als die übrigen Elemente), die Parameter werden nicht mit „sprechenden“ Namen angezeigt (wie eigentlich üblich), sondern gemäß der dort verwendeten Namenskonvention:
Und so bin ich glatt reingefallen, weil der letzte Parameter nicht IIndex (also mit zwei „ii“) heißt, sondern klein-L-Index … Verwirrend!
Eine Sache, die auch bei mir hier (trotz 6GB Arbeitsspeicher) immer wieder kommt, ist folgendes:
Nicht genügend Systemressourcen.
Debuggen kommt dann das hier.. Beim Ausführen einer ganz normalen SQL Anweisung, (zugegeben in einer Rekursion) bleibt er hier stecken..
Auf was muss ich denn da besonders achten?.. Oder könnte man das mit speziellen Funktion abfangen?
Viele Grüße nach München, Bernd
Hi Bernd,
Ich kenne solche Fehler … Nervig!
Versuch mal die Anweisungen rst!dblAnzWdh und
Nz(rst!dblWeightPart, 0) in zwei (Double-)Variablen zu schreiben und dann das
Produkt auszuführen (das Ergebnis wieder in eine dritte Variable), ebenso die
rst!ID.
Einfach nicht aufgepasst. Dabei weiß ich das eigentlich!
In einem großen, langen VBA-Programm werden Daten aus verschiedenen Dateien des gleichen Ordners, in dem sich die Datei mit dem Code befindet, zusammengefasst. Diese Dateien soll geöffnet werden, Inhalt herauskopiert und danach wieder geschlossen werden.
Ich überprüfe im ersten Schritt, ob es sich um eine XLSM oder XLSB-Datei handelt, denn nur dort liegen die gesuchten Daten:
If Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb" Then
Klappt wunderbar.
Fast.
Denn auch die Datei, in der sich der Code befindet, wird bearbeitet, also geschlossen. Das ist nicht Sinn der Sache. Also schließe ich aus:
If Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb" And strDatei <> ThisWorkbook.Name Then
und wundere mich, warum es nicht funktioniert. Die aktuelle Datei wird immer noch geschlossen. Seltsam.
Ich prüfe:
der Inhalt von strDatei entspricht ThisWorkbook.Name. Warum fließt diese Abfrage in die Bedingung, warum wird sie ausgewertet, oder genauer: warum liefert die IF-Verzweigung den Wert Wahr? Es dauert ein paar Sekunden, dann dämmert es mir:
AND ist stärker als OR. Die IF-Verzeigung überprüft, ob die Datei strDatei die Endung XLSB hat UND die gleiche Datei ist. Nein – ich laufende Datei habe die Endung XLSM. Oder: hast du die Endung XLSM? ja – DAS ist korrekt. Also wird die Bedingung ausgewertet. *ggrrrrr* Also noch einmal – diesmal mit Klammer:
If (Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb") And strDatei <> ThisWorkbook.Name Then
Kannst du auch einmal ein Tutorial über diese defekten Tabellen machen und wie man die wegbekommt? Bei meinen Recherchen habe ich bis jetzt nur gefunden das die erstellt werden wenn Excel abstürzt. Die einzige Lösung de in foren geholfen hatte, war eine neue Mappe zu erstellen und alle Daten zu übertragen. Aber das kann doch nicht die Lösung sein oder? Alle Änderungen die vorgenommen werden beziehen sich nur auf „DieseArbeitsmappe“
Andreas weiß keinen Rat. Und ich? Ich hatte mal eine solche korrupte Datei gesehen – ist schon eine Weile her. Und nein – sorry – ich weiß leider auch keinen Rat.
ich arbeite an einem VBA – Projekt, dass aus Power BI Dateien die
Metadaten rauslesen soll.
Das Auslesen geschieht über Power Query (what else….), aber ich
muss noch ein paar Prüfungen mit VBA erstellen und insbesondere die Power Query
Abfragen on the fly erstellen. Letzteres geht problemlos.
Der Ablauf:
Prüfe, ob User die pbix geöffnet hat.
Falls nicht, bitte freundlich darauf aufmerksam machen
Falls nein, Abbruch – falls ja, pbix öffnen.
Bis dahin klappt alles.
Nun kommt der Punkt, wo der Benutzer sich gegenüber der Power BI
Datei authentifizieren muss, nachdem er ja gesagt hat.
falls er aber den Dialog hier abbricht (…..DAU…….), kommt
eine „schöne“ Meldung:
Nun meine Frage:
Wie kann ich hier meine eigene Meldung einbauen und vor allem, wie
fange ich das ab?
Bin schon voller Zweifel…..
Merci, lieber René für deine Geduld mit mir
Freundliche Grüsse Hans Peter
########################################
die unwissenden erleuchten sich selber
habs gefunden. nach Drücken von „Senden“ fiel es mir wieder ein, da stand was im Buch von René
Hallo Rene, ich benötige BITTE DRINGEND Deine Hilfe!!! Ich habe hier irgendetwas abgeschossen…… Kann eine ausgeschnitte Zeile nicht mehr einfügen…. DANKE
es geht es geht…… DANKE DANKE DANK!!!!!!!!!!! Was war es???
vor der Antwort eine Frage: klick mal bitte in Excel auf Datei / Konto. hast du auch Version 2102?
Hallo JA, die habe ich auch. wobei jetzt gerade ein update läuft…. 🙁 hoffe es geht dann noch alles…. habe jetzt: Version 2102 Build 13801.20294 ABER ES LÄUFT!!!
Eben, Jörg:
Microsoft macht Updates. ich vermute stark, dass Microsoft etwas kaputt gemacht
hat.
In eurem Makro
verwendet ihr ein uraltes Tool, um Masken (Dialoge) anzuzeigen. Aus Excel 4.0.
Da lief ungefähr in der Zeit, als die Dinosaurier ausstarben. Ich habe dich ja
im Dezember mal gefragt, ob man (ich) nicht mal den ganzen alten Schrott, der
in euren Makros steckt, erneuern soll. Und ich fürchte: heute ist es passiert –
da wird etwas aus den 90er Jahren nicht mehr unterstützt …
Ich habe dir im
Dezember einen Screenshot von der Maske geschickt. Deshalb wusste ich, dass sie
damals bei mir noch lief. Heute bei mir auch nicht mehr.
ich habe die Maske
mit den aktuellen Bordmitteln nachgebaut – deshalb sieht sie ein klein wenig
anders aus … beispielsweise der Titel in der Titelzeile.
Ich werde das mal
in Foren posten und fragen, ob jemand eine ältere Version hat – ob es da noch
läuft …
Bevor dein Chef
dich tötet, flehe um Gnade und sag ihm einen schönen Gruß von mir, dass
Microsoft Sachen kaputt macht … nicht nur du …
Liebe Grüße
Rene
####
Jörg war glücklich. Ich habe ihm vorgeschlagen, das Makro zu überarbeiten. Habe ihm ein Angebot gemacht. Er hat sich seitdem nicht mehr gemeldet.
nachfolgender Code läuft fehlerfrei unter
Win 7 + Word 2010
Win 7 + Word 2013
Win 10 + Word 2019
Win 10 + Word 365
nur nicht unter Win 10 + Word 2016.
Die rot markierte Zeile ist mein Problemkind.
Vielleicht hast Du noch eine Idee.
Sub TopAktuell_Einfügen()
Application.ScreenUpdating = False
DocPath = "O:\Topaktuell\"
ChDrive ("O:\")
ChDir ("O:\Topaktuell\")
Dim datei As String
Selection.EndKey Unit:=wdStory
For dokumente = 1 To 30
If Dir(DocPath & dokumente & "_1.docx") = "" Then Exit For
For seiten = 1 To 20
datei = dokumente & "_" & seiten & ".docx"
If Dir(DocPath + datei) = "" Then Exit For
Selection.InlineShapes.AddOLEObject ClassType:="Word.Document.12", _
FileName:=datei
Next
Next
Application.ScreenUpdating = True
End Sub
Danke.
LG Traudl
Hi Traudl,
Seit über 20 Jahren schreibe ich VBA-Code. Dabei ist mir aufgefallen, dass sich einige wenige Objekte verändert haben. Ich habe mal vor vielen Jahren ein Makro geschrieben, das Text in eine Word-Tabelle geschrieben hat und den Text mit einem Hyperlink auf ein Word-Dokument versehen hat. Eine der Parameter beim Befehl Hyperlinks.Add wurde geändert.
Deshalb sage ich nicht, dass das nicht sein kann.
ABER! Bitte, bitte, bitte: adressiere doch sauber. Ändere seinen Code in: Dim wdBereich As Range
Set wdBereich = ActiveDocument.Range(Start:=ActiveDocument.Range.End - 1)
(Ende des Dokuments)
Und dort: wdBereich.InlineShapes.AddOLEObject ClassType:="Word.Document.12", _ FileName:=datei
Es kann natürlich sein, dass es trotzdem nicht unterstützt ist – aber eher unwahrscheinlich als das „Hoppeln“ an Ende: Selection.EndKey Unit:=wdStory Raus damit!
Und: wenn du gerade dabei bist: ChDrive ("O:\") ChDir ("O:\Topaktuell\")
Raus damit!
Und: deklariere die Variablen: Dim docpath As String Dim dokumente As Integer Dim seiten As Integer Dim datei as String
Und schließlich: Warum fügst du ein Word-Dokument in ein anderes ein? Ich würde es öffnen, an eine Objektvariable übergeben, den Inhalt (brauchst du die Formate?) entweder in einer Stringvariable speichern oder kopieren und ins Dokument einfügen.
Hilft das?
Liebe Grüße
Rene
Ich danke Dir ganz, ganz herzlich
für Deine Vorschläge, lieber René.
Den Code hatte ich 2017 erstellt und
war froh, dass er läuft. Er ist eine Sub aus einem unfangreichen Makro. Ich bin
allerdings keine Programmiererin. Dazu benutze ich VBA viel zu wenig.
Eine Weile habe ich gebraucht, bis ich es verstanden habe. Excel behauptet, dass auf diesem Tabellenblatt eine Verknüpfung zu einer anderen Datei liege.
„Da liegt nix“, denke ich: auf dem Blatt befindet sich nur ein Button, der ein Makro aufruft:
Nach einer Weile dämmert es mir. Ich kopiere regelmäßig den Bereich von anderen Dateien hier auf dieses Blatt. Und richtig: damit auch die Schaltflächen. Und mit ihnen die Verknüpfungen auf andere Dateien. *gggrrrr*
In einem „unserer“ Excel-Stammtische hast Du uns ein Besispiel gezeigt, wie Du durch ein Klick auf ein Bild „Inhalte aktivieren“ bestätigen kannst (d.h. der Benutzer muss nicht oben auf den orangefarbende Balken klicken , um die Makros der Arbeitsmappe zu aktivieren…).
Leider
musste ich feststellen, dass meine Kollegen gerne die
Aufforderung „Inhalte aktivieren“ übersehen und so meine
Arbeitsmappen mit Makros nicht aktiviert werden. Um das ersichtlich zu machen
bzw. zu erzwingen, würde ich gerne Deine Lösung einbauen.
Kannst
Du mir dazu bitte Dein Beispiel zukommen lassen?
Das
wäre großartig!
Vielen
Dank & Grüße
Sebastian
so!
Ich habe ein Bild auf das Blatt gelegt, das ich beim Öffnen
per Makro unsichtbar mache.
Werden die Makros nicht aktiviert, bleibt das Bild sichtbar.
Die Erklärung: Manchmal beschweren sich Anwender, dass bestimmte Befehle deaktiviert sind. Es wäre praktisch ein Makro zu haben, das meldet, dass die Makros nicht aktiviert wurden. Dies geht natürlich nicht. Um sicherzustellen, dass die Makros aktiviert wurden, kann in ein Projekt ein Bild eingefügt werden.
Werden die Makros nun aktiviert, wird das Bild gelöscht.
Man kann Shapes einen Namen geben oder mit einer Schleife
alle Shapes / Bilder durchlaufen und entweder löschen oder Unsichtbar machen
(Visible = False)
Das Bild wird beim Schließen der Arbeitsmappe wieder
eingeblendet:
Private Sub
Workbook_BeforeClose(Cancel As Boolean)
Ich war von Deiner Lösung damals so begeistert, dass die hängen geblieben ist.
Da ich allerdings nicht mit
(externen) Kunden arbeite, hatte ich keinen Anwendungsfall – bis ich letzte
Woche erfahren durfte, dass meine Kollegen gerne den Balken übersehen und so
nicht meine Datei funktioniert.
Ich werde Deine Lösung am Montag
einbauen- das Bild wird selbstverständlich geändert.
Ich erstelle für einen Kunden in Excel mit VBA ein mächtiges Eingabeformular. Schnell sind wir uns einig darüber, der der Anwender und die Anwenderin nicht mit dem Befehl Suchen-Ersetzen Texte auf einem Tabellenblatt austauschen sollen. Also nehmen wir in diesem Formular dieses Symbol (genau: die ganze Gruppe) aus dem Menüband:
Dazu sind ein paar Zeilen in der XML-Datei nötig, in der das Menüband beschrieben wird:
Und wie wird ersetzt? Natürlich über ein eigenes Werkzeug:
Ich überlege: dem Anwender und der Anwenderin bleiben immer noch die Möglichkeit mit den Tastenkombinationen [Strg] + [F], beziehungsweise [Strg] + [H] den Suche-Dialog zu öffnen. Also raus damit:
Private Sub Workbook_Open()
On Error Resume Next
Application.OnKey "^f", "BitteNicht" ' suchen
Application.OnKey "^h", "BitteNicht" ' ersetzen
End Sub
Beim Öffnen der Datei werden diese beide Tastenkombinationen „verbogen“, indem das Makro „BitteNicht“ aufgerufen wird. Es erscheint ein Meldungsfenster. Diese Prozedur wird auch beim Aktivieren der Mappe gestartet:
Private Sub Workbook_Activate()
Schnell merke ich, dass das Makro nach Schließen der Datei (oder auch Wechseln in eine andere Datei) noch aktiv ist. Also: raus damit:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnKey "^f", "" ' suchen
Application.OnKey "^h", "" ' ersetzen
End Sub
Ebenso beim Ereignis Workbook_Deactivate.
Und was passiert? Richtig – wenn ich jetzt [Strg] + [F] drücke, passiert: NICHTS. Warum? Genau – ich muss natürlich schreiben:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnKey "^f" ' suchen
Application.OnKey "^h" ' ersetzen
End Sub
Merkwürdig. Ich importiere per VBA Daten aus anderen Tabellen in eine Arbeitsmappe. Die Daten der Importtabelle sind als intelligente Tabelle gespeichert, in der aktuellen Tabelle liegt auch eine intelligente Tabelle. Damit sich diese beiden Tabellen nicht überlagern, ist es wohl das Beste die Tabelle zu löschen. Aber wie heißt der Befehl „In Bereich konvertieren“?
Nun – der Makrorekorder hilft:
Sub ZurueckZuDummerTabelle()
'
' ZurueckZuDummerTabelle Makro
'
'
End Sub
Nein – der Makrorekorder hilft nicht! Er zeichnet nur den Befehl auf: lösche das Tabellenformat. Aber nicht: lösche die Tabelle. Also muss ich doch auf die Suche gehen. Ich werde schnell fündig: die Methode heißt Unlist. Damit klappt es:
For j = 1 To xlBlatt.ListObjects.Count
xlBlatt.ListObjects(j).TableStyle = ""
' -- lösche die Formtierung
xlBlatt.ListObjects(j).Unlist
' -- in Bereich (zurück) konvertieren
Next j
Tabellenblätter in Excel in Excel haben nicht nur einen (sichtbaren) Namen, der vom Anwender oder von der Anwenderin geändert werden kann. Im VBA-Editor gibt es auch noch einen Name (Codename), der nur dort und nur per Hand geändert werden kann. Ich kann nun per Programmierung leicht überprüfen, ob alle Tabellen, die ich benötige, noch vorhanden sind. Ich habe eine Funktion GibtEsTabellenblatt geschrieben, die überprüft, ob die aktuelle Datei ein Tabellenblatt mit einem solchen Codenamen hat. Ich möchte die Arbeitsmappe nicht schützen, weil der Anwender oder die Anwenderin neue Blätter hinzufügen, löschen, umbenennen darf. Allerdings: meine Blätter dürfen nicht gelöscht werden. Ich sehe nach:
Jedes der Tabellenblätter hat ein Ereignis BeforeDelete:
Dieses Ereignis hat allerdings kein Abbruchkriterium (Cancel), mit dem man das Löschen verhindern könnte.
Der zweite Blick fällt auf das Objekt Workbook. Gibt es dort ein Abbruchparameter?
Das Ereignis dort heißt SheetBeforeDelete und hat auch keinen solchen Parameter:
Allerdings einen Parameter Sh mit dem ich gezielt überprüfen kann, welches Blatt nicht gelöscht werden darf (und die Liste schnell erweitern kann:
Select Case Sh.CodeName
Case "tbl_Unternehmen", "tbl_Standorte", "tbl_Zuordnung1", "tbl_Organisationseinheit", "tbl_Zuordnung2", "tbl_Geschaeftsprozesse", "tbl_Zuordnung3", "tbl_Uebungstyp", "tbl_Szenario", "tbl_Verantwortlich", "tbl_Uebungsplanung", "tbl_Zuordnung4"
MsgBox "Bitte löschen Sie nicht das Tabellenblatt """ & Sh.Name & """!", vbCritical
End Select
Problem: Die Meldung wird angezeigt und DANN das Blatt gelöscht. Doof!
Nächster Versuch: Und wenn ich DANACH die Arbeitsmappe schütze?
Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
On Error Resume Next
Select Case Sh.CodeName
Case "tbl_Unternehmen", "tbl_Standorte", "tbl_Zuordnung1", "tbl_Organisationseinheit", "tbl_Zuordnung2", "tbl_Geschaeftsprozesse", "tbl_Zuordnung3", "tbl_Uebungstyp", "tbl_Szenario", "tbl_Verantwortlich", "tbl_Uebungsplanung", "tbl_Zuordnung4"
MsgBox "Bitte löschen Sie nicht das Tabellenblatt """ & Sh.Name & """!", vbCritical
ThisWorkbook.Protect
End Select
End Sub
Klappt! Hier die drei Meldungen, die erscheinen:
Allerdings: DANN ist die Arbeitsmappe geschützt. Was ich ja eigentlich nicht wollte. Also flux den Arbeitsmappenschutz wider aufheben. Wo? Am besten beim Wechseln auf ein anderes Arbeitsblatt:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
ThisWorkbook.Unprotect
End Sub
Im VBA-Editor kann man den (internen) Namen eines Tabellenblattes ändern. So kann man auf dieses Blatt über diesen (Code-)Namen zugreifen, egal an welche Position es der Anwender oder die Anwenderin schiebt; egal ob es in Excel umbenannt wird. Allerdings kann es der Anwender oder die Anwenderin löschen. Kein Problem – ich baue einen Button ein, der ein neues Blatt erzeugt:
Dim xlBlattNeu As Worksheet
Set xlBlattNeu = ThisWorkbook.Worksheets.Add
xlBlattNeu.CodeName = "tblITAnwendungen"
*gggrrrrr* CodeName ist schreibgeschützt.
Zuweisung an schreibgeschützte Eigenschaft nicht möglich.
Kann also nicht in VBA umbenannt werden. Ich kann nur überprüfen, ob das Blatt vorhanden ist. Aber nicht (den Namen) erzeugen. Schade! Ärgerlich! Aber verständlich.
Problem: der Anwender oder die Anwenderin kann das Blatt verschieben, wenn die Arbeitsmappe nicht geschützt ist. Außerdem ist die Zählung bei ausgeblendeten Blättern schwierig.
Es gibt noch eine dritte Variante. Im VBA-Editor kann man in den Eigenschaften einen Namen festlegen:
Über diesen Namen kann man auf das Blatt zugreifen, beispielsweise:
tbl_Uebungsplanung.Range("F1").Value = _
tbl_UnternehmenCode.Range("A1").Value ' -- Unternehmen
Auch der Zugriff auf ein Blatt über seinen Codename in einer anderen Datei geht nicht!
Dim xlDatei As Workbook
Set xlDatei = Application.Workbooks.Open("D:\Übungspanung.xlsx")
MsgBox xlDatei.tbl_UnternehmenCode.Range("A1").Value
Schade! Man muss mit einer Schleife über alle Blätter iterieren und abfragen, ob der Codename = „tbl_UnternehmenCode“. Und darauf einen Verweis setzen.
Ich erstelle gerade ein größeres VBA-Projekt für einen Kunden. Und bin mal wieder verblüfft! Die größte Verblüffung hatte ich letzte Woche. Man erstelle in Excel eine XLSM-Datei mit Makros. Ein Makro wird über eine Schaltfläche (oder ein Bild, ein SmartArt, eine Form, ein Diagramm) aufgerufen:
Ich kopiere das Tabellenblatt in eine andere Datei
und speichere die Datei OHNE Makros als XLSX! Ich schließe alles, öffne die XLSX-Datei und werde gefragt, ob ich die Makros aktivieren möchte:
Okay. Ich aktiviere die Datei. Ein Klick auf die Schaltfläche und werde darauf hingewiesen: Microsoft Office hat ein potenzielles Sicherheitsrisiko erkannt.
Was passiert? Die andere Datei wird im Hintergrund geöffnet, das Makro wird ausgeführt! Denn: hinter der Schaltfläche steht noch immer der Dateiname und Makroname.
Fazit: Mein Glaubenssatz: XLSX-Dateien sind sicher, denn sie können keine Makros enthalten, ist erschüttert! Zwar enthalten XLSX-Datei weiterhin keine Makros, können aber Makros aus anderen Dateien aufrufen!
also: ZÄHLENWENN, leistet, was ich möchte: ein Befehl (ohne Schleife) und ich habe die Information (Wert in der Spalte oder nicht vorhanden). Ebenso verwende ich häufig SUMMEWENN:
Application.WorksheetFunction.SumIf
oder – um die Zeilennummer zu ermitteln VERGLEICH:
So spare ich mir das Schreiben von Schleifen. Nun wollte ich die kumulierten Geldbeträge zu bestimmten Monaten wissen. In Excel lautet die Funktion
=SUMMENPRODUKT((MONAT(A:A)=1)*(B:B))
also: summiere die Werte der Spalte B, wenn eine Datumsangabe in der Spalte A ein Datum des ersten Monats (Januar) im Jahr enthält. Klappt wunderbar. Und in VBA? Dort versagt eine Zeile wie:
Die Ursache ist schnell gefunden: Colums(1) = 1 kann nicht verarbeitet werden; auch nicht Month(Columns(1)); der Gleichheitsoperator in VBA ist nicht matrixfähig; „=“ kann nur identische Dinge vergleichen.
Ein Freund von mir erklärt mir häufig, dass er sich nicht bei facebook anmeldet, weil fb ein Zeitfresser sei. Nun: ich kenne einen anderen Zeitfresser. Er heißt: VBA! Kennt ihr folgende Anomalie? Ich erstelle ein Userform mit einem Listenfeld. Zu den Einträgen sollen mehrere Einträge ausgewählt werden können. Ich entschließe mich für ein zweites Listenfeld, das ich daneben platziere. Dem Auftraggeber gefällt es nicht, weil die Userform groß und unübersichtlich ist (sehr viele Steuerelemente). Ich entschließe mich zu einer zweiten Userform. Also: Eintrag auf dem Listenfeld wird ausgewählt: Doppelklick, eine zweite Userform wird geöffnet, mit Werten gefüllt, von denen der Anwender mehrere auswählen kann (MultiSelect).
Zirka zwei Stunden lang habe ich über folgendes Problem gestutzt: Während des Doppelklicks öffnet sich die zweite Userform. Das Klickereignis wird schon abgefangen und sorgt dafür, dass auf der zweiten Liste der zweiten Maske auch Einträge selektiert werden. Sämtliche Versuche per VBA alles zu deselektieren (Selecetd(i) = False) scheitern! Ich habe mich dann entschieden die Userform umzubauen. Das Listenfeld der zweiten Form liegt nicht mehr über dem Listenfeld der ersten. Dann klappt es!
Böses, böses Excel – manchmal nervst du! Also doch lieber facebook – dort vertrödle ich weniger Zeit!
Ich erstelle ein VBA-Projekt in Excel, das auf dem Mac und auf dem PC laufen soll. Da die Trennzeichen zwischen den Ordnern unterschiedlich sind („\“ auf PC, „/“ auf Mac), überprüfe ich, auf welchem System das Programm gerade läuft. Die Funktion
=INFO(„SYSTEM“)
liefert entweder „pcdoc“ oder „mac“. Prima!
Dann kann ich das doch in VBA verwenden. Ich werde eines Besseren belehrt:
Die Funktion INFO (oder Info) findet sich nicht in der Liste der Worksheetfunctions! Objekt unterstützt diese Eigenschaft oder Methode nicht. Lautet die Fehlermeldung. Abhilfe schafft der Befehl die Funktion in eine Zelle zu schreiben, den Wert auszulesen und die Formel wieder zu löschen. Beispielsweise so:
Dim xlBlatt As Worksheet
Dim strSystem As String
Set xlBlatt = ActiveSheet
xlBlatt.Range("A1").FormulaR1C1 = "=INFO(""SYSTEM"")"
strSystem = xlBlatt.Range("A1").Value
MsgBox strSystem
xlBlatt.Range("A1").ClearContents
Hallo, i ch benötige kurze Hilfe, ich habe hier ein Makro, noch von meinem Vorgänger, das wohl in einer Excel Liste eine Zeile entfernt und wo anders dann wieder einsetzen kann. Nun ist es aber so, das ich Zeilen habe, die einen Zeilenumbruch in der Zelle haben, und dann immer eine Fehlermeldung 1004 kommt. Können Sie mir hier BITTE helfen???? Danke Jörg
Hallo Jörg, so groß und schwierig kann das ja wohl nicht sein – wenn das Makro vorher gelaufen ist … schick es mir doch mal, bitte, dann schreibe ich dir etwas dazu. Liebe Grüße Rene
Hallo Rene,
hier mal so….
In einer Excel Zelle
habe ich einen Textumbruch, mit sehr viel Text.
Den kann ich zwar über
den Makro „löschen“, aber dann nciht
mehr an einer anderen
Stelle einfügen…..
BITTE Um HILFE! DANKE
Sub FahrtAuswaehlenEinfach()
Dim aDlg As DialogSheet
Dim aRow As Range
Set aDlg = Sheets("Fahrtauswahl")
i = 1
aDlg.ListBoxes("Fahrten").RemoveAllItems
Do While Sheets("Tabelle2").Cells(i, 2).Value <> ""
astring = Format$(Sheets("Tabelle2").Cells(i, 2).Value, "short time")
astring = astring + " - " + Format$(Sheets("Tabelle2").Cells(i, 3).Value, "short time")
astring = astring + " " + Sheets("Tabelle2").Cells(i, 4).Value
astring = astring + " --- " + Sheets("Tabelle2").Cells(i, 8).Value
astring = astring + " / " + Sheets("Tabelle2").Cells(i, 11).Value
aDlg.ListBoxes("Fahrten").AddItem astring
i = i + 1
Loop
If aDlg.Show = True Then
i = aDlg.ListBoxes("Fahrten").ListIndex
Sheets("Tabelle2").Rows(i).EntireRow.Cut
Sheets("Linienpläne - quer").Select
ActiveCell.EntireRow.Insert Shift:=xlDown
Sheets("Tabelle2").Rows(i).EntireRow.Delete xlUp
End If
End Sub
Zuerst musst ich schlucken und mich auf die Suche machen. Was bitte ist ein DialogSheet? Ich werde fündig: in Excel 4.0 konnte man ein Dialogblatt einfügen. Und darauf Steuerelemente platzieren. Und die verwenden so etwas immer noch im Jahre 2020?
Egal. Meine Vermutung liegt beim Operator „+“. Wenn in den Zellen Zahlen stehen, hat VBA ein Problem beim Verketten von
Das ist aber nicht die Lösung. Ich bitte um die Daten. Der Zeilenumbruch ist nicht das Problem, sondern:
Der Text ist zu lang! Excel lässt mehr als 8.000 Zeichen / Zelle zu, die ListBox leider nur 255
Also schnell den Code geändert:
Sub FahrtAuswaehlen()
Dim aDlg As DialogSheet
Dim aRow As Range
Set aDlg = Sheets("Fahrtauswahl")
i = 1
aDlg.ListBoxes("Fahrten").RemoveAllItems
Do While Sheets("Tabelle2").Cells(i, 2).Value <> ""
astring = Format$(Sheets("Tabelle2").Cells(i, 2).Value, "short time")
astring = astring & " - " & Format$(Sheets("Tabelle2").Cells(i, 3).Value, "short time")
astring = astring & " " & Left(Sheets("Tabelle2").Cells(i, 4).Value, 200)
astring = astring & " --- " & Sheets("Tabelle2").Cells(i, 11).Value
aDlg.ListBoxes("Fahrten").AddItem astring
i = i + 1
Loop
If aDlg.Show = True Then
For i = aDlg.ListBoxes("Fahrten").ListCount To 1 Step -1
If aDlg.ListBoxes("Fahrten").Selected(i) Then
Sheets("Tabelle2").Rows(i).EntireRow.Cut
Sheets("Linienpläne - quer").Select
ActiveCell.EntireRow.Insert Shift:=xlDown
Sheets("Tabelle2").Rows(i).EntireRow.Delete xlUp
End If
Next
End If
End Sub
Ich erstelle per VBA in Excel dynamische Formulare, in die Daten eingetragen werden. Dabei greife ich auf Mastertabellen zu, die ich einblende, kopiere und modifiziere. Eine der Tabellen hat ein Kommentar (okay – eigentlich eine Notiz). Unter bestimmten Voraussetzungen muss ich ihn löschen.
Okay – ich hätte ihn auch umgekehrt einfügen können – ich entschließe mich für die Variante des Löschens:
ZelleLinksOben.Offset(i, 0).Comment.Delete
Allerdings erhalte ich einen Fehler, wenn in der Zelle kein Kommentar mehr vorhanden ist:
Wie kann ich überprüfen, ob eine Zelle einen Kommentar hat? Die Zelle hat ja keine Comments-Sammlung, so dass ich über die Eigenschaft Count herausbekäme, ob Kommentar oder nicht.
Ich schreibe eine Funktion, die auf einen Kommentar zugreift. Ist dieser nicht vorhanden, erhalte ich einen Fehler. Diesen kann ich abfangen:
Public Function GibtEsKommentar(Zelle As Range) As Boolean Dim blnVorhanden As Boolean Dim s As String On Error Resume Next Err.Clear s = Zelle.Comment.Text If Err.Number = 0 Then blnVorhanden = True Else blnVorhanden = False End If Err.Clear GibtEsKommentar = blnVorhanden End Function
Damit kann man (ich) arbeiten:
If RisikoanalyseTyp <> "Dienstleistung" Then
If GibtEsKommentar(ZelleLinksOben) Then
.Comment.Delete ' -- lösche den Kommentar
End If
End If
Nach einer Weile entdecke ich, dass es zum Löschen eines Kommentars einen anderen Befehl gibt: ClearComments
ZelleLinksOben.Offset(i, 0).ClearComments
Und er löscht einen vorhandenen Kommentar oder macht nichts, wenn sich in der Zelle kein Kommentar befindet. Warum nicht gleich so?
Ich finde ja diese Formulierungen ja immer so bemerkenswert,
wenn in der Presse die Rede von Microsoft Excel ist:
„VBA gehört
aber zu jenen Sprachen, die nahezu jedem den Verstand rauben und mit denen fast
niemand freiwillig programmiert. Wer es dennoch muss und dafür bezahlt wird,
wird die Bezahlung eher als Schmerzensgeld betrachten.“
Weiß nicht, ob du den 2. Satz bestätigen kannst. 😉 Viele Grüße, Dominic
Hi Dominic,
nein – das habe ich nicht gelesen. Ich würde ihm erwidern: „Wer so etwas schreibt hat diese Sprache weder gelernt noch verstanden.“
In Corona-Zeiten, in der meine Schulungen weggebrochen sind,
bin ich froh, dass ich VBA-Lösungen entwickeln darf. Ich mache das sehr, sehr
gerne!
Programmieren Sie VBA? Programmieren Sie Formeln in VBA? Auf einem Tabellenblatt befinden sich in den Spalten BJ und BL Daten, die vertikal angeordnet sind:
Auf einem anderen Blatt wird Bezug auf diese Daten genommen:
Während ich die erste Spalte nach unten ziehen kann, muss ich die Formeln der ersten Zeile getrennt eingeben – ich möchte weder mit MTRANS, INDIREKT noch mit BEREICH.VERSCHIEBEN arbeiten. Es handelt sich um maximal sechs Werte.
Die Formeln müssen per VBA neu geschrieben werden. Der Makrorekorder ermittelt für die Formel
Dabei ist RC[61] ein relativer Bezug: R -> gleiche Zeile; C[61] -> Spalte um 61 Spalten verschoben. Wäre der Bezug absolut ($BJ$2) wäre der Code:
R2C62
also: in Zeile 2 und in Spalte 62 – egal, wo sich die Zielzelle befindet. Der Code wird in einer Schleife verwendet:
For i = 1 To 6
ThisWorkbook.Worksheets("tbl_Risikomatrix").Range("A1").Offset(i, 0).FormulaR1C1 = _
"=IF(tbl_Basisdaten!RC[61]="""","""",tbl_Basisdaten!RC[61])" ' -- erste Spalte A
Next i
Klappt famos! Und nun die erste Zeile. Der Makrorekorder zeichnet auf:
For i = 1 To 6
ThisWorkbook.Worksheets("tbl_Risikomatrix").Range("A1").Offset(0, i).FormulaR1C1 = _
"=IF(tbl_Basisdaten!R[" & i & "]C[62]="""","""",tbl_Basisdaten!R[" & i & "]C[62])" ' -- erste Zeile 1
Next i
Padautz – das funktioniert nicht! In B1 greife ich auf Spalte 63 zu, also 62 Spalten „neben mir“. In C1 dagegen benötige ich die Spalte, die sich 61 Spalten neben mir befindet, in D1 dagegen 60 Spalten. Also noch einmal:
For i = 1 To 6
ThisWorkbook.Worksheets("tbl_Risikomatrix").Range("A1").Offset(0, i).FormulaR1C1 = _
"=IF(tbl_Basisdaten!R[" & i & "]C[" & (63 - i) & "]="""","""",tbl_Basisdaten!R[" & i & "]C[" & (63 - i) & "])" ' -- erste Zeile 1
Next i
Und das klappt! Man muss so aufpassen bei den Bezügen! Und beim Umdenken von =BL2 auf =R[1]C[62].
Erstaunlich. Ich programmiere eine Access-Datenbank, die Daten nach Excel exportiert und aufbereitet.
Die Excel-Datei liegt im XLS-Format vor und wird am Ende als XLSX gespeichert. Ich muss die Liste nach vier Kriterien sortieren. Deshlab verwende ich den „neuen“ Sortierbefehl, der seit Excel 2007 vorhanden ist:
i = xlBlatt.Range("I1").CurrentRegion.Rows.Count
xlBlatt.Sort.SortFields.Clear
xlBlatt.Sort.SortFields.Add2 Key:=xlBlatt.Range("I2:I" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
xlBlatt.Sort.SortFields.Add2 Key:=xlBlatt.Range("J2:J" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
xlBlatt.Sort.SortFields.Add2 Key:=xlBlatt.Range("M2:M" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
xlBlatt.Sort.SortFields.Add2 Key:=xlBlatt.Range("K2:K" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With xlBlatt.Sort
.SetRange xlBlatt.Range("I1").CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' -- sortiere, damit man zählen kann
Klappt. Klappt bei meinem Excel in Microsoft 365. Allerdings beim Kunden, der Office 2016 einsetzt – leider nicht:
Schade! Also verwende ich den „alten“ Sortierbefehl. Da er jedoch nur drei Sortierkriterien zulässt muss ich zwei Mal sortieren:
Im Frühjahr habe ich für einen Kunden ein kleines Projekt erstellt. Es geht darum, bestimmte Informationen in Visio einzusammeln und mit VBA nach Excel zu schreiben. Einige Stunden Programmierarbeit; das Projekt lief. Ich habe getestet, der Kunde hat getestet – kein Fehler.
Letzte Woche – nach einem halben Jahr – kam eine Mail, dass das Programm an einer Stelle nicht mehr korrekt rechnen würde. Ich war erstaunt und schaute es mit an. Tatsächlich: drei Fehler(chen) habe ich gefunden:
Fehler I
For i = 1 To vsSeite.Shapes.Count
If vsSeite.Shapes(i).CellExists("Prop._VisDM_ID", False) = True Then
intTemp = WelchesRack(vsSeite.Shapes(i))
For k = 1 To vsSeite.Shapes(i).Section(visSectionProp).Count - 1
[...]
Der Denkfehler ist Folgender: Das erste Shape hat die Nummer 1. Die erste Section allerdings die Nummer 0. Das heißt: In der Zeile
For k = 1 To …
beginnt die Zählung beim zweiten Element! Diese Liste ist nullbasiert!
Fehler II
Ich sammle Werte in einer Liste ein, die folgendermaßen aufgebaut ist:
Europa|Schweiz|Bern|42|12|33|9|4711|||
Einige Werte werden erhöht:
For k = 1 To vsSeite.Shapes(i).Section(visSectionProp).Count - 1
If vsSeite.Shapes(i).Section(visSectionProp).Row(k).Cell(2).FormulaU = """" & strTeil & """" Then ' 2 = Label
If vsSeite.Shapes(i).Section(visSectionProp).Row(k).Cell(0).FormulaU = "" Then
lngTempWert = 0
ElseIf IsNumeric(Replace(vsSeite.Shapes(i).Section(visSectionProp).Row(k).Cell(0).FormulaU, """", "")) = False Then
lngTempWert = 0
Else
lngTempWert = Split(strBerechnung(intTemp), "|")(j) + CLng(Replace(vsSeite.Shapes(i).Section(visSectionProp).Row(k).Cell(0).FormulaU, """", ""))
End If
Der Denkfehler: wenn der einzufügende Wert nicht korrekt ist, darf ich nicht eine 0 einfügen, sondern den alten Wert drinlassen. Also ich darf nicht schreiben:
strTemp = vsSeite.PageSheet.Cells("User.Berechnung" & (k + 1)).ResultStrU("")
If strTemp Like "*|*|*" Then
strTemp0 = Split(strTemp, "|")(0): strTemp1 = Split(strTemp, "|")(1): strTemp2 = Split(strTemp, "|")(2)
If strTemp0 <> "" And strTemp1 <> "" And IsNumeric(strTemp2) Then
strTempWert = Split(strBerechnung(i), "|")(k + 1)
dblBerechneterWert = Berechnung(CDbl(strTempWert), strTemp0, strTemp1, CDbl(strTemp2))
Call ShapeEinrichten(vsShapeUnten, Format(dblBerechneterWert, "0"))
If blnExcel = True Then
xlBlatt.Cells(intExcelZeile + 1, intAnzahlDaten + k + 1).Value = dblBerechneterWert
End If
End If
End If
Und für den zweiten Block – etwas versetzt:
strTemp = vsSeite.PageSheet.Cells("User.Berechnung" & (k + 5)).ResultStrU("")
If strTemp Like "*|*|*" Then
strTemp0 = Split(strTemp, "|")(0): strTemp1 = Split(strTemp, "|")(1): strTemp2 = Split(strTemp, "|")(2)
If strTemp0 <> "" And strTemp1 <> "" And IsNumeric(strTemp2) Then
strTempWert = Split(strBerechnung(i), "|")(k + 1)
dblBerechneterWert = Berechnung(CDbl(strTempWert), strTemp0, strTemp1, CDbl(strTemp2))
Call ShapeEinrichten(vsShapeUnten, Format(dblBerechneterWert, "0"))
If blnExcel = True Then
xlBlatt.Cells(intExcelZeile + 1, intAnzahlDaten + intGruppe1 + k + 1).Value = dblBerechneterWert
End If
End If
End If
Sehen Sie es? Das ist ein „klassischer“ Kopierfehler. Gleicher Block; muss ein bisschen abgeändert werden – Variable ausgetauscht oder Wert erhöht. Hier wird der Wert erhöht: Aus k + 1 wird k + 5:
Allerdings: die zweite Korrektur habe ich übersehen:
strTempWert = Split(strBerechnung(i), "|")(k + 1)
muss heißen:
strTempWert = Split(strBerechnung(i), "|")(k + 5)
Und warum ist das lange Zeit nicht aufgefallen? Weil diese Fälle (die Verarbeitung der ersten Sektion oder die Benutzung des zweiten Block) nicht verwendet wurden …
Nichts wird gelöscht! Also doch per Hand. Beispielsweise so:
Sub Bezug_Loeschen_02()
Dim xlZelle As Range
For Each xlZelle In ActiveSheet.UsedRange
If xlZelle.Text = "#BEZUG!" Then
xlZelle.ClearContents
End If
Next
End Sub
Der Grund ist verständlich: Der Suchen- und Ersetzen-Befehl ist sehr mächtig. So wird auch in „26.10.2020“ der Wert „2020“ gefunden, obwohl in der Zelle eigentlich der Wert 44.130 steht. Eben: #BEZUG! ist eigentlich ein Fehlerwert (und kein Text). Er steht ja auch in der Mitte der Zelle. Suchen und ersetzen „übergeht“ diesen Datentyp; der VBA-Befehl Replace sucht (und ersetzt) einen Text (was nicht in der Zelle steht).
Und wer nun fragt: „Wer braucht denn so etwas?“ Letzte Woche habe ich eine Datei mit fehlerhaften Bezügen bekommen. Der Anwender hat ein Blatt von einer Datei in eine andere kopiert. Ich wollte diese nichtssagenden Fehler mit einem Makro „rausputzen“ – Klick auf Button sollte die Datei „bereinigen“.
Und so habe ich festgestellt, dass der Replace-Befehle nicht geeignet ist.
Ich erstelle gerade benutzerdefinierte Funktionen (UDF) für Excel mit Visual Studio. Dazu habe ich das Projekt Excel-DNA eingebunden. Ich erstelle eine Klasse und wundere mich, warum ich nicht das aktuelle Framework habe. Excel-DNA verlangt ein Framework ab 4.3:
Es lässt sich auch nicht nachinstallieren.
Ich habe lange gesucht, bis ich es gefunden habe. Ich darf nicht die .NET Standard Klassenbibliothek verwenden:
Dort sind die aktuellen Frameworks nicht vorhanden:
Sondern ich muss auf die korrekte Windows Desktop Klassenbibliothek zurückgreifen:
Dann klappt es:
Oder auch mit dem älteren Visual Studio. Diese nicht:
Ach, wie dumm. Eine Fehlermeldung in meinem VBA-Projekt:
Der Grund? Ich lese den Value der Zelle aus. Die Fehlermeldung #NV ist allerdings kein Value. Mit der Eigenschaft „Text“ kann man sie abfangen.
Und wie kommt diese Fehlermeldung nach Excel?
Nun – die Daten wurden aus einer (Access-)Datenbank gezogen. Diese wiederum holt sich die Informationen von Visio. Und dort wurde – durch ungeschicktes Kopieren und Einfügen der Fehler #NA erzeugt. Er wurde in die Datenbank als Text eingetragen – allerdings bei Excel ist ein Fehler die Folge.
Ich habe ja schon mehr als einmal über verbundene Zellen geschimpft. Ich tue es heute mal wieder!
Ich bin gestern beim VBA-Programmieren (mal wieder) über verbundene Zellen gestolpert. Ich muss wissen, wie viele Spalten die Liste hat (weil ich in Zeile 4 den Begriff „Summe“ suche).
Der Befehle MsgBox ThisWorkbook.ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column liefert 1! Allerdings MsgBox ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.Columns.Count liefert 9! Ebenso: MsgBox ThisWorkbook.ActiveSheet.UsedRange.Columns.Count
Frage an den Experten: „Bei einigen der erstellten Exceltools hätte ich gerne den VBA Code geschützt. Aber leider ist der VBA Codeschutz sehr schwach, so dass ich es unterlassen habe und auch keine externen Programme hierfür erworben habe. Einem Kunden eine Exceldatei als .exe zu übergeben ist wohl nicht gerade das Richtige 😉
Letzthin habe ich aus Neugierde den Exactplanner zu
Testzwecken heruntergeladen. Und wie ich bin, schaue ich mir immer zuerst mal
an, ob die Datei auch VBA Code hat und wie der aussieht.
Aber diesmal habe ich eine Überraschung erlebt.
«Das Projekt kann nicht angezeigt werden!»
Oder war es «das Modul kann nicht angezeigt werden»?
Leider kann ich keinen Screenshot zeigen, da meine Testzeit
abgelaufen ist.
Jedenfalls stand im Modulname ManInWeb. Nach einigem Googlen fand ich zwar heraus, dass du ManInWeb bist, aber nicht, wie man VBA-Projekte narrensicher schützen kann.“
Hallo,
Ja, ich hatte eine Zeit lang meine VBA-Projekte geschützt, wo dann erscheint „Das Projekt kann nicht angezeigt werden.“ Das ist eigentlich ganz einfach: Unviewable+ ist das Stichwort. Findest Du hier:
Kostet allerdings etwas. Ich hatte damals an der Beta und den Übersetzungen mitgewirkt. Mittlerweile kann zwar Unviewable+ auch geknackt werden, der Schutz ist aber immer noch so hoch, dass in geschätzt 99 % der Fälle keiner weiss, wie. Geht auch nur über Zusatztools und nicht den DPB-Trick. Der Vorteil von Unviewable+ ist, dass es keiner externen Tools für die geschützte Datei mehr braucht, also z.B. das Erstellen von Exe-Dateien usw. nicht nötig ist.
Jep, ich bin in Foren und generell mit dem Nicknamen „Maninweb“ bekannt. Fun fact, da gibt’s eine Story dahinter: kennst Du den Film MenInBlack (1)? Damals, hatten meine Frau und ich nach einem Domainnamen gesucht und in Analogie zum Filmtitel dann Maninweb und Womaninweb ersonnen. Tja, dabei ist’s geblieben – wenn auch nun nur als Nicknames.
Excel & VBA ist so seit Mitte der Neunziger mein Ding. Vorher habe ich in Pascal, C, C++ inkl. OOP DOS und Windows-Apps geschrieben. Und dann Excel & VBA entdeckt und bin seitdem dabei geblieben – seit 2005 dann auf freiberuflicher Basis. In der Regel beinhalten meine Excel-Anwendungen VBA, sind meist multilingual verfügbar und haben eine Datenbank- Anbindung im Hintergrund. Ab und zu mache ich auch Schulungen (Excel), meist Inhouse.
Wie lautet denn der Befehl, zu prüfen, ob es in einer Datei
Verknüpfungen gibt? Müsste irgendwas mit .LinkSources sein, aber wissen tu ichs
nicht…
Viele Grüße, Dominic
Hallo Dominic,
ja LinkSource. Ich habe damals überprüft:
Application.EnableEvents = False ‚ — Falls die zu öffnende Datei Autostart-Makros enthält, sollen diese nicht geöffnet werden.
Err.Clear
Application.DisplayAlerts = False‘ — bei Excel 4.0-Dateien erscheint ein Warnhinweis!
Set xlDateiZumÖffnen = Application.Workbooks.Open(objDatei.Path, UpdateLinks:=0, Password:=“Renes Quatschwort“) ‚ — Datei öffnen
Application.DisplayAlerts = True‘ — bei Excel 4.0-Dateien erscheint ein Warnhinweis!
‚ xlDateiZumÖffnen.UpdateLinks = xlUpdateLinksNever ‚ — schalte die Warnmeldungen aus, wenn Fehler kommen, die darauf hinweisen, dass Links nicht vorhanden sind. -> leider nein!
If Err.Number = 0 Then
If Not VBA.IsEmpty(xlDateiZumÖffnen.LinkSources(xlExcelLinks)) Then
For i = 1 To UBound(xlDateiZumÖffnen.LinkSources(xlExcelLinks))
xlZelleZeiger.Offset(0, 6 + i).Value = xlDateiZumÖffnen.LinkSources(xlExcelLinks)(i)
Next
If UBound(xlDateiZumÖffnen.LinkSources(xlExcelLinks)) > 0 Then
xlZelleZeiger.Offset(0, 6).Value = _
UBound(xlDateiZumÖffnen.LinkSources(xlExcelLinks))
If Me.chkKaputt.Value = True Then
For i = 1 To UBound(xlDateiZumÖffnen.LinkSources(xlExcelLinks))
If VBA.Dir(xlDateiZumÖffnen.LinkSources(xlExcelLinks)(i), vbNormal) = „“ Then
xlZelleZeiger.Offset(0, 5).Interior.Color = vbBlack
End If
Next
End If
End If ‚ — trage die Anzahl der gefundenen Links ein und markiere schwarz
End If
[…]
An anderer Stelle überprüfe ich die Links (sie könnten ja „kaputt“ sein:
blnDateiSchutz = False
strLinkKaputt = „“
intAnzahlVerknuepfungen = 0
blnKaputt = False
If Not IsEmpty(xlDateiZumÖffnen.LinkSources(xlExcelLinks)) Then
intAnzahlVerknuepfungen = UBound(xlDateiZumÖffnen.LinkSources(xlExcelLinks))
For i = 1 To intAnzahlVerknuepfungen
strLink = xlDateiZumÖffnen.LinkSources(xlExcelLinks)(i)
If VBA.Dir(strLink, vbNormal) = „“ Then
strLinkKaputt = strLinkKaputt & „###“ & strLink
If strLink Like „“ & Me.txtQuelle.Value & „“ Then
‚ — alles paletti
Else
blnKaputt = True
End If
End If
Next
End If
vielleicht ist das so ein Ding, was du direkt weißt. Ich öffne via Makro eine Datei. In dieser Datei befinden sich Verknüpfungen zu externen Mappen, die zwar kein Mensch braucht, aber die nun mal drin sind, weil die Dateien immer vom Kunden kommen und der damit wer weiß was macht. 😉
Beim öffnen erscheint immer dieser Hinweis und das Makro läuft natürlich nicht weiter:
Kriegt man das irgendwie weg? Bzw. gibt es einen Befehlt der
automatisch „Aktualisieren“ oder „Nicht aktualisieren“ auswählt?
Application.DisplayAlerts = False greift hier nicht.
Danke dir und viele Grüße,
Dominic
####
Hat sich schon erledigt – UpdateLinks:=0 nach dem „Open“-Befehl. Manchmal ist der Makro-Rekorder schon ganz praktisch.
####
Hallo Dominic,
ich muss nur ein bisschen warten – und schon lösen die Leute
alleine ihre Probleme.
Ich hätte es trotzdem gewusst.
Hintergrund: Die IT einer großen Behörde beschließt im Frühjahr 2018 das Laufwerk P von allen Anwendern zu löschen. Ab jetzt soll es nur noch Q geben. Jeder Anwender soll seine Dateien von P nach Q kopieren, dabei anschauen, ob er die Dateien noch braucht …
Nach fünf Monaten haben sie festgestellt: ups – einige
Tausend Dateien haben ja Verknüpfungen auf andere Dateien auf Laufwerk P. Dumm!
Die Verknüpfungen funktionieren nicht mehr.
Ich habe ihnen ein VBA-Tool geschrieben:
* liste alle (Excel-)Dateien auf
* Anwender wählt einen Ordner und legt fest welcher Ordner durch welchen ersetzt werden all. Bspw.: P:\Eigene Dateien\Controlling\Excel\2017 durch Q:\Eigene Dateien\ Controlling\Excel\2017
* öffne alle Excelmappen in diesem Ordner (und Unterordner – kann ausgewählt werden)
* prüfe, ob Verknüpfungen drin sind (in Tabellenblättern,
Namen, Bedingten Formatierungen, Datenprüfungen) und putze
* speichere und schließe
Problem beim Öffnen:
* Verknüpfungen (wie du beschreibst)
* AutoOpen-Makros
* geschützte Dateien oder Blätter (mit oder ohne Kennwort)
uff!
Einige Programmierstunden, einige Nachbesserungen, … am Ende
habe ich nie mehr etwas gehört … wahrscheinlich konnten sie alle (?) Dateien
öffnen und putzen.
ich habe mal wieder ein Excel-Problem, diesmal in
Richtung VBA.
Ich möchte gerne in eine dynamische Tabelle eine bedingte Formatierung einfügen, die eine Rahmenlinie-unten in jeder letzten Zeile eines Monats ausführt, wenn der Wert zutrifft.
soweit auch kein Problem, das würde ja auch mit einer normalen bedingten Formatierung funktionieren. Allerdings ist die Tabelle schon mit einem Rahmengitter belegt, um die Zellen besser zu unterscheiden.
Deswegen hätte ich gerne die Rahmenlinie-unten in
„fett“, damit man die einzelnen Monate besser hervorhebt.
Da man das „fett“ in Excel bei bedingten Formatierungen nicht auswählen kann, habe ich durch Internetrecherche herausgefunden, dass man per VBA-Programmierung dieses Problem lösen kann.
Meine VBA-Kenntnisse sind allerdings gleich null, was
mich nicht weiter bringt J
Anbei eine Beispieltabelle in der nach Belieben rumhantiert werden kann.
Hallo Herr F.,
Stimmt – das ist mir noch gar nciht aufgefallen: in der Bedingten Formatierung fehlt die Rahmenart. Doof!
Den Code hätten Sie doch sicherlich selbst hinbekommen.
Das Makro hängt an
der Datei.
Sie können es in
der Datei lassen oder in Ihre persönliche Makrodatei kopieren.
Sie können es über
Entwicklertools / Makros (alternativ: Ansicht / Makros) starten – das Makro
heißt „LinienEinfügen“
Oder Sie fügen ein
Symbol in die Symbolleiste für den Schnellzugriff ein und starten es darüber.
Nicht ganz elegant, aber leicht zu ändern – in den ersten drei Zeilen lege ich die Spalte fest:
Sub LinienEinfügen()
Const BEGINN As String = "A"
Const ENDE As String = "I"
Const SUCHSPALTE As String = "B"
Dim i As Long
Dim lngLetzteZeile As Long
lngLetzteZeile = _
ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row
' -- ermittle die Nummer der letzten Zeile
For i = 1 To lngLetzteZeile
If ActiveSheet.Range(SUCHSPALTE & i).Value <> "" And _
ActiveSheet.Range(SUCHSPALTE & (i + 1)).Value <> "" Then
If IsDate(ActiveSheet.Range(SUCHSPALTE & i).Value) And _
IsDate(ActiveSheet.Range(SUCHSPALTE & (i + 1)).Value) Then
If Month(ActiveSheet.Range(SUCHSPALTE & i).Value) <> _
Month(ActiveSheet.Range(SUCHSPALTE & (i + 1)).Value) Then
With ActiveSheet.Range(BEGINN & i & ":" & _
ENDE & i).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
End If
End If
End If
Next i
End Sub
Guten Morgen
Herr Martin,
vielen
herzlichen Dank für die Codes.
Damit komme ich
zurecht.
Allerdings kann
ich leider noch nicht sagen, dass ich das hinbekommen hätte.
Trotzdem vielen
Dank nochmal und schöne heiße Tage!
Interessant finde ich, dass man erst googlen muss, um herauszufinden, dass die Inversmatrix in Excel als Funktion MINV heißt und bei VBA als Worksheetfunction MINVERSE (sogar mit E am Schluss, da kommst echt net drauf von allein)…
Hallo Axel,
Zwei Tipps, damit die nicht googln musst, was MINV heißt:
Der Translator von Mourad Louha (excel-translator.de) Oder du verwendest die Formel, schaltest den Makrorekorder ein, editierst die Formel (bei mir: [F2]) und Makrorekorder STOPP. Er „übersetzt“ dann die Funktionen ins Englische.
Übrigens: Schöne Skizze:- da steckt viel Hirnschmalz drin …
du bist ein Ass, danke, nun weiß ich wie man die
einzelnen Elemente anspricht, nach Zeile und Spalte und habe meinen Code
angepasst.
aber: in meinem Fall markiere ich ja vor Eingabe der UDF wie immer bei Matrixfunktionen 3 Zellen untereinander und möchte, dass 3 Ergebnisse aus einem neuen Array, das mit den gewonnenen Variablen arbeitet erscheint
Hallo Axel,
Ich habe die Array verkleinert … das Überwachungsfenster hat mir verraten, dass Matrix1(0, x) und Matrix2(0,x) nicht belegt sind.
Public Function Test1(S1 As Variant, R1 As Variant, S2 As Variant, R2 As Variant, R3 As Variant)
' Funktionsbeschreibung: Berechnet den Schnittpunkt einer Geraden mit einer Ebene,
' Inputselektion: 5 zusammenhŠngende Zellbereiche (S1,R1,S2,R2,R3)
' Variablen deklarieren
Dim S1x As Double
Dim S1y As Double
Dim S1z As Double
Dim R1x As Double
Dim R1y As Double
Dim R1z As Double
Dim S2x As Double
Dim S2y As Double
Dim S2z As Double
Dim R2x As Double
Dim R2y As Double
Dim R2z As Double
Dim R3x As Double
Dim R3y As Double
Dim R3z As Double
Dim x1 As Double
Dim x2 As Double
Dim x3 As Double
Dim Loesung()
Dim r As Double
Dim ReturnArray(3)
Dim DoTranspose As Boolean
Dim Matrix1(2, 2) As Double ' (kann man das so deklarieren (Anzahl Zeilen/Spalten der Matrix)?
Dim Matrix2(2, 0) As Double
' Bestimmen, ob Inputbereich horizontal oder vertikal ist
If Application.Caller.Rows.Count > 1 Then
DoTranspose = True
Else
DoTranspose = False
End If
' Werte aus Inputselektion (Vektorkoordinaten und neue LŠnge) holen
S1x = S1.Cells(1).Value
S1y = S1.Cells(2).Value
S1z = S1.Cells(3).Value
R1x = R1.Cells(1).Value
R1y = R1.Cells(2).Value
R1z = R1.Cells(3).Value
S2x = S2.Cells(1).Value
S2y = S2.Cells(2).Value
S2z = S2.Cells(3).Value
R2x = R2.Cells(1).Value
R2y = R2.Cells(2).Value
R2z = R2.Cells(3).Value
R3x = R3.Cells(1).Value
R3y = R3.Cells(2).Value
R3z = R3.Cells(3).Value
'Matrizes bestimmen
'3x3 Matrix
Matrix1(0, 0) = R1.Cells(1).Value
Matrix1(1, 0) = R1.Cells(2).Value
Matrix1(2, 0) = R1.Cells(3).Value
Matrix1(0, 1) = R2.Cells(1).Value * (-1)
Matrix1(1, 1) = R2.Cells(2).Value * (-1)
Matrix1(2, 1) = R2.Cells(3).Value * (-1)
Matrix1(0, 2) = R3.Cells(1).Value * (-1)
Matrix1(1, 2) = R3.Cells(2).Value * (-1)
Matrix1(2, 2) = R3.Cells(3).Value * (-1)
'1x1 Matrix
Matrix2(0, 0) = S2.Cells(1).Value - S1.Cells(1).Value
Matrix2(1, 0) = S2.Cells(2).Value - S1.Cells(2).Value
Matrix2(2, 0) = S2.Cells(3).Value - S1.Cells(3).Value
' Berechnungen
' r berechnen
Loesung = Application.WorksheetFunction.MMult(Application.WorksheetFunction.MInverse(Matrix1), Matrix2)
r = Loesung(1, 1)
MsgBox r
' Ergebnis berechnen (Koordinaten des Schnittpunkts nder Geraden mit der Ebene)
ReturnArray(0) = S1x + r * R1x
ReturnArray(1) = S1y + r * R1y
ReturnArray(2) = S1y + r * R1y
' Output transponieren horizontal zu vertikal oder umgekehrt, falls n_tig
If DoTranspose Then
Test1 = Application.WorksheetFunction.Transpose(ReturnArray)
Else
Test1 = ReturnArray
End If
' Ergebnis erscheint in den 3 Output-Zellen
End Function
was habe ich gestern gemacht? Wir haben ja
herausgefunden, dass in der „Lösungs“-Zeile der Fehler steckt.
Ich habe das Datenfeld Matrix markiert und das Überwachungsfenster eingeschaltet. Dort habe ich festgestellt, dass der Wert an der Position 0 nicht belegt ist:
ich hoffe, es geht dir gut. Darf ich dir eine kurze Frage VBA stellen? Folgende Situation:
Ich habe eine geodätische Kuppel gebaut und möchte ein Gleichungssystem mit 3 Gleichungen und 3 Unbekannten zu lösen:
Function GleichungsSystemMatrix3x3und1x3Parameter(Matrix1 As Range, Matrix2 As Range) As Variant GleichungsSystemMatrix3x3und1x3Parameter = Application.WorksheetFunction.MMult(Application.WorksheetFunction.MInverse(Matrix1), Matrix2) End Function
Und jetzt kommts: Da kommen also 3 Parameter r, t und w raus (so will ich sie später nennen) und die hätte ich gerne in Variablen geschrieben und eben nicht gleich in die Excelzellen, wie das der obige Code halt macht.
Hättest du eine Idee und Lust zu helfen?
LG, Axel
moin Axel,
du musst die Arrays richtig zusammenbauen, dann klappt es. Das Überwachungsfenster hat mir geholfen.
Sub LösungBerechnen() Dim Matrix(2, 2) As Double Dim Lö1(2, 2) As Double Dim R1 As Double Dim R2 As Double Dim R3 As Double Dim T1 As Double Dim T2 As Double Dim T3 As Double Dim W1 As Double Dim W2 As Double Dim W3 As Double Dim L1 As Double Dim L2 As Double Dim L3 As Double R1 = 5 R2 = 3 R3 = 2 T1 = -1 T2 = 2 T3 = 2 W1 = 7 W2 = 5 W3 = 8
Function GleichungsSystemMatrix3x3und1x3Parameter_L1(Matrix1 As Variant, Matrix2 As Variant) As Double Dim Lösung As Variant Lösung = Application.WorksheetFunction.mmult(Application.WorksheetFunction.MInverse(Matrix1), Matrix2) GleichungsSystemMatrix3x3und1x3Parameter_L1 = Lösung(1, 1) End Function
Function GleichungsSystemMatrix3x3und1x3Parameter_L2(Matrix1 As Variant, Matrix2 As Variant) As Double Dim Lösung As Variant Lösung = Application.WorksheetFunction.mmult(Application.WorksheetFunction.MInverse(Matrix1), Matrix2) GleichungsSystemMatrix3x3und1x3Parameter_L2 = Lösung(2, 1) End Function
Function GleichungsSystemMatrix3x3und1x3Parameter_L3(Matrix1 As Variant, Matrix2 As Variant) As Double Dim Lösung As Variant Lösung = Application.WorksheetFunction.mmult(Application.WorksheetFunction.MInverse(Matrix1), Matrix2) GleichungsSystemMatrix3x3und1x3Parameter_L3 = Lösung(3, 1) End Function
Und stutze, weil das Objekt „Picture“ nicht in der Liste der Objektvariablen angeboten wird. Ich versuche es trotzdem:
Das Makro
Sub BildEinfuegen()
Dim xlBlatt As Worksheet
Dim xlBild As Picture
Set xlBlatt = ActiveSheet
Set xlBild = xlBlatt.Pictures.Insert("F:\Eigene Bilder\Erdbeertörtchen.JPG")
End Sub
läuft hervorragend. Okay – und was heißt: Bilder verschieben und verkleinern? Der Makrorekorder liefert:
Merkwürdig – ich hänge den veränderten Code an mein Makro:
Dim xlBlatt As Worksheet
Dim xlBild As Picture
Set xlBlatt = ActiveSheet
Set xlBild = xlBlatt.Pictures.Insert("F:\Eigene Bilder\Erdbeertörtchen.JPG")
With xlBild
.Width = .Width * 0.3169191919
.Height = .Height * 0.3169191919
.Left = 252
.Top = 54.75
.Visible = True
End With
HIER wird das Picture-Objekt erkannt. Und: läuft doch!
Merkwürdig. Ich erstelle in Outlook ein Makro, das eine Mail mit Anhang versendet:
Private Sub MailVersenden()
Dim olApp As Application
Dim olMail As MailItem
Set olApp = Application
Set olMail = olApp.CreateItem(olMailItem) ' olMailItem = 0
With olMail
.To = "rene.martin@compurem.de"
.Subject = "Beratungscheckliste Privatkunden"
.Attachments.Add Source:="D:\Eigene Dateien\Excel\Kundeninformationen.xlsx"
.Body = "Diese Mail wurde automatisch erstellt."
.Send
End With
MsgBox "Das Dokument wurde erfolgreich per Mail gesendet.", vbInformation
End Sub
Klappt! Ich versuche es in Excel. Kopiere den Code, ändere ihn ein wenig, weil ich dort mit late binding arbeite:
Private Sub MailVersenden()
Dim olApp As Application
Dim olMail As MailItem
Const olMailItem As Integer = 0
Set olApp = Application
Set olMail = olApp.CreateItem(olMailItem) ' olMailItem = 0
With olMail
.To = "rene.martin@compurem.de"
.Subject = "Beratungscheckliste Privatkunden"
.Attachments.Add Source:="D:\Eigene Dateien\Excel\Kundeninformationen.xlsx"
.Body = "Diese Mail wurde automatisch erstellt."
.Send
End With
MsgBox "Das Dokument wurde erfolgreich per Mail gesendet.", vbInformation
End Sub
Das Ergebnis:
Erst wenn ich den Namen des Parameters „Source“ lösche, funktioniert es:
Ich vermute, dass „Object“ nicht korrekt in MailItem konvertiert werden kann.
ich wollte gerade Inhalte zwischen zwei Dokumenten kopieren mittels Kopieren / Einfügen von Zellinhalten.
Dies funktioniert jedoch leider nicht. Ich nutze die aktuelle Datei.
Ist dies unterbunden?
Kopieren – und ….… nichts geht mehr“
Hallo Herr H.,
geht doch!? Ich weiß gar nicht was Sie wollen?
Schauen Sie selbst …
*lach*
Und hier die Auflösung:
Erinnern Sie sich an die „unschöne“ Formel, die in der
Bearbeitungsleiste angezeigt wird? Ich habe die Bearbeitungsleiste
ausgeschaltet.
Wenn man nun in eine andere Mappe wechselt wäre es doof, wenn DORT auch die Bearbeitungsleiste ausgeblendet ist. Also schalte ich beim Wechseln in eine andere Datei diese Leiste zur Sicherheit ein:
Private Sub Workbook_Activate() Application.DisplayFormulaBar = False ' -- Bearbeitungleiste End Sub
Private Sub Workbook_Deactivate() Application.DisplayFormulaBar = True ' -- Bearbeitungleiste End Sub
Sie wissen, dass man in Excel nach dem Kopieren sofort
einfügen muss. Wenn man etwas anderes dazwischen macht, löscht Excel den
Zwischenspeicher.
Eben – und HIER machen wir etwas anderes dazwischen. Ich sollte die Datei ändern … Mache ich gleich.
ich hoffe, Sue haben / hatten ein erholsames Wochenende.
Ich habe an meiner Prozesslandkarte weitergebastelt. Hierzu möchte ich den Abonnenten der Prozesslandkarte einen vereinfachten Interviewfragebogen zur Verfügung stellen. Diesen möchte ich gerne mit meinem Namen und / oder Logo versehen. Dieses soll für Nicht-Excel-Profis nicht löschbar sein. Also: Ich möchte ein Copyright Vermerk der nicht einfach herausgelöscht werden kann.
Hallo Herr H.,
Tippen Sie in eine
beliebigen Zelle
=copyright
Achtung: OHNE KLAMMER!
Dann erscheint Ihr Name. DAS findet keiner wohin ich das versteckt habe.
Möchten Sie so
etwas?
Und: was bekomme ich, wenn ich Ihnen verrate, wie ich das in diese Datei reinbekommen habe (und Sie es wieder rausbekommen)?
Hallo Herr Martin,
das ist ja cool!
Ich weiß schon,
warum ich das mit Ihnen mache!
Aber jetzt mal raus mit der Sprache …. Wie geht das?
Hallo Herr
Hämmerle,
kennen Sie Namen in
Excel? Über den Namensmanager in der Registerkarte Formeln oder über das
Namensfeld neben der Bearbeitungsleiste kann man einen Namen sehen und
erstellen.
Über ein kleines
Makro kann man einen unsichtbaren Namen definieren:
Allerdings sollte die Tabelle auf eine Seite angepasst werden:
With ThisWorkbook.Worksheets(1).PageSetup
.Zoom = False
.FitToPagesWide = 1
End With
Und was jetzt passiert entzieht sich meiner Kenntnis:
Ich schaffe es nicht die Seitenbreite = 1 einzustellen UND Seitenumbrüche einzufügen. Alle Versuche die Reihenfolge der Zeilen zu ändern, Application.PrintCommunication ein- oder auszuschalten, die Eigenschaft PageBreak auf xlPageBreakManual festzulegen … Alles scheitert. Also habe ich nachgesehen, wie „breit“ eine Seite ist. Hier: 60%. Und mit dem Befehl
ThisWorkbook.Worksheets(1).PageSetup.Zoom = 60
klappt es auch, wie man sehen kann:
Hier der ganze Code:
Sub SeiteEinrichten()
ThisWorkbook.Worksheets(1).ResetAllPageBreaks
ThisWorkbook.Worksheets(1).PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$4"
.PrintTitleColumns = ""
End With
With ThisWorkbook.Worksheets(1).PageSetup
.Zoom = False
.FitToPagesWide = 1
End With
ThisWorkbook.Worksheets(1).HPageBreaks.Add Before:=ThisWorkbook.Worksheets(1).Cells(5, 1)
ThisWorkbook.Worksheets(1).HPageBreaks.Add Before:=ThisWorkbook.Worksheets(1).Cells(30, 1)
ThisWorkbook.Worksheets(1).HPageBreaks.Add Before:=ThisWorkbook.Worksheets(1).Cells(51, 1)
ThisWorkbook.Worksheets(1).PageSetup.Zoom = 60
ThisWorkbook.Worksheets(1).PageSetup.CenterHorizontally = False
Application.PrintCommunication = True
ThisWorkbook.Worksheets(1).PageSetup.LeftFooter = "Lebensmittel"
' -- Firmenname in der Fußzeile
End Sub
deaktiviert das Kontextmenü der Registerkarte. DAS funktioniert dann nicht mehr:
Da der Befehl zu Application, also zu Excel gehört, bleibt er auch für andere Dateien, die geöffnet werden, aktiv. So lange, bis man Excel schließt und wieder erneut öffnet. DANN steht das Kontextmenü wieder zur Verfügung.
Seltsam. Wenn ich VBA programmiere, verwende ich NIE die Befehle Activate oder Select. Mit zwei Ausnahmen: ich programmiere Spunganweisungen: „wechsle für den Anwender auf ein bestimmtes Blatt oder auf eine bestimmte Zelle“. Oder: am Ende des Programms soll der Cursor auf einem bestimmten Blatt und/oder auf einer bestimmten Zelle sitzen. Ich setze Verweise auf Zellen:
Sub Kopieren_und_Fertig()
Dim xlZelle As Range
Dim i As IntegerSet
Set xlZelle = ActiveSheet.Range("C2")
For i = 1 To 50
xlZelle.Copy Destination:=xlZelle.Offset(i, 0)
Next
MsgBox "fertig"
Ich starte das Makro von Excel aus:
Was passiert? man siehst nichts:
Auch wenn der Verweis auf ein anderes Tabellenblatt gesetzt wird:
Set xlZelle = ThisWorkbook.Worksheets(2).Range(„C2“)
Für den Befehl Inhalte einfügen sind zwei Zeilen Code nötig:
With xlZelle.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="m,w,d"
End With
For i = 1 To 50
xlZelle.Copy
xlZelle.Offset(i, 0).PasteSpecial Paste:=xlPasteValidation
Application.CutCopyMode = False
Next i
MsgBox "fertig"
Was geschieht hier:
Der Cursor wandert über den Bildschirm.
Ein Zucken ist auch am Bildschirm zu sehen, wenn die Inhalte auf einem anderen Tabellenblatt eingefügt werden:
Set xlZelle = ThisWorkbook.Worksheets(2).Range(„C2“)
Was tun? Klar: Die Bildschirmaktualisierung ausschalten. Dann funktioniert es! nichts zuckt; nichts zeigt sich …
Na, ein bisschen mehr Mühe hätte sich VBA schon geben können:
Anwendungs- oder objektorientierter Fehler. Na toll! Und wo? Und was?
Der Debugger hilft: die Berechnung Row – 8 ergibt einen falschen, nämlich negativen Wert (falsch gerechnet; nicht aufgepasst!) – so kann die Zelle „C-1“ nicht ermittelt werden.
Auch hier: Typen unverträglich!
Ey, VBA: sag mir doch deutlich, dass ich Dumpfbacke bei der Funktion MATCH (VERGLEICH) die beiden Parameter vertauscht habe. Während die Funktion der Zeile darüber (COUNTIF, ZÄHLENWENN) die Parameter ich suche wo wen verlangt, ermittelt MATCH / VERGLEICH: ich suche wen wo. Und keiner hilft mir! *lach*
Wenn ich in VBA eine Zelle oder einen Zellbereich kopieren oder ausschneiden möchte, verwende ich die Methoden Copy, beziehungsweise Cut mit dem optionalen Parameter Destination, also beispielsweise:
beispielsweise um die Datenüberprüfung zu kopieren. Danach läuft um die kopierte Zelle eine „Ameisenlinie“:
Das würde nicht weiter stören – sie verschwindet bei den weiteren Befehlen. Aus ästhetischen Gründen und zur Sicherheit (es kann zu Problemen führen, wenn der Kopiermodus noch aktiv ist), schalte ich den Laufrahmen aus. Hierbei hilft
Application.CutCopyMode = False
Das Erstaunliche:
IntelliSense zeigt die Parameter False und True nicht an. Auf der Seite
Die Methode ‚Visible‘ für das Objekt ‚_Worksheet‘ ist fehlgeschlagen.
Ich wollte per VBA ein Blatt ausblenden. Damals war die Ursache, dass dieses auszublendende Blatt das einzige war – DAS geht nicht. Eine Exceldatei benötigt immer mindestens ein sichtbares Tabellenblatt.
Nun erhalte ich die Fehlermeldung erneut.
Die Ursache diesmal: Die Arbeitsmappe ist geschützt (Überprüfen / Arbeitsmappe schützen).
Ich erstelle ein großes, komplexes Formular (UserForm) in VBA, starte es zum Testen und:
Unerwartetes Dateiende.
Etwas differenziertes dürfte es schon sein. Vor allem dürfte der Debugger starten, der mir die Zeile kennzeichnet, in der der Fehler erzeugt wurde.
PS: Der Fehler kam dadurch zustande, dass ich ein Objekt adressiert hatte, das nicht in einer Sammlung vorhanden war. Ich habe den Fehler im Einzelschrittmodus gefunden.
Anruf eines verzweifelten Anwenders. Die Bank, für die er arbeitet, stellt um auf Office 365. Ich glaube von Office 10. Einige Makros in Word funktionieren nicht mehr. Er zeigt es mir online. Altes System: Läuft. Neues System: Nichts läuft.
Ich schaue es mir an und finde sehr schnell:
Ein Modul AutoNew mit einer Main-Prozedur. Mann – das ist sowas von 1997! Ist mir noch nicht aufgefallen – dass diese uralt Automakros nicht mehr funktionieren. Nein – da verwenden wir doch die Ereignisse „Document_Open()“ und „Document_New()“. Damit klappt es dann auch. Der Anwender war glücklich!
Ich erstelle einen Dialog und erstelle eine Schaltfläche, die es ermöglicht, die Werte zu sortieren:
Ich verwende eine Code, den ich im Internet gefunden habe:
Private Sub cmdSort_Click()
Dim i As Integer
Dim listMatrix As Object, varDaten As Variant
Set listMatrix = CreateObject("System.Collections.ArrayList")
For i = 0 To Me.lstAuswahl.ListCount - 1
listMatrix.Add Me.lstAuswahl.List(i)
Next i
listMatrix.Sort
Me.lstAuswahl.Clear ' -- Auswahlliste leeren
For Each varDaten In listMatrix
Me.lstAuswahl.AddItem varDaten
Next varDaten
If Me.lstAuswahl.ListCount > 0 Then
Me.lstAuswahl.ListIndex = 0
End If
Set listMatrix = Nothing
End Sub
Klappt wunderbar – allerdings:
ArrayList ist ein Objekt von .NET-Framework 3.5. Ist dieses nicht auf einem Rechner installiert, funktionieren die Befehle der Klasse auch nicht:
Also – raus damit – und die „klassische“ Variante:
Private Sub cmdSort_Click()
Dim intLast As Integer, intNext As Integer
Dim strTemp As String
With Me.lstAuswahl
For intLast = 0 To .ListCount – 1
For intNext = intLast + 1 To .ListCount – 1
If .List(intLast) > .List(intNext) Then
strTemp = .List(intLast)
.List(intLast) = .List(intNext)
.List(intNext) = strTemp
End If
Next intNext
Next intLast
End With
End Sub
Das ist bösartig! In einem Exceldokument sollen Werte von Eigenschaftsfeldern, die von SAP kommen, in die Kopfzeile geschrieben werden. Also von:
Nach:
Der Befehl für diese Felder ist schnell gefunden:
ContentTypeProperties(„Target group“)
Das Makro:
Dim strTitle As String
Dim strLocation As String
Dim strTarget As String
Dim strType As String
strTitle = ThisWorkbook.BuiltinDocumentProperties("Title").Value
strLocation = ThisWorkbook.ContentTypeProperties("Location").Value
strTarget = ThisWorkbook.ContentTypeProperties("Target group").Value
strType = ThisWorkbook.ContentTypeProperties("Document type").Value
If strTitle <> "" Then strTitle = " " & strTitle
If strLocation <> "" Then strLocation = " " & strLocation
If strTarget <> "" Then strTarget = " " & strTarget
If strType <> "" Then strType = " " & strType
With ActiveSheet.PageSetup
.LeftHeader = Replace(.LeftHeader, "Title", "Title" & strTitle)
.LeftHeader = Replace(.LeftHeader, "Location", "Location" & strLocation)
.LeftHeader = Replace(.LeftHeader, "Target group", "Target group" & strTarget)
.LeftHeader = Replace(.LeftHeader, "Document type", "Document type" & strType)
End With
Ein Durchlauf mit leeren Feldern – klappt! Ein Durchlauf mit Daten bringt die Fehlermeldung 13: Typen unverträglich. Ich stutze. Ich untersuche die Inhalte. tatsächlich: die Daten, die aus Sharepoint kommen, sind keine Texte, sondern Datenfelder. Bestehend aus zwei Werten: Inhalt und ID. Sieht man aber nicht:
Nun das kann man abprüfen:
Dim strTitle As String
Dim strLocation As String
Dim strTarget As String
Dim strType As String
If TypeName(ThisWorkbook.BuiltinDocumentProperties("Title").Value) = "String()" Then
If UBound(ThisWorkbook.BuiltinDocumentProperties("Title").Value) >= 0 Then
strTitle = ThisWorkbook.BuiltinDocumentProperties("Title").Value(0)
End If
ElseIf TypeName(ThisWorkbook.BuiltinDocumentProperties("Title").Value) = "String" Then
strTitle = ThisWorkbook.BuiltinDocumentProperties("Title").Value
End If
If TypeName(ThisWorkbook.ContentTypeProperties("Location").Value) = "String()" Then
If UBound(ThisWorkbook.ContentTypeProperties("Location").Value) >= 0 Then
strLocation = ThisWorkbook.ContentTypeProperties("Location").Value(0)
End If
ElseIf TypeName(ThisWorkbook.ContentTypeProperties("Location").Value) = "String" Then
strLocation = ThisWorkbook.ContentTypeProperties("Location").Value
End If
If TypeName(ThisWorkbook.ContentTypeProperties("Target group").Value) = "String()" Then
If UBound(ThisWorkbook.ContentTypeProperties("Target group").Value) >= 0 Then
strTarget = ThisWorkbook.ContentTypeProperties("Target group").Value(0)
End If
ElseIf TypeName(ThisWorkbook.ContentTypeProperties("Target group").Value) = "String" Then
strTarget = ThisWorkbook.ContentTypeProperties("Target group").Value
End If
If TypeName(ThisWorkbook.ContentTypeProperties("Document type").Value) = "String()" Then
If UBound(ThisWorkbook.ContentTypeProperties("Document type").Value) >= 0 Then
strType = ThisWorkbook.ContentTypeProperties("Document type").Value(0)
End If
ElseIf TypeName(ThisWorkbook.ContentTypeProperties("Document type").Value) = "String" Then
strType = ThisWorkbook.ContentTypeProperties("Document type").Value
End If
If strTitle <> "" Then strTitle = " " & strTitle
If strLocation <> "" Then strLocation = " " & strLocation
If strTarget <> "" Then strTarget = " " & strTarget
If strType <> "" Then strType = " " & strType
With ActiveSheet.PageSetup
' -- schreibe nur rein, falls der Text noch nicht drinsteht.
If InStr(1, .LeftHeader, "Title" & strTitle) = 0 Then
.LeftHeader = Replace(.LeftHeader, "Title", "Title" & strTitle)
End If
If InStr(1, .LeftHeader, "Location" & strLocation) = 0 Then
.LeftHeader = Replace(.LeftHeader, "Location", "Location" & strLocation)
End If
If InStr(1, .LeftHeader, "Target group" & strTarget) = 0 Then
.LeftHeader = Replace(.LeftHeader, "Target group", "Target group" & strTarget)
End If
If InStr(1, .LeftHeader, "Document type" & strType) = 0 Then
.LeftHeader = Replace(.LeftHeader, "Document type", "Document type" & strType)
End If
End With
Ich weiß nicht genau warum. Eigentlich ist VBA VBA. Aber manchmal passiert es, dass ich ein Programm schreibe, es einem Kunden weitergebe und sämtliche Funktionen der VBA-Bibliothek nicht erkannt werden, also: Left, Right, Len, InStr, …
Die Lösung: ich setze den Bibliotheksnamen VBA davor, also: VBA.Left, VBA.Right, VBA.Len, VBA.InStr, … Dann läuft es.
Mein VBA-Programm liefert eine „merkwürdige“ Fehlermeldung. Warum kann ein Tabellenblatt nicht ausgeblendet werden?
Die Ursache ist schnell gefunden – es gibt nur ein Tabellenblatt – und dieses ein darf ich natürlich nicht ausblenden … Also schnell überprüft, wie viele Blätter sichtbar (xlSheetVisible) sind.
Wir haben in
unserem Dokumentenlenkungssystem eine Vorlage für Excel mit spezifischen
Eigenschaften (siehe Bild mit den teilweise kundenspezifischen Eigenschaften).
Damit in Excel
beim ausdrucken die wesentlichen Informationen in Kopf und Fußzeile erscheinen,
müssen wir das alles sehr aufwändig händisch in allen Tabellenblättern pflegen.
Was natürlich sehr fehlerträchtig ist…
Ich habe mit
meinen sehr bescheidenen Kenntnissen mal versucht, irgendwie mit VBA da an die
Info ranzukommen, so:
Sub Test()
rw = 1
Worksheets(1).Activate
For Each p In ActiveWorkbook.CustomDocumentProperties
Cells(rw, 1).Value = p.Name
Cells(rw, 2).Value = p.Value
rw = rw + 1
Next
End Sub
Im Ergebnis allerdings ohne Erfolg, da kaum Werte so rauslesbar sind, vor allem nicht die spezifischen. Das wäre auch nur die halbe Miete gewesen, da ja das Einbringen der Info in die Kopf- und Fußzeile nochmal ein separates Problem darstellt, für die ich aktuell keine Idee zur Lösung habe… Kennen Sie vielleicht ein paar VBA-Kniffe, wie ich hier vielleicht weiterkomme?
Hallo Herr F.,
und so geht es:
mit
Dim i As Integer
On Error Resume Next
For i = 1 To ThisWorkbook.ContentTypeProperties.Count
Die Ursache: zwei Makros hatten den gleichen Namen. Könnte VBA das nicht etwas deutlicher sagen? „Systemfehler“ und „Unbekannter Fehler“ sind nicht sehr vielsagend …
Auch dieses Problem hat mich eine Stunde Zeit gekostet.
Ich erstelle ein umfangreiches Programm für einen Kunden. Die Registerkarten sind ausgeblendet:
Ich starte das Programm mit dem Ausschalten der Bildschirmaktualisierung:
Application.ScreenUpdating = False
Ich blende die Registerkarten per VBA wieder ein:
ActiveWindow.DisplayWorkbookTabs = True
Was passiert? Nichts!
Man muss vor dem Anzeigen die Bildschirmaktualisierung wieder einschalten! ( Application.ScreenUpdating = True). Dann erst werden die Tabs wieder angezeigt:
Übrigens: Es ist erstaunlich, dass die Eigenschaft „DisplayWorkbookTabs“ eine Eigenschaft von ActiveWindow und nicht von ActiveWorkbook ist!
Boah – ist das mies! Zwei Stunden lang habe ich gesucht. Und dann gefunden.
Ich habe eine Datei mit zwei Tabellenblättern. Eines enthält eine Datenliste, ein zweites eine Datenüberprüfung mit einer Liste, die diese Daten aus dem anderen Blatt holt:
Per VBA ziehe ich nun diese beiden Blätter (einzeln!) in eine Masterdatei (man kann es auch per Hand machen. Die Verknüpfung verweist nun auf die alte Datei:
Der Code:
Dim xlFremdeDatei As Workbook
Dim xlEigeneDatei As Workbook
Dim xlFremdesBlattDaten As Worksheet
Dim xlFremdesBlattDatenüberprüfung As Worksheet
Set xlEigeneDatei = ThisWorkbook
Set xlFremdeDatei = Application.Workbooks.Open("D:\Eigene Dateien\Excel\Beispieltabellen\3Musketiere.xlsx")
xlFremdeDatei.Worksheets(2).Copy Before:=xlEigeneDatei.Worksheets(1)
Set xlFremdesBlattDatenüberprüfung = xlEigeneDatei.Worksheets(1)
xlFremdeDatei.Worksheets(1).Copy Before:=xlEigeneDatei.Worksheets(1)
Set xlFremdesBlattDaten = xlEigeneDatei.Worksheets(1)
xlFremdeDatei.Close SaveChanges:=False
xlEigeneDatei.Save
Beide Dateien werden geschlossen, die Masterdatei wird geöffnet. Unter Datei / Informationen wird angezeigt, dass sich in dieser Datei eine Verknüpfung (auf eine andere Datei) befindet, die man hier nicht löschen kann. Klar!
Nun setze ich in der Zelle mit der Datenüberprüfung per Hand oder per VBA die Verknüpfung auf die eigene Datei:
Excel zeigt noch immer (unter Datei / Informationen) an, dass sich in der Datei eine Verknüpfung befindet. Diesen Eintrag kann ich nicht löschen! Erst durch das Schließen und wieder Öffnen der Datei ist er verschwunden.
Das Erstaunliche: werden die Tabellenblätter gelöscht, wird die Verknüpfung nicht angezeigt. Werden die Verknüpfungen „nur“ behoben, bleibt der Eintrag noch in den Informationen stehen.
Zwei Stunden habe ich benötigt, um das herauszufinden. Mies!
Habt ihr schon einmal Ribbon selbst mit einer XML-Datei erstellt. Ein validierender XML-Editor ist wichtig (wer Visual Studio hat, ist gut beraten).
Man fragt sich, welcher Praktikant den Befehl checkBox und nicht Checkbox (auch nicht CheckBox) genannt hat. Buttons habe das Attribut size mit den Werten „large“ und „normal“. Hum!
Ich erstelle ein Exceltabellenblatt, bei dem der Anwender zwischen zwei Texten wechseln kann. Je nach Text wird eine andere Liste für die Datenüberprüfung verwendet. Da die Liste dynamisch ist und aus einem anderen System kommt, muss ich ein paar Zeilen VBA-Code verwenden:
If Target.Value = „Für Standorte“ Or Target.Value = „Für Gebäude“ Then […]
Nach einer Weile meldet sich der Anwender und sagt, dass er auf diesem Tabellenblatt eine Fehlermeldung erhält. Ich schaue nach:
Das Gemeine: Der Anwender hat einen Doppelklick auf eine verbundene Zelle ausgeführt. Dadurch greift das Target-Objekt nicht – es nicht nun nicht mehr EINE Zelle, die einen Inhalt hat, sondern ein Zellbereich. Ich erhalte einen Fehler!
Also noch schnell eine Zeile Code außenrum – in der ersten Spalte wurde nichts verbunden. If Target.Column = 1 Then Und schon klappt es!
Verwende ich für Vlookup die Schreibweise Application.WorksheetFunction.VLookup,
dann bleibt die Schleife beim ersten Wert hängen, den er nicht findet und gibt
den Laufzeitfehler 1004 aus (Die Vlookup-Eigenschaft des
WorksheetFunction-Objektes kann nicht zugeordnet werden). Der Versuch, mit „If
not isError“ den Fehler abzufangen, schlägt fehl.
Verwende ich jedoch die Schreibweise Application.VLookup,
dann funktioniert alles perfekt und in „Sheets(„Tabelle1“).Cells(i,
2).Value“ wird „Fehler“ hineingeschrieben.
Ein identisches Verhalten zeigen auch andere Funktionen, wie
Application.WorksheetFunction.Match.
Verstehen Sie das?
Danke Ihnen und viele Grüße,
Hallo Herr D.
Der Code sieht korrekt aus. Ich kann dazu nur Folgendes
sagen:
Letzte Woche habe ich ein VBA-Add-In für einen Kunden
erweitert – ich wollte Daten per Formeln aufbereiten, um darauf ein Diagramm
aufzusetzen.
Nun möchte ich noch die Zeichnungsfläche verschieben, damit man die Beschriftung der Y-Achse besser sehen kann. Obwohl sie einen Abstand von Links = 7 hat, darf ich diesen Wert nicht auf 100 setzen?!?
Und schon wieder bin ich reingefallen. Ich möchte in einem Excel-Formular per VBA eine Datenübrprüfung einfügen. Referenzspalte ist Spalte A. Steht dort kein Wert wird eine Datenüberprüfung generiert:
For j = 11 To ThisWorkbook.Worksheets(i).Range(„A1“).SpecialCells(xlCellTypeLastCell).Row If ThisWorkbook.Worksheets(i).Range(„A“ & j).Value = „“ Then ‚ — Datenüberprüfung End If Next
Und natürlich erhalte ich einen Fehler: G36 ist „leer“ (weil verbunden mit G35), aber in G36 kann man keine Datenüberprüfung einschalten ( weil verbunden mit G35). Die Lösung: RAUS MIT DEN VERBUNDENEN ZELLEN:
Ich möchte gerne per VBA auf einem geschützten Excel-Formular eine Dropdownliste (Datenüberprüfung) ändern. Okay – man hätte die auch per Formeln mit zwei Dropdownlisten erzeugen können – aber mit meiner VBA-Lösung bin ich flexibler.
Der Code lautet:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim intZeilen As Integer
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim intZeilen As Integer
If Target.Value = "Für Standorte" Or Target.Value = "Für Gebäude" Then
ActiveSheet.Unprotect
If Target.Value = "Für Standorte" Then
intZeilen = ThisWorkbook.Worksheets("tbl_Basisdaten").Range("B1").CurrentRegion.Rows.Count
Target.Value = "Für Gebäude"
With Target.Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=tbl_Basisdaten!$B$2:$B$" & intZeilen ' -- Spalte B bei "Standorten"
End With
ElseIf Target.Value = "Für Gebäude" Then
intZeilen = ThisWorkbook.Worksheets("tbl_Basisdaten").Range("D1").CurrentRegion.Rows.Count - 1
Target.Value = "Für Standorte"
With Target.Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=tbl_Basisdaten!$D$2:$D$" & intZeilen ' -- Spalte D bei "Gebäuden"
End With
End If
With Target.Offset(0, 1).Validation
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Target.Offset(0, 1).Value = ""
ActiveSheet.Protect
End If
End Sub
Ich erhalte einen Fehler:
Klaro – ich schützte das Blatt (ActiveSheet.Protect) und anschließend wirkt der Doppelklick, denn ich verwende ja das Ereignis BeforeDoubleClick. Die Lösung ist einfach: ich setze den Cursor auf eine nicht gesperrte Zelle – dann klappt es:
Target.Offset(0, 1).Activate
Witzigerweise befindet sich DANN der Cursor in keiner Zelle.
Ich habe den Fehler nicht einkreisen können. Aber er ist da:
In einem Excelformular werden Daten eingegeben. Dort werden Datenüberprüfungen verwendet, die Daten aus einem anderen Tabellenblatt holen. Die Liste verwendet einen Bezug auf das Blatt =tbl_Basisdaten!…
Ich kopiere über eine Schaltfläche ein Tabellenblatt „Interview BIA“ von einer anderen Datei in die aktuelle Datei.
Dadurch wird der Bezug auf die alte Datei hergestellt =[Alte Datei.xlsm]tbl_Basisdaten!…
Das ist mir leider nicht aufgefallen, weil die Dateien auf meiner Festplatte liegen und es deshalb zu keiner Fehlermeldung kommt.
Das muss raus! Okay – wir ändern das:
Ich generiere die Datenüberprüfungen aufgrund der Basisdaten auf dem Interviewformular. Ich verwende keinen Bezug, schreibe per Programmierung „Auftragsabwicklung;Arbeitsvorbereitung und Einkauf;Produktrealisierung Individual;Produktrealisierung maschinelle Fertigung;Wartung / Instandhaltung;Lager / Logistik;EDV-Systembetreuung:
For i = 2 To intZeilen
strZellinhalt = ThisWorkbook.Worksheets(Blatt).Range(strSpalte & i).Value
strListeDatenüberprüfung = strListeDatenüberprüfung & "," & strZellinhalt
Next
If strListeDatenüberprüfung Like "*,*" Then
strListeDatenüberprüfung = VBA.Mid(strListeDatenüberprüfung, 2)
End If
If strListeDatenüberprüfung <> "" Then
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=strListeDatenüberprüfung ' -- geändert, weil Interviewblatt nun importiert wird
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End If
Das Ergebnis:
Klappt:
Ich speichere die Datei, schließe sie und öffne sie:
„Wir haben ein Problem bei einigen Inhalten erkannt. Sollen wir so viel wie möglich wiederherstellen? Wenn Sie der Quelle dieser Arbeitsmappe vertrauen, klicken Sie auf ‚Ja‘.“ Die Datei ist kaputt! Ich finde den Fehler nicht!
VBA-Schulung. Wir erstellen eine Datei mit mehreren Tabellenblättern:
Wir schreiben ein kleines Makro, das uns bei der Blattsuche hilft:
Option Explicit
Option Compare Text
Sub BlattSuche()
Dim strBlattname As String
Dim i As Integer
strBlattname = InputBox("Bitte geben Sie den gesuchten Blattnamen ein!")
For i = 1 To ActiveWorkbook.Sheets.Count
If ActiveWorkbook.Sheets(i).Name = strBlattname Then
ActiveWorkbook.Sheets(i).Activate
Exit Sub
End If
Next
MsgBox "Das gesuchte Blatt " & strBlattname & " wurde nicht gefunden."
End Sub
Bei einer Teilnehmerin funktioniert das nicht:
Die Activate-Methode des Worksheet-Objektes kann nicht ausgeführt werden.
Ich werde stutzig, als ich das Menüband aufklappe:
Da entdecke ich, dass sie noch beim Schreiben eines Tabellenblattnamens ist:
VBA-in-Excel-Schulung. Am dritten Tag beginne ich mit einer Aufgabe: ein Makro soll alle Tabellenblätter schützen; ein zweites Makro den Schutz aufheben. Die Teilnehmer beginnen die Lösung zu erstellen. Eine mögliche Lösung wäre:
Sub AlleBlaetterSchuetzen()
Dim i As Integer
For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Protect
Next
End Sub
Sub BlattSchutzAufheben()
Dim i As Integer
For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Unprotect
Next
End Sub
Ein Teilnehmer erstellt auf dem ersten Tabellenblatt zwei Schaltflächen dafür:
Das Perfide: Wenn die Datei mehr als zwei Tabellenblätter hat, wechselt Excel beim Blattschutzaufheben zu einem anderen Tabellenblatt (dem vorletzten). Ich weiß nicht warum …
Ich erstelle ein Add-In für einen Kunden. Mit Makros und mit Symbolen im Menüband. Ein Teil des XML-Codes sieht wie folgt aus:
<tab id="tabBCM" label="BCM">
<group id="grpEinstellungen" label="Einstellungen">
<button id="cmdKonfigurationImpact" imageMso="FieldList" label="Konfiguration Impactbewertung" onAction="cmdKonfigurationImpact" size="large" supertip="Öffnet den Dialog zur Konfiguration der Impactbewertung des Kernprozesses" screentip="Dialog: Konfiguration"></button>
<button id="cmdKonfiguration" imageMso="ControlLayoutStacked" label="Konfiguration Betrachtungshorizont" onAction="cmdKonfiguration" size="large" supertip="Öffnet den Dialog zur Konfiguration" screentip="Dialog: Konfiguration"></button>
<button id="cmdBasisdaten" imageMso="ControlLayoutTabular" label="Basisdaten (Interview BIA)" onAction="cmdBasisdaten" size="large" supertip="Öffnet den Dialog zur Eingabe der Basisdaten" screentip="Dialog: Basisdaten"></button>
Das Ergebnis sieht wie folgt aus:
Mit fällt auf, dass auf einem kleinen Bildschirm (beispielsweise Laptop) die Gruppen zusammengepackt werden. Diese Darstellung gefällt mir nicht:
Zum Glück entdecke ich, dass man in die Gruppen auch Bilder (imageMso) einfügen kann. Und nun wird mir der Zweck klar: beim Verkleinern werden diese Symbole angezeigt. Das werde ich nun immer machen:
An der Darstellung des Menübandes hat sich nichts geändert:
Nein, nein, nein – so nicht! Bitte verbindet keine Zellen! Das bringt nur Ärger! Einige Ärgernisse habe ich hier schon beschrieben – über ein neues bin ich vor einigen Tagen gestolpert: Wenn A1 und A2 verbunden sind, dann liefert:
Inga meint, dass es gefährlich ist mit VBA bedingte Formatierungen zu programmieren. Warum? Ich zeichne mit dem Makrorekorder auf – wie lautet der Befehl: „färbe die aktuelle Zelle blau, wenn in A1 das heutige Datum steht“?
Sub HeuteMachenWirBlau() Selection.FormatConditions.Add Type:=xlExpression, Formula1:=“=A1=HEUTE()“ Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 12611584 .TintAndShade = 0 End With End Sub
Ich teste – klappt! Ich überlege mir: es wäre besser statt HEUTE TODAY zu schreiben; teste – klappt nicht mehr.
Das heißt: die Formeln laufen nur lokal – man müsste nun die einzelnen Sprachen unterscheiden, damit das Makro mehrsprachig funktioniert. Das ist die Hölle.
P.S.: Wenn Sie eine Idee
haben, wie ich in PowerPoint eine eingebettete Excel-Tabelle (OLEObject?)
ansprechen kann, so wäre ich Ihnen äußerst dankbar
Und so geht es: Mit folgendem Code können Sie auf die Tabelle zugreifen:
Dim ppApp As Application
Dim ppDatei As Presentation
Dim ppFolie As Slide
Dim ppShape As Shape
Dim ppObjekt As Object
Set ppApp = Application
Set ppDatei = ppApp.ActivePresentation
Set ppFolie = ppDatei.Slides(11)
Set ppShape = ppFolie.Shapes(3)
Set ppObjekt = ppShape.OLEFormat.Object
MsgBox ppObjekt.Sheets(1).Range("A2").Value
Ich melde mich dieses mal mit einer generellen Frage an Sie. Im Moment nervt mich nämlich PowerPoint. Es ist schon gemein, dass es da keinen Makrorekorder gibt.
Ich schätze mal, dass auch Sie ab und zu den Makrorekorder in Excel benutzen, wenn Ihnen mal dieser und jener Befehl nicht einfällt.
Nun, in PowerPoint geht das ja leider nicht. Daher meine Frage, wie gehen Sie eigentlich vor oder wo lesen Sie nach, wenn Sie PowerPoint-Kommandos benötigen und gerade nicht wissen, wie diese lauten?
Momentan beschäftige ich mich mit einer automatisierten Berichtserstellung aus Excel heraus. Das klappt auch alles soweit ganz gut, also neue Präsi aus Vorlage erstellen, Daten aus Excel in Diagramme oder Tabellen einfügen oder auch Diagramme von Excel nach PowerPoint zu exportieren. Schwierig wird es nur dann, wenn ich z.B. Formatierungen ausschließlich in PowerPoint durchführen muss. Ich habe z.B. ewig gebraucht um dahinter zu kommen, wie ich in einer PowerPoint-Tabelle eine Zelle farblich hervorhebe (Präsi.Slides(5).Shapes(„Inhaltsplatzhalter 3“).Table.Cell(a, 1).Shape.Fill.ForeColor.RGB = RGB(255, 0, 0)). Oder einen Pfeil einfügen (ActivePresentation.Slides(1).Shapes.AddShape Type:=msoShapeBentUpArrow, Left:=50, Top:=50, Width:=100, Height:=200), mit Animationen fange ich gar nicht erst an.
Haben Sie da einen Tipp oder ein schlaues Nachschlagewerk? Jemand hat mir mal erzählt (ich glaube, es war Andreas Thehos), dass man mit dem Makrorekorder in Word hier so einiges ableiten kann, aber da bin ich irgendwie noch nicht dahinter gekommen…
Ihnen schon einmal vielen Dank und viele Grüße,
Hallo Herr Dauphin,
das Problem des fehlenden Makrorekorders kenne ich –
darüberhinaus gibt es noch weitere Fallstricke:
Guten Tag Herr Martin
Vielen Dank für Ihre ausführlichen Mitteilungen in Ihrer letzten Mail
Inzwischen steht das erste Modul (Flächenmanagement).
Allerdings sind mir dabei Probleme begegnet, die ich nicht lösen konnte.
1.) Zugriff auf den richtigen Visio Prozess.
Der Zugriff auf Visio läuft prima, solange nicht mehrere Visio-Anwendungen laufen.
Hier die entsprechenden Code-Zeilen
Public vsoApp As Visio.Application
Public Sub Set_vsoApp()
‚Prüfen ob eine Visio Application läuft
‚Wenn ja, dann die laufende App als vsoApp definieren
‚Wenn nein dann eine neue Application starten
If GetObject("winmgmts:").ExecQuery("select * from win32_process where name='VISIO.EXE'").Count > 0 Then
Set vsoApp = GetObject(, "Visio.Application")
Else
Set vsoApp = CreateObject("Visio.Application")
End If
End Sub
Mit diesen Zeilen gelange ich zu einer Objektvariable vsoApp, deren Dokumente ich durchlaufe und das gewünschte Dokument entweder finde oder öffne.
Wenn nun aber mehrere Applikationen laufen und das gewünschte Dokument dummerweise nicht in der ersten App läuft, so funktioniert der Code nicht mehr. Das gewünschte Dokument wird nicht gefunden und kann auch nicht ein zweites Mal geöffnet werden.
Die Frage lautet darum:
Gibt es eine Möglichkeit die ganze Auflistung
„select * from win32_process where name=’VISIO.EXE'“
zu durchlaufen und einzelnen zu durchsuchen?
#
Hallo Herr M.,
und hier meine Antworten:
1.) Es gibt verschiedene Strategien. Ich habe ein Projekt, da arbeite ich folgendermaßen:
On Error GoTo Fehler
Set vsApp = CreateObject(„Visio.Application“)
[…]
Set vsDatei = vsApp.Documents.Open(strDateiName)
[…]
vsDatei.Save
vsDatei.Close
[…]
Call VisioSchliessen(True)
Set vsDatei = Nothing
Set vsApp = Nothing
Exit Sub
Fehler:
MsgBox „Es trat ein Fehler auf:“ & vbCr & Err.Number & „: “ & Err.Description
Das heißt: ich öffne einfach die Datei. Sollte die Datei schon offen sein, wird ein Fehler erzeugt, die Sprungmarke angesprungen und die Meldung ausgegeben, dass die Datei in Benutzung ist. Der Anwender muss sie zumachen und das Programm erneut starten.
Zweite Variante: Sie greifen mit GetObject auf das bereits geöffnete Visio und auf die bereits geöffnete Datei zu. Sollte sie noch nicht geöffnet sein, wird ein Fehler erzeugt, der verarbeitet wird, indem Visio, bzw. die Datei geöffnet wird:
Dim vsApp As Object
Dim vsDatei As Object
Const PFAD As String = „D:\Eigene Dateien\Räuberhauptmann2.vsdx“
On Error Resume Next
Set vsApp = GetObject(, "Visio.Application")
If Err.Number <> 0 Then
Err.Clear
Set vsApp = CreateObject("Visio.Application")
End If
vsApp.Visible = True
Set vsDatei = vsApp.Documents("Räuberhauptmann2.vsdx")
If Err.Number <> 0 Then
Err.Clear
Set vsDatei = vsApp.Documents.Open(PFAD)
End If
MsgBox vsDatei.Name
Und zur dritten (Ihrer Lösung) – durchlaufen Sie alle Dokumente von Visio:
Dim i As Integer
Dim blnDateiOffen As Boolean
blnDateiOffen = False
For i = 1 To vsoApp.Documents.Count
If vsoApp.Documents(i).Name = "Räuberhauptmann2.vsdx" Then
Set vsoDatei = vsoApp.Documents(i)
blnDateiOffen = True
End If
Next
If blnDateiOffen = False Then
Set vsoDatei = vsoApp.Documents.Open(PFAD)
End If
Ich erhalte eine Mail mit der Frage, wie man in einem ACCESS-Diagramm einen Datenpunkt mit VBA formatiert.
Da Access keinen Makrorekorder hat und da ich nicht genau weiß, wie der Datenpunkt in VBA heißt und mit welchen Eigenschaften man die Farbe ändern kann, erstelle ich in Excel ein Diagramm, verwende den Makrorekorder und baue den Code ein wenig um:
Der neue Code sieht folgendermaßen aus:
Dim s As Worksheet
Dim c As ChartObject
Dim cc As Chart
Dim f As FullSeriesCollection
Dim p As Point
Set s = ActiveSheet
Set c = s.ChartObjects(1)
Set cc = c.Chart
Set f = cc.FullSeriesCollection
Set p = f(1).Points(1)
p.Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
In Access empfiehlt es sich einen Verweis auf die Objektbibliothek „Microsoft Graph“ einzubinden:
Ich kopiere den Excel-VBA-Code nach Access und bin verblüfft, wie viel ich in Access ändern muss, um zu dem gleichen Ergebnis zu gelangen:
Dim rep As Report
Dim c As Graph.Chart
Dim f As Graph.SeriesCollection
Dim p As Graph.Point
Set rep = Reports(„repDiagramm“)
Set c = rep.Controls(„Diagramm1“).Object
Set f = c.SeriesCollection
Set p = f(1).Points(3)
p.Interior.Color = RGB(255, 0, 0)
Hätte man nicht die gleichen Befehle und Eigenschaften verwenden können? Hätte man sicherlich …
Dürfen die das? Ich wollte gerade in Excel mit VBA programmieren, öffne den VBA-Editor:
und bin ein bisschen verblüfft. Wo kommt denn DER Code her? Ich überlege. Stimmt: ich habe vor Kurzem das Add-In „Analyse-Funktionen“ installiert. Ich wollte etwas in einem der Assistenten nachschauen … Das hat man nun davon!
Nicht aufgepasst. Da habe ich einfach nicht aufgepasst!
Ich fülle eine Userform mit Daten. In einem Listenfeld werden Informationen angezeigt.
Beim Klicken auf einen Eintrag wird der erste Teil in einem Textfeld angezeigt, der zweite Teil im Kombinationsfeld, dessen Eigenschaft Style auf 2: fmStyleDropDownList gestellt wurde. Das Ergebnis: der Eintrag wurde nicht gefunden …
… und mit der Fehlermeldung „Eigenschaft Value konnte nicht gesetzt werden. Ungültiger Eigenschaftswert“ quittiert.
Also: immer gut aufpassen, was man wo reinschreibt!
Ich habe für eine Firma ein kleines Add-In geschrieben: Daten werden von A nach B übertragen und andere Daten zurück von B nach A. Um die korrekten Daten zu ermitteln verwende ich die Formeln – man kann es mit SVERWEIS machen – ich habe mich für die flexiblere Variante INDEX und VERGLEIC entschieden. Diese Formel wird in den Bereich eingefügt, der Bereich wird kopiert und als Werte wieder eingefügt:
On Error Resume Next
[...]
xlBereich.Copy
xlBereich.PasteSpecial Paste:=xlPasteValues
Das Programm läuft. Nach einigen Tagen erhalte ich einen Anruf:ein Fehler ist aufgetreten. Ich schaue es mir an. Sie Anwenderinnen haben auf den Bereich einen Filter gesetzt und gefiltert! Klaro – nun kann mein Makro nicht mehr die Inhalte als Werte einfügen:
Also überprüfe ich, ob ein Filter eingeschaltet ist. Wenn ja – dann wird er ausgeschaltet. Und schon kann das Programm wieder sauber die Daten übertragen …
Und was passiert? Ich soll eine Korrektur in einem Programm vornehmen, das ich vor einigen Monaten geschrieben habe. In der Spalte BC sollen nun auch die Werte übertragen werden. Also schnell den alten Code von oben kopieren, ändern, testen und: staunen. Warum? Klar – an einer Stelle habe ich vergessen BG3 in BC3 umzubenennen – deshalb wird der Bereich nun nicht von BC3:BC300 aufgespannt, sondern von BC300:BG3, also von BC3:BG300. Ich musste eine Weile suchen.
Immer wieder schön, wenn VBA von Excel so etwas behauptet:
Es läuft aber trotzdem. Vielleicht will er sich einfach nur wichtig machen. Oder auf sich aufmerksam machen. Bekommt vielleicht nicht genug Aufmerksamkeit.
Ich programmiere ein Formular für einen Kunden. Einige Zellen sollen dynamische gesperrt oder entsperrt werden. Ich erhalte eine Fehlermeldung:
Seltsam: Der Befehl:
MsgBox Range(„K158“).Locked liefert False
Okay – noch ein Versuch:
Nutzt nichts! Ich schaue nach:
Ah! Verbundene Zellen. Ich darf nicht eine Zelle aus diesem Zellverbund sperren oder entsperren – dies funktioniert nur bei der ersten (hier: C158). Könnte mir Excel VBA ja auch sagen …
Och, Leute – nö! Wie oft muss ich es sagen! Und ich sehe es immer wieder! Gestern zu Beispiel:
Wird in VBA deklariert:
Dim strDateiImport, strDateiExport As String
dann ist strDateExport vom Datentyp String, strDateiImport dagegen vm Typ Variant. Und dies kann zu Problemen führen. Beispielsweise beim Befehl Dir, der zwar „“ verarbeiten kann, aber nicht Leer (Null):
Also bitte:
Dim strDateiImport As String, strDateiExport As String
oder:
Dim strDateiImport As String Dim strDateiExport As String
Umgekehrt: Quizfrage: was liefern folgende Meldungsfenster:
Heute habe ich mit einem Kunden zusammen in VBA einige Dinge programmiert. Er zeigte mir die Sachen, die nicht funktionieren – beispielsweise die Schaltfläche, die per Programmierung ein Formular füllt, das anschließend angezeigt wird. Es wurde aber nicht angezeigt. Wir haben eine Weile gesucht. Wo hat es sich nur versteckt? Bis wir dahinter kamen, dass der Kunde vor Kurzem mit seinem Laptop mit zwei Bildschirmen gearbeitet hatte. Windows hatte den zweiten Bildschirm noch gespeichert – und dort – für uns nicht sichtbar! – wurde das Formular angezeigt. Böses Versteck!
Also: die StartUpPosition der Userform auf „Fenstermitte“ gestellt – und schon klappte es wieder!
Am Montag hat Johannes Curio auf unserem Excelstammtisch SharePoint-Listen vorgestellt. Er hat gezeigt, dass SharePoint zeilenweise speichert und dass man so Pflichtfelder anlegen kann. In Excel kann man so etwas nur per Programmierung. Schade eigentlich!
Der Vorteil von Excel: jeder darf alles überall hinschreiben. Der Nachteil: jeder schreibt alles überall hin!
Böses Excel! Ich erstelle ein dynamisches Excel-Formular mit VBA. Ich muss bestimmte Stellen ermitteln – beispielsweise die Position „7.1.“ Die Funktion
=VERGLEICH(„7.1.“;A:A;0)
liefert die Zeilennummer. Ich versuche es mit VBA:
Gefühlte 120 Versuche, warum WorksheetFunction.Match nicht funktioniert und wie man diese Funktion richtig schreibt. Die Match-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden. Bis ich dahinterkomme, dass ich nicht „7.1“ suche, sondern „7.1.“ Der letzte Punkt hat gefehlt. Während die Funktion
=VERGLEICH(„7.1“;A:A;0)
den Fehler #NV erzeugen würde, schreibt WorksheetFunction.Match erst gar nichts in die Zelle, beziehungsweise in das Meldungsfenster. VBA für Excel könnte ja wenigstens sagen, dass die FUNKTION okay ist, dass sie allerdings einen fehlerhaften WERT liefert. Aber nicht so etwas!
Erstaunlich. Ich programmiere ein Tool für eine Firma. Dort werden per VBA Daten in ein Formular eingetragen. Da mehrere Personen Zugriff auf das Formular haben, wird überprüft, ob das Formular geöffnet ist: die Eigenschaft ReadOnly liest aus, ob die Datei schreibgeschützt geöffnet wurde. Oder man versucht die Datei zu speichern – wird ein Fehler erzeugt, wurde die Datei bereits von einem anderen Anwender geöffnet.
Allerdings scheint es keine Eigenschaft oder Methode zu geben, mit EINFACHEN Mitteln mit VBA herauszufinden, welcher Anwender das Formular benutzt.
Schon doof. Ich erstelle für einen Kunden ein dynamisches Excel-Formular. Die Daten sollen automatisiert ausgelesen werden. Deshalb muss ich wissen wie „groß“ das Formular ist, das heißt: wie viele Zeilen es enthält und wo sich bestimmte Informationen befinden.
Ein bisschen mehr Mühe hätten sie sich schon bei den Fehlertexten machen können. Lediglich der Kopieren wird mit einem Verweis auf den Schutz der Arbeitsmappe quittiert …
Ich erstelle für einen Kunden ein Programm. Per VBA greift Visio auf Excel zu. Dabei wird überprüft, ob Excel (beziehungsweise eine bestimmte Datei) schon offen ist:
On Error Resume Next
' -- greife auf das offene Excel zu
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
' -- falls Excel nicht offen ist: öffne Excel
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
End If
On Error GoTo 0
Die Zeile GetObject(, „Excel.Application“) liefert einen Fehler, wenn sich der Cursor in einer Zelle befindet:
Eigentlich sollte es automatisch (automatisiert) gehen …
Ich weiß, das sollte man nicht machen. Ich lasse den Anwender über eine Inputbox eine Zahl eingeben, speichere sie in einer String-Variablen und schreibe das Ergebnis nach Excel:
Sub ZahlAlsTextEintragen()
Dim s As String
s = InputBox(„Bitte eine Zahl eintragen.“)
ActiveCell.Value = s
End Sub
Das Ergebnis: 1 bleibt 1 (Excel schafft eine korrekte Typkonvertierung). Aus 1,5 und 1,55 werden die Texte 1,5 und 1,55 (sie stehen linksbündig in der Zelle und werden mit einem Smarttag versehen, das „die Zahl in dieser Zelle als Text formatiert ist oder ein Apostroph vorangestellt wurde“.)
Gibt man allerdings 1,555 ein, wird diese Zahl in 1.555 konvertiert. Ups!
