Telefonszámunk: 1-472-0679

Macro-VBA

Internetkapcsolat tesztelése VBA kódból

On 2013-01-27   /     /   Leave a comment

Ha van Internet kapcsolatunk, akkor EXCEL VBA kód segítségével az Interneten lévő forrásokat is használhatjuk, pl.

  • Megnyithatunk egy publikus weboldalt, és olvashatjuk  a tartalmát
  • Be tudunk jelentkezni egy jelszóval védett oldalra
  • GET vagy POST adatokat küldhetünk egy weboldalnak
  • Egy weboldal által visszaküldött választ fogadhatunk, majd feldolgozhatunk (JSON vagy XML)
  • stb…


Mindezekhez azonban szükséges ellenőrizni, hogy van-e egyáltalán élő internet kapcsolatunk. Ehhez nyújt segítséget a következő kód, ami a Windows egyik DLL hívását használja.

Az elején láthatjuk a Windows API függvény definíciót, majd következik az a saját függvény, ami az API hívás alapján eldönti, hogyvan-e kapcsolat vagy nincs, és logikai visszatérési értéket ad.

Az utolsó subrutin pedig bemutatja használat közben.

Csak másoljuk be egy standard modulba, és máris kipróbálhatjuk.

[vb]
‘Windows API függvény deklaráció
Private Declare Function InternetGetConnectedState _
Lib "wininet.dll" (ByRef dwflags As Long, _
ByVal dwReserved As Long) As Long

‘UDF a fenti API hívással és kiértékelésével
Function IsInternetConnected() As Boolean
Dim L As Long
Dim R As Long
R = InternetGetConnectedState(L, 0&)
If R = 0 Then
IsInternetConnected = False
Else
If R <= 4 Then
IsInternetConnected = True
Else
IsInternetConnected = False
End If
End If
End Function

‘Használati példa
Sub Test_Internet()
If IsInternetConnected() = True Then
MsgBox "Connected"
Else
MsgBox "No Internet Connection"
End If
End Sub

[/vb]

Tovább... →
Back to Top

Lapvédelem több munkalapra egyszerre

On 2013-01-10   /     /   7 hozzászólás

Az Excel egészen a 2013-as verzióig (de elképzelhető, hogy a további verziókra is igaz lesz) nem képes arra, hogy több munkalapot kijelölve egyszerre bekapcsolhassuk a lapvédelmet ugyanazzal a jelszóval.

Tehát több, sok munkalap esetén hosszas kattintgatás előtt állunk, ha ezt meg szeretnénk oldani, mivel egyesével kell bekapcsolni a lapvédelmet.

A jó hír az, hogy van megoldás a problémára, a rossz hír pedig az, hogy csak VBA kóddal oldható meg.

Igaz, a kódot nem kell a védendő dokumentumba tenni, elég, ha nyitva van egy olyan munkafüzet, amiben benne van az alábbi makró, és azt lefuttatjuk. Akár be is tehetjük a PERSONAL makró munkafüzetbe, és akkor bármikor rendelkezésünkre áll.

Nyissuk meg a PERSONAL makró füzetet. Ha nincs ilyen vagy nem tudjuk miről van szó,
akkor nézzük meg itt. A PERSONAL (Egyéni) makró füzet (link)

  1. Illesszünk be egy új modult, vagy ugorjunk egy létező modul végére
  2. Másoljuk be a lenti kódot
  3. Mentsük el
  4. Nyissuk meg a több védendő lapot tartalmazó dokumentumot
  5. ALT+F8 (Makró futtatás)
  6. Adjuk meg, hogy a PERSONAL makrófüzetben lévő makrókat mutassa
  7. Válasszuk ki a „Protect_Unprotect” makrót, és futtassuk

Lapvédelem ki/bekapcsolása több munkalapra makró

[vb]

Sub Protect_Unprotect( )

Dim wSheet As Worksheet
Dim pw as string

pw = inputbox("Add meg a jelszót:")
For Each wSheet In Worksheets

With wSheet

If .ProtectContents = True Then

.Unprotect Password:=pw

Else

.Protect Password:=pw

End If

End With

Next wSheet

End Sub

[/vb]

A makró bekéri a jelszót, majd végiglép minden munkalapon, és ha nincs bekapcsolva a védelem, akkor bekapcsolja, ha be volt kapcsolva, akkor pedig feloldja a védelmet.

A kódot bátran próbáljuk  módosítani, hogy pl. csak bekapcsolja vagy csak kikapcsolja. Ezeket akár külön makróba is lehet tenni.

Vigyázat! A jelszót ne felejtsük el, mert ez a kód nem tárolja le sehová!

Tovább... →
Back to Top

Az Excel nemzetközi beállításainak lekérdezése

On 2013-01-04   /     /   3 hozzászólás

Gyakran előfordulhat, főleg Multi környezetben, hogy az Excel munkafüzeteket, alkalmazásokat több nyelvi környezetre is fel kell készíteni.

