Ако тек почињете да користите ВБА^(VBA) , онда ћете желети да почнете да проучавате наш ВБА водич за почетнике^{(VBA guide for beginners)} . Али ако сте искусни ВБА^(VBA) стручњак и тражите напредније ствари које можете да урадите са ВБА^(VBA) у Екцел^(Excel) -у , наставите да читате.

Могућност коришћења ВБА^(VBA) кодирања у Екцел^(Excel) -у отвара читав свет аутоматизације. Можете да аутоматизујете прорачуне у Екцел^(Excel) -у , дугмад, па чак и да шаљете е-пошту. Постоји више могућности да аутоматизујете свој свакодневни рад са ВБА^(VBA) него што мислите.

Напредни ВБА водич за Мицрософт Екцел^{(Advanced VBA Guide For Microsoft Excel)}

Главни циљ писања ВБА^(VBA) кода у Екцел^(Excel) -у је тако да можете извући информације из табеле, извршити разне прорачуне на њој, а затим резултате записати назад у табелу

Следеће су најчешће употребе ВБА^(VBA) у Екцел^(Excel) -у .

• Увезите^(Import) податке и извршите прорачуне
• Израчунајте^(Calculate) резултате када корисник притисне дугме
• Пошаљите^(Email) некоме резултате прорачуна е-поштом

Уз ова три примера, требало би да будете у могућности да напишете различите сопствене напредне Екцел ВБА коде^{(Excel VBA)} .

Увоз података и извођење прорачуна^{(Importing Data and Performing Calculations)}

Једна од најчешћих ствари за које људи користе Екцел^(Excel) је извођење прорачуна на подацима који постоје изван Екцел^(Excel) -а . Ако не користите ВБА^(VBA) , то значи да морате ручно да увезете податке, покренете прорачуне и изнесете те вредности на други лист или извештај.

Са ВБА^(VBA) , можете аутоматизовати цео процес. На пример, ако имате нову ЦСВ^(CSV) датотеку која се преузима у директоријум на рачунару сваког понедељка^(Monday) , можете да конфигуришете свој ВБА^(VBA) код да се покреће када први пут отворите табелу у уторак^(Tuesday) ујутро.

Следећи код за увоз ће се покренути и увести ЦСВ^(CSV) датотеку у вашу Екцел^(Excel) табелу.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")
Cells.ClearContents

strFile = “c:\temp\purchases.csv”

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With

Отворите Екцел ВБА^{(Excel VBA)} алат за уређивање и изаберите објекат Схеет1 . ^(Sheet1)Из падајућих оквира објеката и метода изаберите Радни лист^(Worksheet) и Активирај^(Activate) . Ово ће покренути код сваки пут када отворите табелу.

Ово ће креирати функцију Суб Ворксхеет_Ацтивате()^{(Sub Worksheet_Activate() )} . Налепите горњи код у ту функцију.

Ово поставља активни радни лист на Схеет1^(Sheet1) , брише лист, повезује се са датотеком користећи путању датотеке коју сте дефинисали променљивом стрФиле^(strFile) , а затим петља Витх^(With) пролази кроз сваки ред у датотеци и ставља податке у лист почевши од ћелије А1 .

Ако покренете овај код, видећете да су подаци из ЦСВ^(CSV) датотеке увезени у вашу празну табелу, у Схеет1^(Sheet1) .

Увоз је само први корак. Затим желите да креирате ново заглавље за колону које ће садржати ваше резултате прорачуна. У овом примеру, рецимо да желите да израчунате порез од 5% плаћеног на продају сваке ставке.

Редослед радњи које ваш код треба да предузме је:

1. Направите нову колону резултата која се зове порези^(taxes) .
2. Прођите кроз колону продатих јединица^{(units sold)} и израчунајте порез на промет.
3. Запишите резултате израчунавања у одговарајући ред на листу.

Следећи код ће извршити све ове кораке.

Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double

Set StartCell = Range("A1")

'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))

rowCounter = 2
Cells(1, 5) = "taxes"

For Each cell In rng
     fltTax = cell.Value * 0.05
     Cells(rowCounter, 5) = fltTax
     rowCounter = rowCounter + 1
Next cell

Овај код проналази последњи ред у вашем листу података, а затим поставља опсег ћелија (колона са продајним ценама) према првом и последњем реду података. Затим код петља кроз сваку од тих ћелија, обавља обрачун пореза и уписује резултате у вашу нову колону (колона 5).

Налепите горњи ВБА^(VBA) код испод претходног кода и покрените скрипту. Видећете да се резултати приказују у колони Е.

