Odświeżanie nazwanych zakresów

Opublikowany przez
Odpowiedz

Często pojawiającym się problemem jest zmiana zakresów nazwanych zakresów 🙂
Poniższy kod dopasuje nowe zakresy do ich nazw. Opieramy się na założeniu, że zakresy nie sąsiadują bezpośrednio z innymi danymi

Sub AktualizujNazwy()
    Dim Nazwa As Name
    For Each Nazwa In Names
        Nazwa.RefersToRange.CurrentRegion.Name = Nazwa.Name
    Next
End Sub

Usuwanie fragmentów tekstu ograniczonego parą znaków

Opublikowany przez
1

Ostatnio dwukrotnie zostałem poproszony o rozwiązanie problemu usuwania części tekstu pomiędzy parą znaków
Np „Ala (hihi) ma kota (hehe) a kot to” – powinno zostać zamienione na „Ala ma kota a kot to”
Oto rozwiązanie, pisane jak zwykle na szybko, więc pewnie ktoś już to kiedyś zrobił lepiej 🙂

' Wymaga biblioteki Excela
' ze względu na obiekt WorksheetFunction
Function PominTekst( _
    Tekst As String, Ogr1 As String, Ogr2 As String, _
    BezZbednychSpacji As Boolean)
 
    Dim Pocz As Long, Wynik As String
    Dim PozOgr1 As Long
    Dim PozOgr2 As Long
    Pocz = 1
    Do
        PozOgr1 = InStr(Pocz, Tekst, Ogr1)
        If PozOgr1 = 0 Then Exit Do
        PozOgr2 = InStr(PozOgr1 + 1, Tekst, Ogr2)
        Wynik = Wynik & Mid(Tekst, Pocz, PozOgr1 - Pocz)
        Pocz = PozOgr2 + 1
    Loop
    Wynik = Wynik & Mid(Tekst, Pocz)
    If BezZbednychSpacji Then
        PominTekst = WorksheetFunction.Trim(Wynik)
    Else
        PominTekst = Wynik
    End If
End Function

Pobieranie pliku ze strony WWW

Opublikowany przez
Odpowiedz

Można tego dokonać całkiem prosto:

 
Sub PobierzPlik()
Dim Link As String
Link = "http://serwer.pl/pliki/katalog.csv"
ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
End Sub

Usuwanie wybranych obiektów z kolekcji OLEObjects

Opublikowany przez
Odpowiedz

Jak się okazuje Excel nadaje się do wszystkiego 🙂 Ostatnio troszeczkę pomogłem przy aplikacji, gdzie Excel występował w roli kontenera na różne pliki (może OneNote byłby tu lepszy?). W każdym razie był problem z usuwaniem grupy plików tego samego typu bez względu na wersję (dokładnie chodziło o pliki Worda). Sprawa okazała się dość prosta, po znalezieniu właściwości progID, zwracającej ślicznego stringa z nazwą i wersją aplikacji, np:
PowerPoint.Show.8
Word.Document.8
Word.Document.12
Excel.Sheet.8

W związku z tym wystarczyło użyć For Each .. Next dla kolekcji OLEObjects i użyć w warunku usuwania prostej funkcji tekstowej left:

Private Sub pUsun_Click()
    Dim OLEOb As Excel.OLEObject
 
    For Each OLEOb In ActiveSheet.OLEObjects
        If Left(OLEOb.progID, 4) = "Word" Then OLEOb.Delete
    Next
 
    Set OLEOb = Nothing
End Sub

Usuwanie duplikatów

Opublikowany przez
Odpowiedz

Jedną z nowości w Excelu 2007/2010 jest narzędzie do usuwania duplikatów. Bardzo przydatne np do tworzenia słowników. We wcześniejszych wersjach ten efekt można było osiągnąć za pomocą opcji filtra zaawansowanego, co nie było bardzo proste i mało kto o tym wiedział. Nowego narzędzia Usuń duplikaty da się też użyć z poziomu kodu, np:

'---
ActiveSheet.UsedRange.Columns(10).RemoveDuplicates Columns:=1, Header:=xlYes
'---

Transpozycja części kolumn zakresu

Opublikowany przez
1

Dzisiaj spory kawałek kodu. Aplikacja „Transpozycja części kolumn” naprawia błąd projektu tabelki, w której występuje grupa kolumn (np kolejne miesiące są nagłówkami kolumn). Zamienia ona te kolumny na dwie: Nagłówek i Wartość, odpowiednio wydłużając tabelkę przyległymi danymi. Taka tabelka dużo lepiej nadaje się do analizy…
Udanej transpozycji życzę 🙂

Option Explicit
 
