اگر تازه با VBA شروع کرده اید ، می خواهید مطالعه راهنمای VBA برای مبتدیان ما را شروع کنید. اما اگر شما یک متخصص VBA فصلی هستید و به دنبال کارهای پیشرفته تری هستید که می توانید با VBA در Excel انجام دهید ، ادامه مطلب را بخوانید.
توانایی استفاده از برنامه نویسی VBA در اکسل در کل جهان باز می شود. اتوماسیون می توانید محاسبات را در اکسل ، کلیدهای فشار و حتی ارسال ایمیل بصورت خودکار انجام دهید. امکانات بیشتری برای خودکارسازی کار روزانه خود با VBA از آنچه ممکن است بدانید وجود دارد.
راهنمای پیشرفته VBA برای Microsoft Excel
هدف اصلی نوشتن کد VBA در اکسل به گونه ای است که می توانید اطلاعات را استخراج کنید از یک صفحه گسترده ، محاسبات مختلفی را روی آن انجام دهید ، و سپس نتایج را به صفحه گسترده بنویسید
موارد زیر رایج ترین کاربردهای VBA در Excel است.
با این سه مثال ، شما باید قادر به نوشتن انواع کد پیشرفته VB Excel خود هستید.
وارد کردن داده و انجام محاسبات
یکی از رایج ترین مواردی که افراد از Excel برای آن استفاده می کنند در حال انجام محاسبات بر روی داده هایی است که در خارج از اکسل وجود دارد. اگر از VBA استفاده نمی کنید ، این بدان معنی است که شما باید داده ها را به صورت دستی وارد کنید ، محاسبات را اجرا کنید و آن مقادیر را به یک برگه دیگر یا گزارش دهید. خروجی : [640x360]->
با VBA ، می توانید کل فرایند را خودکار کنید. به عنوان مثال ، اگر فایل CSV جدیدی را هر روز دوشنبه در یک دایرکتوری در رایانه خود بارگیری کرده اید ، می توانید هنگام باز کردن صفحه گسترده خود در صبح سه شنبه ، کد VBA خود را پیکربندی کنید.
کد واردات زیر پرونده CSV را در صفحه Excel خود اجرا کرده و وارد کنید.
Dim ws As Worksheet, strFile As StringSet 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 را انتخاب کنید. از جعبه کشویی شی و روش ، کاربرگو فعال سازیرا انتخاب کنید. این کار هر بار که صفحه گسترده را باز می کنید ، کد را اجرا می کند.
با این کار یک تابع Sub Worksheet_Activate ()ایجاد می شود. کد فوق را در آن عملکرد جایگذاری کنید.
این برگه فعال را به برگه 1تنظیم می کند ، ورق را پاک می کند ، با استفاده از مسیر پرونده ای که با متغیر strFileتعریف کرده اید ، به پرونده متصل می شوید و سپس با چرخه حلقه حلقهدر هر خط در پرونده و قرار دادن داده ها در برگه از سلول A1 شروع می شود.
اگر این کد را اجرا کنید ، می بینید که اطلاعات پرونده CSV به صفحه گسترده شما ، در برگ1وارد می شود
واردات تنها اولین قدم است . در مرحله بعد می خواهید یک ستون جدید برای ستون ایجاد کنید که شامل نتایج محاسبه شما باشد. در این مثال ، بیایید بگوییم که شما می خواهید 5٪ مالیات پرداخت شده برای فروش هر یک از کالاها را محاسبه کنید.
ترتیب اقداماتی که کد شما باید انجام دهد این است:
کد زیر تمام این مراحل را انجام می دهد.
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) می نویسد. نتایج نمایش داده شده در ستون E. را مشاهده خواهید کرد.
اکنون ، هر بار که صفحه کاربر اکسل خود را باز می کنید ، به طور خودکار بیرون می رود و تازه ترین نسخه از داده های پرونده CSV را دریافت می کنید. سپس محاسبات را انجام داده و نتایج را به ورق می نویسد. شما دیگر نیازی به انجام کار دستی ندارید!
محاسبه نتایج از دکمه را فشار دهید
اگر ترجیح می دهید هنگام اجرای محاسبات ، کنترل مستقیم بیشتری داشته باشید ، به جای اینکه به طور خودکار در هنگام باز شدن ورقه اجرا شود ، می توانید به جای آن از یک دکمه کنترل استفاده کنید. به عنوان مثال ، در همان مورد مشابه در بالا ، اگر می خواهید از یک نرخ مالیات 5٪ برای یک منطقه و نرخ مالیات 7٪ برای منطقه دیگر استفاده کنید ، چه می کنید؟
شما می توانید اجازه دهید همان کد واردات CSV مجاز باشد به طور خودکار اجرا کنید ، اما هنگامی که دکمه مناسب را فشار می دهید ، کد محاسبه مالیات را اجرا کنید.
با استفاده از همان صفحه گسترده مانند بالا ، برگه توسعه دهندهرا انتخاب کنید و وارد کردن را انتخاب کنید.از گروه کنترلدر روبان. دکمه فشارActiveX Control را از فهرست کشویی انتخاب کنید.
دکمه را روی هر بخشی از برگه به دور از جایی که هر اطلاعاتی می رود بکشید.
بر روی دکمه فشار راست کلیک کرده و خصوصیاترا انتخاب کنید. در پنجره Properties ، عنوان را به آنچه می خواهید برای کاربر نمایش دهید تغییر دهید. در این حالت ممکن است محاسبه 5٪ مالیاتباشد.
این متن را در خود دکمه فشار منعکس می کنید. پنجره ویژگی هارا ببندید ، و روی دکمه فشار خود کلیک کنید. با این کار پنجره ویرایشگر کد باز می شود و مکان نما شما در هنگام عملکرد کاربر با فشار دادن دکمه در داخل عملکردی قرار خواهد گرفت.
کد محاسبه مالیات را از بخش فوق در این عملکرد قرار دهید ، و ضرب نرخ مالیات را در 0.05 نگه دارید. به یاد داشته باشید که 2 خط زیر را برای تعریف برگه فعال درج کنید.
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
اکنون ، فرایند را دوباره تکرار کنید ، یک دکمه فشار دوم ایجاد کنید. عنوان را محاسبه کنید. مالیات 7٪ را محاسبه کنید.
روی آن دکمه دوبار کلیک کنید و همان کد را بچسبانید ، اما ضریب مالیات را 0.07 کنید.
اکنون بسته به کدام دکمه را فشار دهید ، ستون مالیات خواهد شد. بر این اساس محاسبه شود.
پس از اتمام کار ، هر دو دکمه فشار را روی برگه خود خواهید داشت. هر یک از آنها محاسبه مالیات متفاوت را آغاز می کنند و نتایج متفاوتی را در ستون نتیجه می نویسند.
برای متن سازی این گزینه ، منوی توسعه دهندهرا انتخاب کنید و طراحی حالترا انتخاب کنید و گروه کنترل را در نوار انتخاب کنید تا حالت طراحیرا غیرفعال کنید. قوی>این دکمه های فشار را فعال می کند.
هر دکمه فشار را انتخاب کنید تا ببینید که چگونه ستون نتیجه "مالیات" تغییر می کند.
نتایج محاسبه ایمیل به شخصی
چه چیزی اگر می خواهید نتایج را در صفحه گسترده به کسی از طریق ایمیل ارسال کنید؟
با استفاده از همان روش فوق می توانید دکمه دیگری به نام نامه الکترونیکی به رئیسایجاد کنید. کد این دکمه با استفاده از شیء Excel CDO برای پیکربندی تنظیمات ایمیل SMTP و ارسال ایمیل به نتایج با فرمت قابل خواندن کاربر شامل می شود.
برای فعال کردن این ویژگی ، باید ابزارها و منابعبه Microsoft CDO برای ویندوز 2000 کتابخانهبروید ، آن را فعال کنید و خوبرا انتخاب کنید.
برای ارسال ایمیل و جاسازی نتایج صفحه گسترده ، سه بخش اصلی برای كدی كه باید ایجاد كنید وجود دارد.
اولین تنظیم متغیرهایی برای نگهداری است. موضوع ، آدرس ها از و به آدرس ها ، و آدرس ایمیل.
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 (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
1 را جایگزین کنید.و گذرواژه با جزئیات حساب شخصی خود.
در آخر ، برای شروع ارسال ایمیل ، کد زیر را وارد کنید.
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
توجه: اگر هنگام اجرای این کد خطای حمل و نقل را مشاهده کردید ، به احتمال زیاد به دلیل حساب Google شما اجرای "برنامه های کمتر ایمن" را مسدود می کند. لازم است به صفحه تنظیمات برنامه های کمتر ایمن مراجعه کرده و این ویژگی را روشن کنید.
پس از فعال شدن ، ایمیل شما ارسال می شود. این همان چیزی است که به نظر می رسد به شخصی که ایمیل شما را به طور خودکار ایجاد می کند ، باشد.