Сада, сваки пут када отворите свој Екцел^(Excel) радни лист, он ће се аутоматски угасити и добити најсвежију копију података из ЦСВ^(CSV) датотеке. Затим ће извршити прорачуне и записати резултате на листу. Не морате више ништа да радите ручно!

Израчунајте резултате притиском на дугме^{(Calculate Results From Button Press)}

Ако бисте радије имали директнију контролу над извршавањем прорачуна, уместо да се извршавају аутоматски када се лист отвори, можете користити контролно дугме.

Контролна^(Control) дугмад су корисна ако желите да контролишете који се прорачуни користе. На пример, у овом истом случају као горе, шта ако желите да користите пореску стопу од 5% за један регион, а пореску стопу од 7% за други?

Можете дозволити да се исти код за увоз ЦСВ^(CSV) -а покреће аутоматски, али оставите код за обрачун пореза да се покреће када притиснете одговарајуће дугме.

Користећи исту табелу као горе, изаберите картицу Девелопер^(Developer) и изаберите Инсерт^(Insert) из групе Цонтролс^(Controls) на траци. Изаберите дугме ^{(push button)} АцтивеКс Цонтрол^{(ActiveX Control)} из падајућег менија.

Нацртајте дугме на било који део листа даље од места где ће ићи подаци.

Кликните десним тастером миша на дугме и изаберите Својства^(Properties) . У прозору Својства^(Properties) промените натпис у оно што желите да прикажете кориснику. У овом случају то може бити Calculate 5% Tax .

Видећете да се овај текст одражава на самом дугмету. Затворите прозор са својствима^(properties) и двапут кликните на само дугме. Ово ће отворити прозор уређивача кода, а ваш курсор ће бити унутар функције која ће се покренути када корисник притисне дугме.

Налепите код за обрачун пореза из горњег одељка у ову функцију, задржавајући мултипликатор пореске стопе на 0,05. Не заборавите да укључите следећа 2 реда да бисте дефинисали активни лист.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Сада поновите поступак поново, стварајући друго дугме. Направите натпис Calculate 7% Tax .

Двапут кликните на^{(Double-click)} то дугме и налепите исти код, али направите порески мултипликатор 0,07.

Сада, у зависности које дугме притиснете, колона пореза ће бити израчуната у складу са тим.

Када завршите, имаћете оба дугмета на листу. Сваки од њих ће покренути другачији обрачун пореза и уписати различите резултате у колону резултата. 

Да бисте ово послали, изаберите мени за програмере^(Developer) и изаберите Режим дизајна^{(Design Mode)} из групе Контроле^(Controls) на траци да бисте онемогућили режим дизајна^{(Design Mode)} . Ово ће активирати дугмад. 

Покушајте да изаберете свако дугме да бисте видели како се мења колона резултата „порези“.

Пошаљите некоме резултате израчунавања е-поштом^{(Email Calculation Results to Someone)}

Шта ако желите да пошаљете резултате у табели некоме путем е-поште?

Можете креирати још једно дугме под називом Пошаљите листу шефу^{(Email Sheet to Boss)} користећи исту процедуру изнад. Код за ово дугме ће укључивати коришћење Екцел ЦДО^{(Excel CDO)} објекта за конфигурисање поставки СМТП^(SMTP) е-поште и слање резултата е-поштом у формату који може да прочита корисник.

Да бисте омогућили ову функцију, потребно је да изаберете Алатке и референце^{(Tools and References)} . Померите се надоле до библиотеке Мицрософт ЦДО за Виндовс 2000^{(Microsoft CDO for Windows 2000 Library)} , омогућите је и изаберите ОК^(OK) .

Постоје три главна одељка кода који морате да креирате да бисте послали е-пошту и уградили резултате табеле.

Први је подешавање променљивих за држање предмета, адреса примаоца и пошиљаоца^(From) и тела е-поште.

Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double

Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."

Наравно, тело треба да буде динамично у зависности од тога који су резултати у табели, тако да ћете овде морати да додате петљу која пролази кроз опсег, издваја податке и уписује ред по ред у тело.

Set StartCell = Range("A1")

'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))

rowCounter = 2
strBody = strBody & vbCrLf

For Each cell In rng
     strBody = strBody & vbCrLf
     strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _
     & " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "."
     rowCounter = rowCounter + 1
Next cell

