Excel 2007 Lista wartości

Jak przypisać komórce Excel-a słownik - na bazie listy rozwijanej.

Excel Formatowanie warunkowe... - Conditional Formatting

Excel oferuje opcje formatowania tekstu. Wystarczy zaznaczyć komórkę lub nawet cały blok i w menu kontekstowym pod prawym klawiszem myszki znajduje się opcja "Format" (Format cells). Tam można wybrać format, czcionkę, kolory, ramki, wzorce i dodatkowo zablokować komórkę / komórki.

Czasami jednak chcielibyśmy, żeby formowanie czcionek w skoroszycie odbywało się na podstawie wartości czy formuł tychże komórek. Wtedy arkusz staje się bardziej przejrzysty i gdy wyliczone wartości będą np przekraczać graniczne ramki trzeba to koniecznie podkreślić - wyróżnienie kolorem jak najbardziej się sprawdzi. Do tego celu należy posłużyć się funkcją dostępną w górnym menu Excela w zakładce Format - Formatowanie warunkowe.

Aby skorzystać z tej opcji należy zaznaczyć dowolny blok komórek (najlepiej wypełnionych tak żeby było widać jakieś różnice). Wybieramy z menu opcje Formatowanie warunkowe i zaczynamy wypełniać warunki.

W zależności czy w zaznaczone komórki będziemy sprawdzać na podstawie warunku mniejszości/większości (>,>=,<,<=, =, <>) czy zwyczajnej Excelowej formuły wybieramy z pierwszego ComboBoxa wartości lub formuły i podajemy kolejne warunki np. jeśli wartości są większe od 100 to należy ustawić w nich tło na czerwono jeśli mniejsze od zera to czcionka na niebiesko itp.

Excel operacje na zakresach, zakresy a formuły tablicowe

Pracując nad tworzeniem formuł odwołujących się do operatorów zakresowych należy używać CTRL + SHIFT + ENTER zamiast samego Entera.

Jeśli np chcemy policzyć średnią dla wszystkich komórek danego zakresu wpisujemy np:

F2 (edycja komórki C1)
=average(A1:B100)
Enter

Jeśli potrzebujemy średniej ograniczanej warunkami na wartości komórek powinniśmy zrobić coś takiego:

F2 (edycja komórki C1)
=AVERAGE(IF((A1:B100<50)*(A1:B100>0);B1:C2;0))
CTRL + SHIFT + Enter

W ten sposób obliczymy sobie średnią komórek z zakresu A1:B100 zachowujących własność bycia większym 0 i mniejszym od 50

Trzeba zwrócić uwagę na to, że łączenie warunków odbyło się tutaj z pomocą operatora:

() * ()

Ostatnia pełna i pierwsza pusta komórka

lastCell = Application.WorksheetFunction.CountA(Range("B:B"))

można też:

 CurrentRegion.Rows.Count
'lub
CurrentRegion.Cols.Count
'lub
CurrentRegion.Count

Liczba zaznaczonych obszarów:

Selection.Areas.Count

Obsługa zdarzeń klawiatury GetKeyState - Shift

Public Declare Function GetKeyState Lib "USER32" _
(ByVal nVirtKey As Long) As Integer

Sub Klawisz()
  If GetKeyState(16) < 0 Then
  ' &H10 = VKSHIFT
    MsgBox "Wciśnięto klawisz Shift"
  Else
    MsgBox "Shift został zwolniony"
  End If
End Sub

słowa kluczowe: klawiatura zdarzenie klawisz

Katalog systemowy Windows

Jak wyświetlić ścieżkę do katalogu systemowego Windows?

Sub WindowsSystemFolder()
   Set obj = CreateObject("Scripting.FileSystemObject")
   WindowsDirectory = obj.GetSpecialFolder(WindowsFolder)
   Set obj = Nothing
   MsgBox WindowsDirectory
End Sub

słowa kluczowe: windows dir, katalog Windowsa, PATH, ścieżka windows

VBA Function Average - Średnia