Az is előfordulhat, hogy az adott Excel nyelvi verzióban az érvényes beállításokat meg kell néznünk, hogy a képleteink vagy egyéb műveleteinket az Excel helyesen értelmezze.

Leggyakoribb nemzetközi eltérések szoktak lenni

  • Dátum, Idő, Pénznem karakterek és elválasztójelek
  • Lista szeparátor jel (A függvényargumentumok elválasztásához használjuk)
  • Ezres és tizedes elválasztó karakter

Összesen 45 ilyen nemzetközi beállítást tartalmaz az Excel. Készítettem egy táblázatot, amiben egy makró lefuttatásával Te is megnézheted, hogy a saját Excel-edben mik ezek a beállítások.

Töltsd le a fájlt, engedélyezd a makrókat, és nyomd meg a tetején a „Get values” gombot

Az Excel_nemzetközi_beállításai munkafüzet letöltése

proba

  • Excel nemzetközi beállításai Video

Tovább... →
Back to Top

Az összes PIVOT tábla frissítése a munkafüzetben

On 2012-12-29   /     /   Leave a comment

Ha egy munkafüzetben több kimutatást, azaz PIVOT táblát hozunk létre, akkor az adatok változásakor azokat kézzel kellene egyenként frissíteni. Ez hibalehetőséget teremt, és időt vesz el. Az alábbi VBA eljárással a munkafüzet összes kimutatását egyszerre frissíthetjük. A kódot nem is kell az adott munkafüzetbe tenni, mivel az ActiveWorkbook névvel dolgozik, akár a PERSONAL, vagy egyéb makrófüzetbe is tehető.

A kód végiglépked a PivotCaches gyűjteményen, és mindegyiket frissíti.

[vb]
Sub Refresh_all_Pivot()

Dim pt As PivotCache

For Each pt In ActiveWorkbook.PivotCaches
pt.Refresh
Next pt

End Sub

[/vb]

Tovább... →
Back to Top

Érték beillesztése az összes képletre hipergyorsan VBA segítségével

On 2012-12-18   /     /   Leave a comment

Ha csak egy darab összefüggő tartományra szeretnénk a képleteket értékükkel helyettesíteni, akkor azt viszonylag gyorsan meg tudjuk csinálni az EXCEL által adott speciális beillesztési lehetőséggel.

De mi van akkor, ha egy munkalapon az összes képletet ki szeretnénk cserélni értékre? Hagyományos módszerrel egyenként kell megcsinálni. De ha az alábbi kis kódot elraktározzuk magunknak pl. a PERSONAL makrófüzetbe, akkor bármelyik dokumentumunknál nagyon gyorsan megtehetjük a cserét.

Háttér

Az Excel a speciális kijelölések segítségével megadja a módját, hogy az összes, képletet tartalmazó cellát egy lépésben kijelöljük. Ezt az Excel felületen így kell:

  1. F5
  2. Special… (Irányított…) gomb
  3. Képletek

Ugyanezt VBA kóddal

Selection.SpecialCells(xlCellTypeFormulas, 23).Select

Képletek cseréje nem összefüggő tartományoknál

Nagyon valószínű, hogy nem csak egy tartományban lesznek képletek. Így több, nem összefüggő tartományt jelöl ki az Excel. Ezekre viszont NEM LEHET értéket beilleszteni az Excel felületről.

Megoldás

A nem összefüggő tartományokat egy VBA ciklusban bejárva, egyesével cseréljük ki bennük a képleteket értékre.

A nem összefüggő tartományok bejárásának leírását lásd itt.

[vb]

‘Képletek értékké alakítása nem összefüggő tartományokban is

Sub Formula2Value()
Dim r As Range

‘Képleteket tartalmazó cellák(tartományok) kijelölése
Selection.SpecialCells(xlCellTypeFormulas, 23).Select

‘A keletkezett tartományok (areas) bejárása
For Each r In Selection.Areas
r.Select
Selection.Formula = Selection.Value
Next

End Sub

[/vb]

Tovább... →
Back to Top

Nem összefüggő kijelölések bejárása VBA-ban

On 2012-12-18   /     /   Leave a comment

Az Excel használata közben többféleképpen keletkezhetnek nem összefüggő tartományok:

  • Mi jelöljük ki a Ctrl billentyűvel
  • Szűréssel keletkeznek
  • Speciális kijelölést használunk, pl.: Az összes képletet tartalmazó cella kijelölése
  • VBA kódból jelölünk ki nem összefüggően (lásd itt)
  • Egyéb ?

Akármilyen módon is keletkeztek a tartományok, a kezelésük már nem annyira egyszerű, sőt, bizonyos műveletek nem megengedettek ilyen kijelöléskor. Például nem lehet beilleszteni értékeket a vágólapról.

Elméleti háttér

Ha több tartományt jelölünk ki, akkor a kijelölésen (Selection) belül létrejön egy új kollekció „AREAS” (területek) néven. Próbáljuk ki, hogy kijelölünk néhány nem összefüggő tartományt CTRL lenyomásával, majd az Immedate ablakba beirjuk:

?Selection.areas.count