Следећи одељак укључује подешавање СМТП^(SMTP) подешавања тако да можете да шаљете е-пошту преко свог СМТП^(SMTP) сервера. Ако користите Гмаил^(Gmail) , ово је обично ваша Гмаил^(Gmail) адреса е-поште, ваша Гмаил^(Gmail) лозинка и Гмаил СМТП^{(Gmail SMTP)} сервер (смтп.гмаил.цом).

Set CDO_Mail = CreateObject("CDO.Message") 
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields

With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With

With CDO_Mail
     Set .Configuration = CDO_Config
End With

Замените [email protected] и лозинку детаљима свог налога.

На крају, да бисте покренули слање е-поште, унесите следећи код.

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send

Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description

Напомена^(Note) : Ако видите грешку у транспорту када покушавате да покренете овај код, вероватно је зато што ваш Гоогле^(Google) налог блокира покретање „мање безбедних апликација“. Мораћете да посетите страницу са подешавањима мање безбедних апликација^{(less secure apps settings page)} и УКЉУЧИТЕ ову функцију.

Након што је то омогућено, ваша е-пошта ће бити послата. Овако то изгледа особи која прими вашу аутоматски генерисану е-пошту са резултатима.

Као што видите, постоји много тога што заправо можете аутоматизовати помоћу Екцел ВБА^{(Excel VBA)} . Покушајте да се поиграте са исечцима кода о којима сте сазнали у овом чланку и креирајте сопствене јединствене ВБА^(VBA) аутоматизације.

An Advanced VBA Guide For MS Excel

If you are just getting started with VBA, then you’ll wаnt to start оut studying our VBA guide for beginners. But if you’re a seasoned VBA expert and you’re looking for more advanced things you can do with VBA in Excel, then keep reading.

The ability to use VBA coding in Excel opens up a whole world of automation. You can automate calculations in Excel, pushbuttons, and even send email. There are more possibilities to automate your daily work with VBA than you may realize.

Advanced VBA Guide For Microsoft Excel

The main goal of writing VBA code in Excel is so that you can extract information from a spreadsheet, perform a variety of calculations on it, and then write the results back to the spreadsheet

The following are the most common uses of VBA in Excel.

• Import data and perform calculations
• Calculate results from a user pressing a button
• Email calculation results to someone

With these three examples, you should be able to write a variety of your own advanced Excel VBA code.

Importing Data and Performing Calculations

One of the most common things people use Excel for is performing calculations on data that exists outside of Excel. If you don’t use VBA, that means you have to manually import the data, run the calculations and output those values to another sheet or report.

With VBA, you can automate the entire process. For example, if you have a new CSV file downloaded into a directory on your computer every Monday, you can configure your VBA code to run when you first open your spreadsheet on Tuesday morning.

The following import code will run and import the CSV file into your Excel spreadsheet.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")
Cells.ClearContents

strFile = “c:\temp\purchases.csv”

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With

Open the Excel VBA editing tool and select the Sheet1 object. From the object and method dropdown boxes, choose Worksheet and Activate. This will run the code every time you open the spreadsheet.

This will create a Sub Worksheet_Activate() function. Paste the code above into that function.

This sets the active worksheet to Sheet1, clears the sheet, connects to the file using the file path you defined with the strFile variable, and then the With loop cycles through every line in the file and places the data into the sheet starting at cell A1.

If you run this code, you’ll see that the CSV file data is imported into your blank spreadsheet, in Sheet1.

Importing is only the first step. Next, you want to create a new header for the column that will contain your calculation results. In this example, let’s say you want to calculate the 5% taxes paid on the sale of each item.

The order of actions your code should take is:

1. Create new results column called taxes.
2. Loop through the units sold column and calculate the sales tax.
3. Write the calculate results to the appropriate row in the sheet.

The following code will accomplish all of these steps.

Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double

Set StartCell = Range("A1")

'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))

rowCounter = 2
Cells(1, 5) = "taxes"

For Each cell In rng
     fltTax = cell.Value * 0.05
     Cells(rowCounter, 5) = fltTax
     rowCounter = rowCounter + 1
Next cell

This code finds the last row in your sheet of data, and then sets the range of cells (the column with the sales prices) according to the first and last row of data. Then the code loops through each of those cells, performs the tax calculation and writes the results into your new column (column 5).

Paste the above VBA code below the previous code, and run the script. You will see the results show up in column E.

Now, every time you open your Excel worksheet, it’ll automatically go out and get the freshest copy of data from the CSV file. Then, it will perform the calculations and write the results to the sheet. You don’t have to do anything manually anymore!

Calculate Results From Button Press

If you’d rather have more direct control over when calculations run, rather than running automatically when the sheet opens, you can use a control button instead.

