Rejestrator makr domyślnie nagrywa nazwę pliku i arkusza, z których pobieramy dane. Problem pojawia się w sytuacji, gdy któraś z tych nazw ulegnie zmianie. Zmienna nazwa stanowi problem, więc potrzebujemy zapisać w makrze elastyczne odniesienie do takiej zakładki.
Problem z nazwą arkusza
W przypadku arkusza sprawa jest prosta, gdy nie korzystamy z makr. Gdy pobieramy dane z innej zakładki i później zmienimy jej nazwę – formuła automatycznie nam się dopasuje.
Sprawa się komplikuje, gdy chcemy wprowadzić formułę makrem. W takiej sytuacji najczęściej nagrywamy ją rejestratorem i wklejamy do edytora VBA. Domyślnie zawiera ona zarówno nazwę skoroszytu jak i arkusza.
Jest to dosyć niebezpieczne i w praktyce często prowadzi do błędów. Nazwa zakładkowa może się przecież zmienić i różnić się od tej podanej w treści makra. Możliwe, że akurat w danym dniu raport musiał zostać utworzony ręcznie. Osoba przygotowująca go wpisała inną nazwę arkusza i podmieniła pliki na serwerze nie informując nas o tym.
Cały automat w takiej sytuacji bierze w łeb – makro po prostu się wysypie.
Niestety w takiej formule nie możemy odwołać się do nazwy kodowej, która jest niezależna od nazwy zakładkowej.
Przerzucenie nazwy do zmiennej
Zmienna nazwa arkusza może prowadzić do błędów. Jak się przed tym zabezpieczyć?
Najlepiej sczytać nazwę arkusza do zmiennej i później wstawić ją do treści samej formuły.
Jest to rozwiązanie elastyczne, odporne na zmiany w nazwie arkusza, ponieważ akceptuje każdą dozwoloną nazwę.
Nazwę pliku możemy natomiast przechować w stałej. Jeżeli się zmieni – będzie nam łatwiej ją edytować w stałej niż bezpośrednio w w formułach.
|
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 |
Private Const msMODUL As String = "MAktualizacja" Option Explicit Private Sub SczytajAktualneCeny() Const sPROC As String = "SczytajAktualneCeny" Const sPLIK_CENY_NAZWA As String = "Aktualne ceny.xlsm" Const sPLIK_CENY_SCIEZKA As String = _ "C:\Documents and Settings\Mariusz Jankowski\Desktop\Aktualne ceny.xlsm" Dim wkbCeny As Workbook ' Plik "Aktualne ceny.xlsm" Dim sNazwaArkusza As String ' Nazwa "zakładkowa" pierwszego arkusza Dim sCiagTekstowy As String ' Ciąg potrzebny do wstawienia formuły 'Aktywuj obsługę błędów na starcie 1 On Error GoTo ObslugaBledu 'Otwórz plik z aktualnymi cenami i pobierz do zmiennej aktualną nazwę 2 Set wkbCeny = Workbooks.Open(Filename:=sPLIK_CENY_SCIEZKA, ReadOnly:=True) 3 sNazwaArkusza = wkbCeny.Worksheets(1).Name 'Zdefiniuj ciąg tekstowy potrzebny do wstawienia formuły makrem 4 sCiagTekstowy = "'[" & sPLIK_CENY_NAZWA & "]" & sNazwaArkusza & "'" 'Wstaw formułę makrem 5 With wksRaport.Range("B2:B10") 6 .FormulaR1C1 = _ "=IFERROR(VLOOKUP(RC1," & sCiagTekstowy & "!R1C1:R99999C2,2,FALSE),""brak ceny"")" '"=IFERROR(VLOOKUP(RC[-1],'[Aktualne ceny.xlsm]04-10-2012 (4)'!R[-1]C1:R[99997]C2,2,FALSE),""brak ceny"")" 7 .Value = .Value 8 End With 'Zamknij plik 9 wkbCeny.Close SaveChanges:=False Wyjscie: 10 Set wkbCeny = Nothing 11 On Error GoTo 0 12 Exit Sub ObslugaBledu: 13 MsgBox "Wystąpił błąd nr " & Err.Number & " (" & Err.Description & ")." _ & vbCr & vbCr & "Linia kodu nr " & Erl & " w procedurze """ & _ sPROC & """ modułu """ & msMODUL & """.", vbInformation, "BŁĄD!" 14 Resume Wyjscie End Sub |
Screen z rozwiązaniem