Válaszként megkapjuk, hogy hány tartományt jelöltünk ki. A képen látható, hogy három tartomány kijelölése után milyen választ kaptunk.

A gyűjtemény egyes területeire a SELECTION.AREAS(n) módon lehet hivatkozni.

A területek (areas) bejárása

Mivel ez is egy kollekció, a bejárásához legegyszerűbben egy for each ciklust lehet használni. A példában a kijelölt területek celláinak a számát írjuk ki egy üzenőboxba. A kód futtatása előtt legyen kijelölve legalább kettő, de jobb, ha több tartomány.

[vb]

Sub areas()
Dim r As Range

‘A keletkezett tartományok (areas) bejárása
For Each r In Selection.Areas
r.Select
msgbox selection.cells.count
Next

End Sub

[/vb]

Tovább... →
Back to Top

Nem összefüggő tartományok kijelölése VBA-ban

On 2012-12-18   /     /   Leave a comment

Ha szükség lenne nem összefüggő tartományok kijelölésére, akkor ahhoz a union() metódust tudjuk használni. Általános szintaktikája:

dim eredmeny as range

set eredmeny = UNION(range 1, range 2, …range n)

eredmeny.select

Az egyes tartományokat külön-külön is megadhatjuk az egyesítéshez, de ha ezt előre nem tudjuk, akkor menet közben is felépíthetjük a dolgot úgy, hogy mindig az előző union-hoz adjuk hozzá az aktuális tartományt.

Második és negyedik oszlop kijelölése az aktív tartományban

[vb]

Sub union_1()
Dim oszlop1 As Range
Dim oszlop2 As Range

Set oszlop1 = Range(ActiveCell.CurrentRegion.Cells(2), _
ActiveCell.CurrentRegion.Cells(2).End(xlDown))

Set oszlop2 = Range(ActiveCell.CurrentRegion.Cells(4), _
ActiveCell.CurrentRegion.Cells(4).End(xlDown))

union(oszlop1, oszlop2).Select
End Sub

[/vb]

Az eredmény

Az aktív cellától kezdve minden második sor kijelölése a végéig

[vb]

Sub union_2()
Dim akt_sor As Range
Dim osszes_sor As Range
Dim uo As Integer
Dim us As Long
Dim vege As Boolean

‘Sor vége, azaz utolsó oszlop
uo = ActiveCell.CurrentRegion.SpecialCells(xlCellTypeLastCell).Column
‘Utolsó sor
us = ActiveCell.CurrentRegion.SpecialCells(xlCellTypeLastCell).Row

‘Első Aktualis sor kijelolése
Set akt_sor = Range(ActiveCell, Cells(ActiveCell.Row, uo))

‘Kezdetben az összes kijelölés egyenlő az első aktuális sorral
Set osszes_sor = akt_sor

vege = False
‘Ciklus, amíg a tartomány végéig nem érünk
While vege <> True

‘Két sorral lejjebb lépünk, és kijelöljük a következő sort
ActiveCell.Offset(2, 0).Select

Set akt_sor = Range(ActiveCell, Cells(ActiveCell.Row, uo))

‘Ha még nem értünk a végére
If ActiveCell.Row <= us Then
‘Összeadjuk az eddigi kijelöléseket és az aktuális sort
Set osszes_sor = union(osszes_sor, akt_sor)
Else
vege = True
End If
Wend

osszes_sor.Select

End Sub

[/vb]

Eredmény

Továbbiak

Az így kijelölt tartományokkal SELECTION néven dolgozhatunk tovább, megformázhatjuk, képlettel tölthetjük fel, törölhetjük, stb…

Tovább... →
Back to Top

A Windows felhasználó nevének lekérdezése

On 2012-12-18   /     /   Leave a comment

Ha szükségünk lenne arra a névre, amivel az aktuális felhasználó belépett a Windows-ba, akkor azt lekérdezhetjük a Windows környezeti változóiból. Ez a név nem feltétlenül egyezik meg az Excel-ben beállított felhasználónévvel.

[vb]

Sub winuser()

Dim user as string

user= Environ ("username")

msgbox user

End sub

[/vb]

Az összes környezeti változó listázása az ‘A’ oszlopba

[vb]

Sub environ_variables()

Dim i As Integer

Dim env_string As String

‘Kis takarítás, ha előzőleg már használták és elmentettés

Range("A:A").ClearContents

Cells(1, 1).Select

‘Az elsőtől kezdve az összes környezeti változó listázása az A oszlopba

i = 1

Do

env_string = Environ(i)

ActiveCell = env_string

ActiveCell.Offset(1, 0).Select

i = i + 1

Loop Until env_string = ""

End Sub

[/vb]

Eredmény

Látszik, hogy a listában változónév = érték formában kapjuk az adatokat. Egy konkrét változót a fenti „usermame” példához hasonlóan az Environ („valtozónév”) paranccsal kérdezhetünk le. A környezeti változók nevei nem érzékenyek a kis/nagy betűkre.

Tovább... →
Back to Top
« Előző oldal