Control buttons are useful if you want to control which calculations are used. For example, in this same case as above, what if you want to use a 5% tax rate for one region, and a 7% tax rate for another?

You could allow the same CSV import code to run automatically, but leave the tax calculation code to run when you press the appropriate button.

Using the same spreadsheet as above, select the Developer tab, and select Insert from the Controls group in the ribbon. Select the push button ActiveX Control from the dropdown menu.

Draw the pushbutton onto any part of the sheet away from where any data will go.

Right-click the push button, and select Properties. In the Properties window, change the Caption to what you’d like to display to the user. In this case it might be Calculate 5% Tax.

You’ll see this text reflected on the push button itself. Close the properties window, and double-click the pushbutton itself. This will open the code editor window, and your cursor will be inside the function that will run when the user presses the pushbutton.

Paste the tax calculation code from the section above into this function, keeping the tax rate multiplier at 0.05. Remember to include the following 2 lines to define the active sheet.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Now, repeat the process again, creating a second push button. Make the caption Calculate 7% Tax.

Double-click that button and paste the same code, but make the tax multiplier 0.07.

Now, depending which button you press, the taxes column will be calculated accordingly.

Once you’re done, you’ll have both push buttons on your sheet. Each of them will initiate a different tax calculation and will write different results into the result column. 

To text this, select the Developer menu, and select Design Mode form the Controls group in the ribbon to disable Design Mode. This will activate the push buttons. 

Try selecting each push button to see how the “taxes” result column changes.

Email Calculation Results to Someone

What if you want to send the results on the spreadsheet to someone via email?

You could create another button called Email Sheet to Boss using the same procedure above. The code for this button will involve using the Excel CDO object to configure SMTP email settings, and emailing the results in a user-readable format.

To enable this feature, you need to select Tools and References. Scroll down to Microsoft CDO for Windows 2000 Library, enable it, and select OK.

There are three main sections to the code you need to create to send out an email and embed spreadsheet results.

The first is setting up variables to hold the subject, To and From addresses, and the email body.

Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double

Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."

Of course, the body needs to be dynamic depending on what results are in the sheet, so here you’ll need to add a loop that goes through the range, extracts the data, and writes a line at a time to the body.

Set StartCell = Range("A1")

'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))

rowCounter = 2
strBody = strBody & vbCrLf

For Each cell In rng
     strBody = strBody & vbCrLf
     strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _
     & " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "."
     rowCounter = rowCounter + 1
Next cell

The next section involves setting up the SMTP settings so that you can send email through your SMTP server. If you use Gmail, this is typically your Gmail email address, your Gmail password, and the Gmail SMTP server (smtp.gmail.com).

Set CDO_Mail = CreateObject("CDO.Message") 
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields

With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With

With CDO_Mail
     Set .Configuration = CDO_Config
End With

Replace [email protected] and password with your own account details.

Finally, to initiate the email send, insert the following code.

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send

Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description

Note: If you see a transport error when trying to run this code, it’s likely because your Google account is blocking “less secure apps” from running. You’ll need to visit the less secure apps settings page and turn this feature ON.

After that’s enabled, your email will be sent. This is what it looks like to the person who receives your automatically generated results email.

As you can see there is a lot you can actually automate with Excel VBA. Try playing around with the code snippets you’ve learned about in this article and create your own unique VBA automations.

Related posts

• Најбољи ВБА водич (за почетнике) који ће вам икада требати

• Како направити ВБА макро или скрипту у Екцелу

• Водич за све екстензије Екцел датотека и шта оне значе

• Како избрисати празне линије у Екцелу

• Поправите поруку о грешци МС Оффице „Не могу да проверим лиценцу“.

• Користите тастатуру да промените висину реда и ширину колоне у Екцел-у

• 4 начина да користите квачицу у Екцелу

• Како претраживати у Екцел-у

• Како уклонити дупле редове у Екцелу

• Брзо изговорите бројеве у Ворд-у и Екцел-у

• Користите називе динамичког опсега у Екцел-у за флексибилне падајуће листе

• Како да поправите #Н/А грешке у Екцел формулама као што је ВЛООКУП

• 3 начина да поделите ћелију у Екцел-у

• Групирајте редове и колоне у Екцел радном листу

• Повезивање Екцел-а са МиСКЛ-ом

• Како користити Фласх Филл Ин Екцел

• Како премештати колоне у Екцелу

• Основно сортирање података у једној и више колона у Екцел табелама

• Коришћење Екцел-овог алата за тражење циља за анализу шта ако

• Како спојити ћелије, колоне и редове у Екцел-у