VBA – Visual Basic for Applications, Excel – početak 1
Hoću da uvučem podatke iz tabele u Word-u u Excel tabelu, ali na tačno određene lokacije.
Za to mi treba da naučim da koristim VBA i da napišem makro u Excel-u (NE u Word-u!).
Napomena : Excel dokumenti koji u sebi sadrže markoe imaju ekstenziju : *.xlsm
PAZITI : za sve koristiti English tastaturu, jer neki simboli nisu isti (npr dvostruku navodnici), i to onda daje gresku “Out of range”
Početak učenja
Declaring the Sub
At the beginning of the module, we have “Sub StoreSales()”. This defines a new sub called StoreSales.
You can also define functions—the difference is that functions can return values, and subs can’t
At the end of the module, we have “End Sub,”.
Declaring Variables
Dim is VBA’s command for declaring a variable.
So “Dim Sum1” creates a new variable called “Sum1.” However, we need to tell Excel what kind of variable this is. We need to choose a data type, some of which are :
Boolean – True or False
Char – one character
Currency – valuta
Date – datum
Integer – od -2,147,483,648 do 2,147,483,647
Long – dugačak integer, od -9,223,372,036,854,775,808 do 9,223,372,036,854,775,807
Object
String – 0 to approximately 2 billion Unicode characters
Variant – Variant variable can hold any type of value
Tako da deklarisanje promenljive izgleda ovako : Dim Sum1 As Currency
The statement “Dim Sum1 As Currency” tells Excel to create a new Currency variable called Sum1. Every variable that you declare needs to have an “As” statement to tell Excel its type.
Reference unutar Excel dokumenta
Here, the range function is referencing cell A5 : Worksheets(“Sheet1”).Range(“A5”)
Here, the range function is referencing all cells between A1 through E20 :
Worksheets(“Sheet1”).Range(“A1:E20”)
Petlje/Loops
For Each Cell in Range(“C2:C51”)
[a bunch of stuff]
Next Cell
“For each cell” znači da se nešto radi sa SVAKOM ćelijom koja je navedena.
Kopiranje ćelija iz jednog u drugi Excel worksheet
Sheets(“Sheet1”).Range(“A1:B10”).Copy Destination:=Sheets(“Sheet2”).Range(“E1”)
Kako promenljivoj dajemo vrednost koju unosi korisnik :
myValue = InputBox(“Give me some input”)
Kako prikazati promenjivu u pop-up box-u
MsgBox “Vrednost za staro je” + mojapromenljiva
ILI, ako ovo gore bas nece da radi :
MsgBox “Kolona je ” & kolona, vbInformation
Ako hoćemo da u message box-u prikažemo DVE promenljive sa razmakom između njih :
MsgBox “Kolona i red su ” & kolona & ” ” & original
Loop through rows and columns
Ako hoćemo da prođemo (loop) kroz niz kolona i redova, to se radi sa dvostrukom petljom, ali PAZITI, ako se ćelija adresira sa dve promenljive PRVO ide red, ONDA ide kolona :
For kolona = 4 To 35
For original = 3 To 25
‘originalna boja iz prve tabele
bojacelije = Worksheets(“ObradaSmene”).Cells(original, kolona).Interior.Color
‘kopiranje originalnog farbanja na tabelu prekovremenih sati
kopijaprekovremeni = original + 32
Worksheets(“ObradaSmene”).Cells(kopijaprekovremeni, kolona).Interior.Color = bojacelije
Next original
Next kolona
Kako kopirati kolone u redove
Sheets(“deo1”).Range(“C2:C7”).Copy
Sheets(“deo2”).Range(“C10”).PasteSpecial Transpose:=True
The default value of the Transpose parameter is False. Therefore, if you omit it, Excel doesn’t transpose the rows and columns of the copied range.
Kako uvući promenljivu u definiciju Excel ćelije
Sheets(“deo1”).Range(“C2:C7”).Copy
Sheets(“deo2”).Range(“C” & (mojapromenljiva)).PasteSpecial Transpose:=True
Kako promenljivu upisati u Excel celiju
Range(“M17”) = mojapromenljiva
Zamena posebnih karaktera u ćeliji Excel-a
Link8 ka spisku svih ASCII karaktera.
tacka = Chr(46)
dvotacka = Chr(58)
Worksheets(“deo2”).Range(“C” & (jafinred)).Replace What:=tacka, Replacement:=dvotacka
Pošto su . i : specijalni karakteri moraju se definisati kroz svoje ASCII vrednosti, a ne mogu se pozvati direktno za zamenu.
Kako zameniti ćirilične karaktere u skupni, npr : ц-2,3
Link6 – spisak Unicode karaktera, tu je ц=U+0446
I evo kako :
Dim cirilica1 As String
cirilica1 = ChrW(&H446) & “-2,3″ & ” ”
Worksheets(“deo2”).Range(“D9″).Replace What:=cirilica1, Replacement:=””
I ovime smo set karaktera : ц-2,3 (sa blankom na kraju) zamenili ni sa cime 🙂
Kako dodati “space” pre i posle promenljive
(treba mi jer mi je u ćeliji ovakav sadržaj : 0:03 – 0:04
minus = Chr(150)
minus1 = ” ” & minus & ” ”
Ako se “replace” koristi da se “.” zameni sa ničim “”, javlja se problem ako su u pitanju brojevi koji se završavaju sa nulom, pa od 2550 dobijamo 255. lek za to je da se Excel celije U koje se kopiraju podaci proglase za text (u Excel-u).
Problemi u startnoj kopiranoj Excel ćeliji
1) Ćelija sadrži dva broja koja mi oba trebaju da se kopiraju u odvojene ćelije, i između kojih je znak / (npr : 123 / 456)
Dim pu As String
pu = Sheets(“deo1”).Range(“C8”)
Dim Result() As String
Result = Split(pu, “/”)
Sheets(“deo2”).Range(“I” & (mojapromenljiva)) = Result(0)
Sheets(“deo2”).Range(“J” & (mojapromenljiva)) = Result(1)
Problematičnu ćeliju (sadržaj tipa : 123 / 456) sam proglasila za string “pu”, i koristila VBA funkciju “split”.
Dobar link3.
2) Ćelija sadrži dva broja koja mi oba trebaju da se kopiraju u odvojene ćelije, i između kojih je znak minus (npr : 123 – 456)
E ono gore NE RADI kada je u pitanju znak “-“!!!!!
Spisak ASCII karaktera : link4
Samo ako se minus definise kao ASCII karakter, onda ga funkcija replace prepoznaje :
minus = Chr(150)
staro = Range(“M16”)
MsgBox “Vrednost za staro je ” + staro
novo = Replace(staro, minus, “aaa”)
MsgBox “Vrednost za novo je ” + novo
Ali šta ko vaš minus nije KLASIČAN minus, nego nešto što samo liči na njega (u ASCII tabeli ima jedno 6 karaktera koji izgledaju kao običan minus, ali nisu)?
Excel ima funkciju CODE koja prikazuje ASCII broj za dati karakter.
Dakle ako se “minus” nalazi u ćeliji D11, u ćeliju D12 upišete : =CODE(D11) i dobićete ASCII broj za vaš “minus” znak!
Ako znak ima ASCII prikaz funkcija CODE ce dati njega, a ako nema, dace UNICODE.
Definisanje vremena radi matematičkih operacija
Treba da kopiram vrednosti vremena u hh:mm formatu, ali uporno mi upada neki datum u 1900god, i takođe se vreme pretvara u format hh:mm:ss iz nepoznatih razloga.
REh ovo je rešeno tako što sam koristila kao pšromenljivu ne samo -, nego – sa blankom pre i posle (videti gore kako se definiše).
Pokušaću da zato odamh posle kopiranja sadržaja iz Word-a u Excel ćelije definišem kao “text” što bi trebalo da ukloni problem.
Odličan link5 koji navodi sve formate.
Ono što meni treba je “text” format :
Range(“A1”).NumberFormat = “@”
Ako hoćemo ćeliju da definišemo kao “Custom hh:mm” :
Range(“A1”).NumberFormat = “hh:mm”
Kako oduzeti dva vremena jedno od drugog
Dim Total as Date
Dim Timein as Date
Dim Timeout as Date
Total = TimeValue(Timeout) – TimeValue(Timein)
I da sva vremena se definisu kao datum, bez obzira sto su u stvari vremena.
Ako je potrebno Excel ćeliju definisati kao broj BEZ decimalnih mesta : Worksheets(“deo1”).Range(“C20”).NumberFormat = “0”
Kako kopirati i boju ćelije i mustru ćelije, na primer :
To se radi ovako :
Dim bojacelije As Long
Dim mustra As Long
Dim original As Integer
Dim kopija As Integer
Dim kolona As Integer
……
‘originalna boja iz prve tabele
bojacelije = Worksheets(“test1”).Cells(original, kolona).Interior.Color
mustra = Worksheets(“test1”).Cells(original, kolona).Interior.Pattern
……
‘sad se originalni podaci kopiraju u drugu tabelu
Worksheets(“test2”).Cells(kopija, kolona).Interior.Color = bojacelije
Worksheets(“test2”).Cells(kopija, kolona).Interior.Pattern = mustra
