W potocznej opinii rejestrator makr jest narzędziem dla początkujących. Jest to tylko część prawdy. Pomimo tego, że posiada liczne ograniczenia – są sytuacje, w których jest niezastąpiony. W tym tekście napiszę, kiedy warto skorzystać z jego pomocy.
Czego nie nagra rejestrator?
Rejestrator makr nie jest narzędziem idealnym – nagrany kod będzie się znacząco różnił od procedur napisanych przez profesjonalistę.
Dlaczego? Wynika to z faktu, że pewnych rzeczy nie zarejestrujemy popularną „nagrywarką”. Czego konkretnie?
- Deklaracji stałych i zmiennych.
- Instrukcji warunkowych.
- Pętli.
- Obsługi błędów.
- Modułów klas i funkcji API.
- Zdarzeń obiektów.
Te wszystkie „rzeczy” musimy napisać samodzielnie lub przekleić kod z gotowca – rejestrator nam w tym nie pomoże.
Kiedy używać „nagrywarki”?
Dlaczego rejestrator makr jest niezastąpiony pomimo licznych ograniczeń?
Przyczyna jest prosta. Excel posiada w swoim zbiorze kilkaset obiektów i nie ma możliwości aby zapamiętać budowę każdego z nich.
Zarejestrowany kod jest nadmiarowy, ale dzięki temu – możemy poznać właściwości danego obiektu. Zbędne linijki możemy skasować i zostawić tylko te, których potrzebujemy. Mamy gotowca i nie musimy korzystać z przeglądarki obiektów. Dzięki temu znacząco ułatwiamy sobie pracę!
Pole przydatności rejestratora jest tak duże, że nie sposób wymienić wszystkich sytuacji. Poniżej opiszę dziesięć obszarów, gdzie nagranie makra okazuje się bezcenne.
Wstawienie formuły
Bezdyskusyjnie u mnie na pierwszym miejscu – czynność tą wykonuję najczęściej.
Formułę możemy nagrać i wkleić w całości do swojego makra. Nazwy funkcji zostaną przetłumaczone na angielski, średniki zamienione na przecinki, komunikaty tekstowe opatrzone zostaną podwójnym cudzysłowem itp.
Nagranie formuły minimalizuje ryzyko błędu, które pojawia się, gdy kopiujemy ręcznie jej treść z paska formuły.
1 2 3 |
ActiveCell.FormulaR1C1 = "=VLOOKUP(5,R2C1:R30C5,3,FALSE)" |
Formatowanie warunkowe
Sporo osób korzysta w swoich arkuszach z narzędzia formatowania warunkowego. Rejestrator makr nagrywa obszerny kod dotyczący ustanowionych reguł. Możemy je później z łatwością edytować z poziomu edytora VBA.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
With Selection.FormatConditions(1) .ReverseOrder = False .ShowIconOnly = False .IconSet = ActiveWorkbook.IconSets(xl3Arrows) End With With Selection.FormatConditions(1).IconCriteria(2) .Type = xlConditionValuePercent .Value = 33 .Operator = 7 End With With Selection.FormatConditions(1).IconCriteria(3) .Type = xlConditionValuePercent .Value = 67 .Operator = 7 End With |
Tabele przestawne
Popularne pivoty mają wiele obiektów i aby dobrać się do konkretnych pól, potrzebna jest specjalistyczna wiedza lub…. nagranie makra.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
Sheets.Add ActiveWorkbook.Worksheets("Zadanie 2").PivotTables("TP_zad2").PivotCache. _ CreatePivotTable TableDestination:="Arkusz1!R3C1", TableName:= _ "Tabela przestawna1", DefaultVersion:=xlPivotTableVersion12 Sheets("Arkusz1").Select Cells(3, 1).Select With ActiveSheet.PivotTables("Tabela przestawna1") .ColumnGrand = True .HasAutoFormat = False .DisplayErrorString = False .DisplayNullString = True .EnableDrilldown = True .ErrorString = "" .MergeLabels = False .NullString = "" .PageFieldOrder = 2 .PageFieldWrapCount = 0 .PreserveFormatting = True .RowGrand = True .SaveData = True .PrintTitles = False .RepeatItemsOnEachPrintedPage = True .TotalsAnnotation = False .CompactRowIndent = 1 .InGridDropZones = False .DisplayFieldCaptions = True .DisplayMemberPropertyTooltips = False .DisplayContextTooltips = True .ShowDrillIndicators = True .PrintDrillIndicators = False .AllowMultipleFilters = False .SortUsingCustomLists = True .FieldListSortAscending = False .ShowValuesRow = False .CalculatedMembersInFilters = False .RowAxisLayout xlTabularRow End With With ActiveSheet.PivotTables("Tabela przestawna1").PivotCache .RefreshOnFileOpen = False .MissingItemsLimit = xlMissingItemsDefault End With ActiveSheet.PivotTables("Tabela przestawna1").RepeatAllLabels 2 |
Wykresy
Wykresy – podobnie jak tabele przestawne – są bardzo skomplikowanymi obiektami. W ich skład wchodzi całe mnóstwo mniejszych obiektów.
Już samo rozróżnienie ChartObject od Chart rodzi spory problem początkującym. I o ile rejestrator nam w tym konkretnym przypadku nie pomoże, o tyle wesprze nas właśnie przy tych mniejszych obiektach jak seria danych, punkt czy oś.
1 2 3 4 5 |
ActiveSheet.Shapes.AddChart2(227, xlLine).Select ActiveChart.SetSourceData Source:=Range("'Table 0'!$A$1:$C$36") ActiveChart.Axes(xlCategory).HasMajorGridlines = True |
Drukowanie
Przy podglądzie wydruku generuje nam się lista wszystkich właściwości obiektu PageSetup.
Możemy usunąć niepotrzebne linie kodu i zmodyfikować wartości tych właściwości, które nas interesują. Mam tutaj na myśli zwłaszcza: obszar wydruku, orientację, szerokość marginesów, teksty w nagłówku i stopce, drukowanie nagłówków i linii siatki.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
Application.PrintCommunication = False With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With Application.PrintCommunication = True ActiveSheet.PageSetup.PrintArea = "" Application.PrintCommunication = False With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "MAKROAPLIKACJE.PL" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.7) .RightMargin = Application.InchesToPoints(0.7) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.75) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With Application.PrintCommunication = True |
Sprawdzanie poprawności
Czasami warto wprowadzić pewne ograniczenia związane z wpisywaniem wartości do komórek np. maksymalną liczbę określić na podstawie komórki obok.
Jak się do tego zabrać? Na pierwszy rzut oka wydaje się to trudne, jednak przy użyciu rejestratora makr jest banalnie proste.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=A1<=B1" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "UWAGA !" .ErrorTitle = "BŁĄD !" .InputMessage = _ "Wartość komórki B1 nie może być większa od wartości komórki A1." .ErrorMessage = "Wystąpił błąd wprowadzania danych." .ShowInput = True .ShowError = True End With |
Filtrowanie
Jednym z paradoksów VBA jest to, że filtr zaawansowany ma dużo prostszą konstrukcję od zwykłego filtra.
Tego pierwszego używam bardzo często, drugiego – bardzo rzadko. Gdy to jednak robię – zawsze korzystam z rejestratora makr.
1 2 3 4 5 6 7 |
Range("A1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$C$36").AutoFilter Field:=1, Criteria1:=Array( _ "dolar amerykański", "dolar australijski", "dolar Hongkongu", "dolar kanadyjski", _ "dolar nowozelandzki", "dolar singapurski"), Operator:=xlFilterValues |
Obramowanie
Nigdy nie pamiętam właściwości i stałych obiektu Borders, który odpowiada za obramowanie zakresu. Tymczasem wstawienie i usunięcie ramek generuje bardzo pokaźny kod, który możemy dopasować pod swoje preferencje.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone |
Formatowanie niestandardowe
Zamiast podawać je bezpośrednio w kodzie makra – zdecydowanie lepiej jest je nagrać. Gotowy wynik możemy np. podstawić do stałej.
1 2 3 |
Selection.NumberFormat = "($#,##0.00_);[Red]($#,##0.00)" |
Formanty ActiveX
Formanty w arkuszu dzielą się na kontrolki formularza i kontrolki ActiveX. Te drugie są pochodną kontrolek, których używamy w formularzach Userform.
W przypadku kontrolek ActiveX mamy podobny problem jak przy wykresach (ChartObject vs. Chart). Tutaj mamy obiekt główny OLEOject, ale dostęp do właściwości kontrolki uzyskujemy dopiero odwołując się do Object.
1 2 3 4 5 |
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _ , DisplayAsIcon:=False, Left:=605.25, Top:=45.75, Width:=142.5, Height _ :=30).Select |
Podsumowanie
Rejestrator makr jest często nadużywany w początkowej fazie nauki VBA, co może prowadzić do generowania nieefektywnego kodu. Niemniej jednak, jest on niezbędnym narzędziem pracy, ponieważ pozwala szybko zrozumieć właściwości i metody obiektów w Excelu. Dzięki rejestratorowi użytkownicy mogą obserwować, jak Excel interpretuje ich działania i jak przekłada to na kod VBA, co przyspiesza proces nauki i rozwoju.