Sub TranspozycjaCzesciKolumn()
 
    ' funkcjonalność:
    ' ===============
    ' Aplikacja zamienia zaznaczony zakres
    ' grupy kolumn w dwie kolumny - nagłówek i wartość,
    ' powtarzając dane przyległe
    ' dla każdego dokładanego wiersza.
    ' Wynik umieści w nowym arkuszu.
 
    ' założenia:
    ' ==========
    ' - zakres do transpozycji to końcowe kolumny tabeli
    '   wraz z nagłówkami (ma nagłówki!)
    ' - nazwy nagłówków kolumn docelowych
    '   to stałe NAGLOWEK i WARTOSC
    '   (można sobie zmienić ich wartość)
    ' - nie sprawdzamy, czy wynik zmieści się w arkuszu
 
    Const WERSJA As String = "Transpozycja części kolumn v.1.0 (Akademia-VBA.pl)"
    Const NAGLOWEK As String = "NAGŁÓWEK"
    Const WARTOSC As String = "WARTOŚĆ"
 
    Dim NazwaKolumny As String
    Dim ZakresTranspozycji As Range
    Dim ZakresPowtarzany As Range
    Dim ZakresCalkowity As Range
    Dim LW As Long, LK As Long, RoznicaK As Long, K As Long
    Dim LW_Wart As Long, W As Long
    Dim Ark As Worksheet
    Dim ZakresPowtarzany_BN As Range
    Dim KomNaglowkowa As Range, ZakresWartosci As Range
    Dim PoczNaglowki As Range, PoczWartosci As Range
 
    On Error GoTo Obsluga
 
    Set ZakresTranspozycji = Application.InputBox( _
        Prompt:="Podaj zakres kolumn do transpozycji (z nagłówkami)", _
        Title:=WERSJA, _
        Type:=8)
    LW = ZakresTranspozycji.Rows.Count
    LK = ZakresTranspozycji.Columns.Count
    Set ZakresCalkowity = ZakresTranspozycji.CurrentRegion
    RoznicaK = ZakresCalkowity.Columns.Count - LK
    Set ZakresPowtarzany = _
        ZakresCalkowity.Resize(LW, RoznicaK)
    ZakresPowtarzany.Interior.ColorIndex = 34
    ZakresTranspozycji.Interior.ColorIndex = 36
    Set Ark = Sheets.Add()
    Ark.Name = "Transpozycja" & StempelCzasowy
    ZakresPowtarzany.Copy Ark.Range("A1")
    Set ZakresPowtarzany_BN = _
        ZakresBezNaglowka(Ark.Range("A1").CurrentRegion)
    ZakresPowtarzany_BN.Interior.ColorIndex = 37
    LW_Wart = ZakresPowtarzany_BN.Rows.Count
    Set PoczNaglowki = Ark.Cells(1, RoznicaK + 1)
    PoczNaglowki = NAGLOWEK
    Set PoczWartosci = Ark.Cells(1, RoznicaK + 2)
    PoczWartosci = WARTOSC
    For K = 1 To LK
        Set KomNaglowkowa = ZakresTranspozycji.Cells(1, K)
        Set ZakresWartosci = _
            ZakresBezNaglowka(ZakresTranspozycji.Columns(K))
        For W = 1 To LW_Wart
            KomNaglowkowa.Copy PoczNaglowki.Offset(W, 0)
        Next
        Set PoczNaglowki = PoczNaglowki.Offset(LW_Wart, 0)
        ZakresWartosci.Copy PoczWartosci.Offset(1, 0)
        Set PoczWartosci = PoczWartosci.Offset(LW_Wart, 0)
        If K = LK Then Exit For
        ZakresPowtarzany_BN.Copy _
            ZakresPowtarzany_BN.Offset(LW_Wart * K, 0)
    Next
 
    Exit Sub
Obsluga:
    If Err = 424 Then
        MsgBox "Należy zaznaczyć zakres kolumn do transpozycji", _
            vbExclamation, WERSJA
    Else
        MsgBox Err.Description, vbCritical, WERSJA
    End If
End Sub
 
Function ZakresBezNaglowka(Zakres As Range) As Excel.Range
 
    Dim LW As Long, LK As Long
 
    LW = Zakres.Rows.Count
    LK = Zakres.Columns.Count
    Set ZakresBezNaglowka = _
        Range(Zakres.Cells(2, 1), Zakres.Cells(LW, LK))
 
End Function
 
Function StempelCzasowy() As String
    StempelCzasowy = Format(Now(), "_yyyymmdd_hhmmss")
End Function

Wartość minimalna z tablicy

Opublikowany przez
Odpowiedz

Ponieważ pytanie o minimalną wartość z tablicy pojawia się na forach VBA z częstotliwością semestralną, postanowiłem umieścić kawałek kodu, pomagający w rozwiązaniu problemu:

