VBA 를 막 시작했다면 초보자를 위한 VBA 가이드(VBA guide for beginners) 공부를 시작하고 싶을 것 입니다. 그러나 노련한 VBA 전문가이고 (VBA)Excel 에서 (Excel)VBA 로 수행할 수 있는 고급 작업을 찾고 있다면 계속 읽으십시오.
Excel 에서 (Excel)VBA 코딩 을 사용하는 기능 은 자동화의 세계를 열어줍니다. Excel 에서 계산을 자동화 하고 버튼을 누르고 이메일을 보낼 수도 있습니다. VBA 로 일상 업무를 자동화할 수 있는 가능성은 생각보다 많습니다.
Microsoft Excel용 고급 VBA 가이드(Advanced VBA Guide For Microsoft Excel)
Excel 에서 (Excel)VBA 코드 를 작성하는 주요 목표는 스프레드시트에서 정보를 추출하고 이에 대해 다양한 계산을 수행한 다음 결과를 스프레드시트에 다시 쓸 수 있도록 하는 것입니다.
다음 은 Excel 에서 (Excel)VBA 의 가장 일반적인 용도입니다 .
- 데이터 가져오기(Import) 및 계산 수행
- (Calculate)사용자가 버튼을 누르면 결과 계산
- (Email)누군가에게 계산 결과를 이메일 로 보내기
이 세 가지 예를 통해 다양한 고급 Excel VBA 코드를 작성할 수 있습니다.
데이터 가져오기 및 계산 수행(Importing Data and Performing Calculations)
사람들이 Excel 을 사용하는 가장 일반적인 작업 중 하나는 (Excel)Excel 외부에 있는 데이터에 대한 계산을 수행하는 것입니다 . VBA 를 사용하지 않는 경우 데이터를 수동으로 가져와서 계산을 실행하고 해당 값을 다른 시트나 보고서로 출력해야 함을 의미합니다.
VBA 를 사용 하면 전체 프로세스를 자동화할 수 있습니다. 예를 들어 매주 월요일(Monday) 컴퓨터의 디렉토리에 새 CSV 파일을 다운로드한 경우 (CSV)화요일(Tuesday) 아침 에 스프레드시트를 처음 열 때 VBA 코드가 실행되도록 구성할 수 있습니다 .
다음 가져오기 코드가 실행되어 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 편집 도구를 열고 Sheet1 개체를 선택합니다. 개체 및 메서드 드롭다운 상자에서 워크시트(Worksheet) 및 활성화(Activate) 를 선택합니다 . 스프레드시트를 열 때마다 코드가 실행됩니다.
그러면 Sub Worksheet_Activate() 함수가 생성됩니다. 위의 코드를 해당 함수에 붙여넣습니다.
이렇게 하면 활성 워크시트가 Sheet1 으로 설정되고 시트가 지워지고 (Sheet1)strFile 변수로 정의한 파일 경로를 사용하여 파일에 연결 한 다음 With 루프가 파일의 모든 줄을 순환하고 A1 셀에서 시작하는 시트에 데이터를 배치합니다. .
이 코드를 실행하면 CSV 파일 데이터가 Sheet1 의 빈 스프레드시트로 가져온 것을 볼 수 있습니다 .
가져오기는 첫 번째 단계일 뿐입니다. 다음으로 계산 결과를 포함할 열에 대한 새 머리글을 생성하려고 합니다. 이 예에서 각 품목의 판매에 대해 지불하는 5% 세금을 계산하려고 한다고 가정해 보겠습니다.
코드에서 수행해야 하는 작업의 순서는 다음과 같습니다.
- 세금(taxes) 이라는 새 결과 열을 만듭니다 .
- 판매 단위(units sold) 열 을 반복 하고 판매세를 계산합니다.
- 계산 결과를 시트의 해당 행에 씁니다.
다음 코드는 이러한 모든 단계를 수행합니다.
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(VBA) 코드를 이전 코드 아래에 붙여넣고 스크립트를 실행합니다. 결과가 E 열에 표시되는 것을 볼 수 있습니다.
이제 Excel(Excel) 워크시트 를 열 때마다 자동으로 실행되어 CSV 파일 에서 최신 데이터 복사본을 가져옵니다 . 그런 다음 계산을 수행하고 결과를 시트에 기록합니다. 더 이상 수동으로 아무것도 할 필요가 없습니다!
버튼 누름에서 결과 계산(Calculate Results From Button Press)
시트가 열릴 때 자동으로 실행되는 것보다 계산이 실행되는 시기를 더 직접적으로 제어하려면 대신 제어 버튼을 사용할 수 있습니다.
제어(Control) 버튼은 사용되는 계산을 제어하려는 경우에 유용합니다. 예를 들어 위와 같은 경우에 한 지역에 5% 세율을 사용하고 다른 지역에 7% 세율을 사용하려면 어떻게 합니까?
동일한 CSV 가져오기 코드가 자동으로 실행되도록 할 수 있지만 적절한 버튼을 누를 때 세금 계산 코드가 실행되도록 둡니다.
위와 동일한 스프레드시트를 사용하여 개발(Developer) 도구 탭을 선택 하고 리본 의 컨트롤 그룹에서 (Controls)삽입 을 선택합니다. (Insert)드롭다운 메뉴 에서 푸시 버튼 (push button)ActiveX 컨트롤(ActiveX Control) 을 선택합니다.
데이터가 갈 곳에서 떨어진 시트 부분에 푸시 버튼을 그립니다.
푸시 버튼을 마우스 오른쪽 버튼으로 클릭하고 속성 을 선택 합니다(Properties) . 속성(Properties) 창 에서 캡션을 사용자에게 표시할 내용으로 변경합니다. 이 경우에는 Calculate 5% Tax 이 될 수 있습니다 .
푸시 버튼 자체에 이 텍스트가 반영된 것을 볼 수 있습니다. 속성(properties) 창을 닫고 푸시 버튼 자체를 두 번 클릭합니다. 이렇게 하면 코드 편집기 창이 열리고 사용자가 푸시 버튼을 눌렀을 때 실행될 함수 안에 커서가 있습니다.
위 섹션의 세금 계산 코드를 이 함수에 붙여넣고 세율 승수를 0.05로 유지합니다. 활성 시트를 정의하려면 다음 두 줄을 포함해야 합니다.
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
이제 프로세스를 다시 반복하여 두 번째 푸시 버튼을 만듭니다. Calculate 7% Tax 캡션을 만듭니다 .
해당 버튼을 두 번 클릭(Double-click) 하고 동일한 코드를 붙여넣되 세금 승수는 0.07로 설정합니다.
이제 어떤 버튼을 누르느냐에 따라 세금 열이 그에 따라 계산됩니다.
완료되면 시트에 두 개의 푸시 버튼이 있습니다. 그들 각각은 다른 세금 계산을 시작하고 다른 결과를 결과 열에 기록합니다.
이것을 문자로 보내려면 개발자(Developer) 메뉴를 선택 하고 리본 의 컨트롤 그룹에서 (Controls)디자인 모드 를 선택하여 디자인 (Design Mode)모드(Design Mode) 를 비활성화 합니다. 그러면 푸시 버튼이 활성화됩니다.
각 푸시 버튼을 선택하여 "세금" 결과 열이 어떻게 변경되는지 확인하십시오.
누군가에게 계산 결과를 이메일로 보내기(Email Calculation Results to Someone)
스프레드시트의 결과를 이메일을 통해 다른 사람에게 보내려면 어떻게 해야 합니까?
위의 동일한 절차를 사용하여 이메일 시트 라는 다른 버튼 을 보스에게 생성할 수 있습니다. (Email Sheet to Boss)이 버튼의 코드에는 Excel CDO 개체를 사용하여 SMTP 이메일 설정을 구성하고 사용자가 읽을 수 있는 형식으로 결과를 이메일로 보내는 작업이 포함됩니다.
이 기능을 활성화하려면 도구 및 참조(Tools and References) 를 선택해야 합니다 . Windows 2000 라이브러리용 Microsoft CDO(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 설정을 지정합니다. (SMTP)Gmail 을 사용하는 경우 일반적으로 Gmail 이메일 주소, Gmail 비밀번호 및 Gmail SMTP 서버(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
[email protected] 및 비밀번호를 자신의 계정 세부 정보로 [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(Excel VBA) 를 사용하여 실제로 자동화할 수 있는 항목이 많이 있습니다 . 이 기사에서 배운 코드 조각을 가지고 놀고 고유한 VBA 자동화를 만들어 보십시오.
An Advanced VBA Guide For MS Excel
If you are jυst getting started with VBA, then you’ll want to start out 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:
- Create new results column called taxes.
- Loop through the units sold column and calculate the sales tax.
- 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.