Przedstawiam w miarę uniwersalną i prostą funkcję do obliczania wartości średniej z zaznaczonych komórek:

Function MyAverage(rng As Range) As Double
Application.Volatile

'default for MyAverage is 0
Dim numElements As Integer

numElements = 0
For Each cell In rng
  If Not IsEmpty(cell) Then
   If IsNumeric(cell) Then
       MyAverage = MyAverage + cell
       numElements = numElements + 1
   End If
  End If
Next

If numElements > 0 Then
  MyAverage = MyAverage / numElements
ElseIf numElements = 0 Then
  MyAverage = 0
End If

End Function

Przykład użycia:

' w komórce A1 wpisujemy
=MyAverage(B1:B100)

Objaśnienia: Application.Volatile - Funkcja zareaguje automatycznie na zmianę wartości w komórkach IsEmpty(cell), isNumeric(cell) - najpierw sprawdzamy czy komórka nie jest przypadkiem pusta a dopiero potem czy znajduje się tam liczba numElements > 0 - nie zawsze zakres będzie przechowywał jakieś liczby

słowa kluczowe: własna funkcja na obliczanie średniej arytmetycznej, średnia arytmetyczna, średnio, wylicz średnią, średnia w Excelu, skrypt na średnią w VBA

Obsługa błędów - Errors - Jak obsługiwać błędy w VBA

W VBA podobnie jak w innych językach możemy przechwytywać nietypowe zdażenia w postaci błędów. Aby dobrać się do tych błędów (Errors) należy posłużyć się funkcją CVErr, która zwraca wartość błędu na podstawie jego numeru i tak wyróżnia się błędy

• xlErrDiv0
• xlErrNA
• xlErrName
• xlErrNull
• xlErrRef
• xlErrNum
• xlErrValue

I tak dla prostej procedurki możemy napisać:

Sub obslugaSytuacjiWyjatkowych()
  
  If (ActiveCell.Offset(0, 1).Value = 0) Then
    ActiveCell.Offset(0, 2).Value = CVErr(xlErrDiv0)
  
  ElseIf (ActiveCell.Text = "") Then
    ActiveCell.Offset(0, 2).Value = CVErr(xlErrNull)
  
  ElseIf (ActiveCell Is Empty) Then
    ActiveCell.Offset(0, 2).Value = CVErr(xlErrNull)
  
  Else
    ActiveCell.Offset(0, 2).Value = _
      ActiveCell.Value / ActiveCell.Offset(0, 1).Value
  End If
End Sub

Teraz należy w polu A1 wpisac liczbę 3, B1 liczbe 4, ustawić się na A1 Alt + F8 wykonać makro i w C1 powinnien pojawić się wynik dzielnia. Teraz, jeśli działa należy spróbować manipulować komórkami A1 i B1 wpisując tam 0 liczbę albo np pozostawić pola puste. Aby wszystko dobrze działało w takich przypadkach należy obsługiwać wszyskie typy błędów - jest to kłopotliwe, jednak tego wymaga tzw dobry styl programowania... Pokaz obsługi błędów w aktywnej komórce

Sub bledyAktywnejKomorki()
If IsError(ActiveCell.Value) Then
    wartoscbledu = ActiveCell.Value
    Select Case wartoscbledu
        Case CVErr(xlErrDiv0)
            MsgBox "Dzielenie przez zero"
        Case CVErr(xlErrNum)
            MsgBox "Num"
        Case CVErr(xlErrRef)
            MsgBox "REF"
        Case CVErr(xlErrValue)
            MsgBox "VALUE"
        Case CVErr(xlErrNA)
            MsgBox "Nierzeczywiste"
        Case CVErr(xlErrName)
            MsgBox "Błąd w nazwie"
        Case CVErr(xlErrNull)
            MsgBox "NULL"
        Case Else
            MsgBox "Nie ma więcej błędów."
    End Select
End If
End Sub

słowa kluczowe: błędy, błąd, wyjątki, obsługa wyjątków, zdarzenia, zdarzenie, err, errors, obsługa błędów