Function MinTablicy(Tablica As Variant) As Variant
    Dim WartMin As Variant
    Dim IndeksDolny As Long, IndeksGorny As Long, i As Long
    IndeksDolny = LBound(Tablica)
    IndeksGorny = UBound(Tablica)
    WartMin = Tablica(IndeksDolny)
    For i = IndeksDolny To IndeksGorny
      If Tablica(i) < WartMin Then WartMin = Tablica(i)
    Next
    MinTablicy = WartMin
End Function
 
Sub TestFunkcji()
    Dim Tablica() As Variant
    Tablica = Array(7, 3, 5, 2, 6)
    MsgBox "Minimalana wartość z tablicy to: " & MinTablicy(Tablica)
End Sub

Sprawdzenie dostępności pliku

Opublikowany przez
2

Czasem chcielibyśmy wiedzieć, czy plik na którym chcemy wykonać jakąś operację, jest dla nas dostępny. Np gdy jest otwarty przez innego użytkownika próba jego użycia może powodować błąd. Ponadto wcześniej należy sprawdzić czy dany plik w ogóle istnieje. Można to sprawdzić przy pomocy poniższych funkcji:

Public Const WERSJA as String = "Dostępność pliku v.0.1"
Sub TestFunkcji()
    Const NAZWA_PLIKU As String = "C:\EX04\Baza.xls"
    If BrakPliku(NAZWA_PLIKU) Then Exit Sub
    If PlikNiedostepny(NAZWA_PLIKU) Then Exit Sub
    MsgBox "Plik jest dostępny, można coś z nim robić ;)", vbInformation
End Sub
 
Function PlikNiedostepny(NazwaPliku As String) As Boolean
    Dim NrPliku As Long
 
    On Error GoTo Obsluga
 
    NrPliku = FreeFile()
    Open NazwaPliku For Binary Access Read Write Lock Read Write As #NrPliku
    Close #NrPliku
    Exit Function
Obsluga:
    Select Case Err
    Case 70
        MsgBox "Plik jest obecnie otwarty przez inny proces", vbExclamation
    Case Else
        MsgBox "Przy próbie otwarcia pliku zgłaszany jest błąd: " _
            & Err & " - " & Err.Description, vbCritical
    End Select
    PlikNiedostepny = True
End Function
 
Function BrakPliku(PelnaNazwaPliku As String) As Boolean
    If Dir(PelnaNazwaPliku) = "" Then
        MsgBox "Brak pliku", vbCritical, WERSJA
        BrakPliku = True
    End If
End Function

Deklaracja zmiennych

Opublikowany przez
4

Dla początkujących użytkowników VBA nieodmiennie zagadką jest deklarowanie zmiennych. Po pierwsze w ogóle nie wiadomo po co to pisać, skoro bez tego i tak działa, a po drugie nazwy typów zmiennych nie brzmią zbyt przyjaźnie.
Niestety brak stosowania i rozumienia deklaracji jest głównym powodem błędów, literówek, spowolnienia działania kodu i niewygody jego pisania. Temat nie jest bardzo prosty, ale bardzo ważny. Tu krótko wymienię zalety rozumienia i stosowania właściwych deklaracji:

  • Kod działa nawet do 4 razy szybciej
  • Łatwiej pisać kod bo zmienne zadeklarowane są wspomagane przez technologię podpowiadania słów kluczowych – Intellisense
  • Unikamy literówek
  • Piszemy kod wysokiej jakości pozwalający na szybsze wychwytywanie błędów – np niezgodność typu co przy przeoczeniu może prowadzić do negatywnych konsekwencji
  • Poprawiamy czytelność kodu

Problem polega na tym, że ta wiedza przychodzi z czasem, a tego jak zwykle wszystkim brakuje …

Test wtyczki do automatycznej publikacji WP -> FB

Opublikowany przez
Odpowiedz

Jako, że blog Akademia-VBA.pl stoi na WordPressie to myślę, że czasem mogę i o tych doświadczeniach coś napisać. Właśnie testuję wtyczkę Add Link to Facebook i jeżeli to czytacie na FB to znaczy, że działa 🙂
Instalacja wtyczki nie jest specjalnie prosta bo trzeba sobie najpierw utworzyć aplikację na FB i coś tam powpisywać, ale jak ja dałem radę, to chyba inni też sobie poradzą 😉
Ale np ostatniego wpisu już nie udało mi się automatycznie zaktualizować 🙁
Dostałem komunikat błędu:
…Get me: Error validating access token: Session has expired at unix time…

Aby rozwiązać problem należy ponownie wejść do ustawień wtyczki i ponownie wcisnąć przycisk Autoryzuj i ponownie opublikować post 🙂