هدف کار -مطالعه عملگرهای پایه و پیاده سازی سازه های برنامه نویسی پایه در زبان VBA.

4.1 مراحل اصلی کار با برنامه های vba در اکسل

برای تهیه و اجرای برنامه در VBA باید موارد زیر را انجام دهید:

در اکسل، یک دستور را از منو انتخاب کنید ابزارها – ماکرو – ویرایشگردیداریپایه ای;

در پنجره ای که ظاهر می شود، دستور را از منو انتخاب کنید درج کنید – مدول. یک ماژول ایجاد می شود، یعنی به بیان ساده، پنجره ای باز می شود که در آن می توانید متن برنامه را وارد کنید.

ممکن است یک دستورالعمل در ابتدای ماژول نشان داده شود گزینهصریح. اگر مشخص شده باشد، تمام متغیرهای استفاده شده در برنامه باید در عبارت اعلان شوند کم نور(برای اطلاعات بیشتر در این مورد، به بخش 4.3 مراجعه کنید).

در برخی موارد، بسته به نحوه پیکربندی محیط VBA، دستورالعمل گزینهصریحدر ابتدای ماژول به طور خودکار نشان داده شده است. اگر برنامه نویس بخواهد از متغیرها بدون اعلام آنها در یک دستور استفاده کند کم نور، سپس دستورالعمل ها گزینهصریحباید حذف شود.

برای اجرای برنامه باید دستور را از منو انتخاب کنید اجرا – Sub/UserForm را اجرا کنید.

4.2 ساده ترین مثال از یک برنامه در VBA

مثال 4.1– برنامه ای که عدد مشخصی را بالا می برد آبه درجه مشخص شده ب.

Dim a به عنوان تک، b به عنوان تنها

a = InputBox ("پایه را وارد کنید:")

b = InputBox ("نمایش را وارد کنید:")

x = a^b 'نمایش

MsgBox ("نتیجه" و x)

این کلمه است زیرشروع روش را نشان می دهد. نام او در در این مورد – آغازگر4_1 . یک برنامه VBA همیشه از یک یا چند رویه (در این مورد، یک) تشکیل شده است.

کاراکتر ' (یک علامت نقل قول) شروع یک نظر را نشان می دهد. متن نظر می تواند هر چیزی باشد.

کم نور- عملگر اعلام متغیر. در این حالت مشخص می شود که متغیرها آو بنوع داشته باشد تنها. آن ها می تواند یا کامل باشد یا اعداد کسری. انواع داده ها و اعلان های متغیر با جزئیات بیشتر در بخش 4.3 مورد بحث قرار خواهند گرفت.

InputBox– تابعی برای وارد کردن مقدار یک متغیر. خط آ = InputBox(«پایه را وارد کنید:”) به این معنی است که مقدار متغیر در حال وارد شدن است آ; پیامی روی صفحه نمایش داده می شود "وارد پایگاه:". خط ایکس = آ^ ب– عملگر انتساب: مقدار سمت راست محاسبه می شود (در این حالت، متغیر آبه قدرت رساند ب) و نتیجه به متغیر مشخص شده در سمت چپ (در این مورد متغیر ایکس). خط MsgBox("نتیجه" وایکس) به این معنی است که یک پیام روی صفحه نمایش داده می شود "نتیجه برابر است با"و مقدار متغیر ایکس.

توجه - امضا کنید & در عمل MsgBoxطراحی شده برای به هم پیوستن چندین عنصر داده ای که باید نمایش داده شوند، در این مثال - رشته ها "نتیجه برابر است"و متغیر ایکس. علامت مشابه & می تواند در عملکرد استفاده شود InputBox(نمونه هایی از این موارد در زیر آورده خواهد شد). جلوی تابلو & و پس از آن باید فضاهایی وجود داشته باشد.

می توانید چندین دستور VBA را در یک خط قرار دهید. برای انجام این کار، آنها با کاراکترهای کولون از هم جدا می شوند. بنابراین، برنامه از مثال 4.1 می تواند به عنوان مثال به صورت زیر نوشته شود:

اولین نمونه از یک برنامه VBA

Dim a به عنوان تک، b به عنوان تنها

a = InputBox("پایه را وارد کنید:") : b = InputBox("نمایش را وارد کنید:")

x = a^b: MsgBox ("نتیجه " & x) "نمایش و خروجی نتیجه

این مثال از رویه ای به نام Subroutine استفاده می کند. این رویه با کلمه شروع می شود زیر. یک برنامه VBA همیشه حداقل یک رویه زیر روال دارد. علاوه بر این، زبان VBA نوع دیگری از رویه دارد - توابع. یک رویه تابع با کلمه شروع می شود تابع. استفاده از چنین رویه‌هایی در بخش 4.9 مورد بحث قرار خواهد گرفت.

(ویژوال بیسیکبرای درخواست)

زبان برنامه نویسی پایه - سطح بالا (مترجم)

ویژوال - حاوی ابزارهای بصری برای توسعه برنامه ها (کدها) است که کار کاربر را ساده می کند، به شما امکان می دهد کدها را با استفاده از یک ضبط کننده ماکرو ضبط کنید.

Application - برنامه ای برای سیستم نرم افزار MSOffice، همراه با برنامه های Word، Access و PowerPoint.

چرا نیاز است؟VBA؟

برنامه ها را ترکیب می کند (ادغام می کند)، به شما امکان می دهد کار برنامه های دیگر را بدون ترک اکسل مدیریت کنید و اشیاء را از برنامه های دیگر جاسازی کنید.

اقدامات در دسترس کاربر استدر کاربرگ 10٪ از تمام قابلیت های برنامه اکسل را تشکیل می دهد، VBA به شما امکان می دهد کار پروژه خود را خودکار کنید.

VBA یک زبان شی گرا است، به این معنی که از اشیاء هنگام توسعه پروژه ها استفاده می شود. تعریف:ترکیب داده ها و کدها در یک کل واحد به عنوان مثال، عنصر "Button" و کد مربوط به این دکمه انتقال به صفحه دیگری را فراهم می کند. کتاب کار.

اشیاء اصلیVBA:

برنامه (خود برنامه اکسل)

کتاب کار (کتاب کار فایل شماست)

WorksheetFunction (جادوگر عملکرد)

برگه کار

دامنه

چارت سازمانی

سبک

مرز

داخلی (رنگ پس زمینه)

فونت (فونت)

مجموعه ای از برخی اشیاء را تشکیل می دهند خانواده ها-کتاب های کاری، برگه های کاری، نمودارها.

اشیا دارای خواص (عملکرد بر روی اشیا) و روش (عملکرد بر روی خود اشیا) هستند.

بیایید شروع به آشنایی با ابزارهای تجسم برای توسعه پروژه در VBA کنیم. این محیط توسعه برنامه یکپارچه است. برای ورود به این محیط، باید ToolsMacroVBA Editor را از آیتم منو انتخاب کنید یا کلیدهای ALT و F11 را به طور همزمان فشار دهید.

اجزای ویرایشگر VBA روی صفحه ظاهر می شوند:

پنجره پروژه – پروژه VBA

پنجره خواص

پنجره کد

پنجره UserForm

نوار ابزار

پنجره Project–VBAProject (شکل 1) ساختار پروژه (فایل) شما را نشان می دهد. این پنجره در ویرایشگر VBA با انتخاب دستور View  Project Explorer یا دکمه "Project Explorer" یا با فشار دادن Ctrl + R فعال می شود.

برنج. 1 پنجره پروژه

شکل 2 محیط توسعه برنامه یکپارچه

پنجره کد برای ذخیره کد مرتبط با یک شی طراحی شده است. هر شی پنجره مخصوص به خود را دارد، بنابراین هر صفحه کاری پنجره کد خود را دارد و WorkBook پنجره مخصوص به خود را دارد.

ایجاد توابع سفارشی

ویژگی های سفارشی اضافه شده است لیست استانداردجادوگران تابع (WorkSheetFunction). این توابع در یک ماژول ویژه ایجاد می شوند که شی WorkSheetFunction را همراهی می کند. این ماژول با استفاده از دستور InsertModule به پروژه اضافه می شود و در پنجره پروژه در سطح برنامه شما ظاهر می شود. تمام کدهای نوشته شده در این ماژول در دسته توابع تعریف شده توسط کاربر در Function Wizard ظاهر می شوند.

بنابراین یک ماژول به پروژه خود اضافه می کنیم (InsertModule) و در پنجره کد این ماژول متن برنامه را می نویسیم:

y = Cos((x + 2) / 2) ^ 2 + Exp(-2 * x) / (x ^ 2 + 1) ^ 0.5

سپس به کاربرگ "1 نمودار" بروید، در سلول c2 ما به آن دسترسی خواهیم داشت خصوصیت جدید، به تابع ویزارد اضافه شد - y(x). کار با این تابع هیچ تفاوتی با کار با هر تابع دیگری ندارد. در مرحله اول، باید y(x) را در دسته «توابع تعریف شده توسط کاربر» انتخاب کنید، در مرحله دوم سلول A2 را به عنوان آرگومان x مشخص کنید. در نتیجه فرمول =y(A2) در سلول C2 نوشته خواهد شد. همانطور که در شکل نشان داده شده است این فرمول را در کل محدوده A2:A17 بکشید. 3. البته، نتیجه باید با آنچه که با محاسبه این تابع با استفاده از ابزارهای معمول کاربرگ به دست آورده اید مطابقت داشته باشد.

شکل 3. تابع y(x)، به روش معمول و با استفاده از یک تابع تعریف شده توسط کاربر محاسبه می شود.

پایه ای اپراتورهای vba- اظهارات مشروط

عملگرهای شرطی 2 شکل نوشتن دارند:

1) یک خط

اگر< условие>سپس<оператор 1>

IF، THEN، ELSE - کلمات غیرقابل تغییر را در براکت های زاویه ای ارائه دهید< >متن کاربر همان چیزی است که مطابق با تکلیف می نویسید، بخشی در کروشه اختیاری است، ممکن است متن موجود نباشد. این فرم معمولاً در موارد ساده استفاده می شود، به عنوان مثال، هنگام محاسبه مدول یک عدد y=x=abs(s)، می توانید از عملگر زیر استفاده کنید:

اگر x > 0، y = x ELSE y = -x

2) در چند خط. در این حالت، دستور شرطی باید با عبارت "ENDIF" پایان یابد.

اگر<условие>سپس

<оператор 1>

<оператор 2>

<оператор 3>

<оператор 4>

این فرم برای محاسبات پیچیده، به عنوان مثال، هنگام محاسبه ریشه های یک معادله درجه دوم استفاده می شود. فرض کنید باید ریشه های معادله a*x 2 +b*x+c= 0 را پیدا کنیم. همانطور که مشخص است، اگر b 2 -4*a*c≥ 0 باشد، ریشه ها با استفاده از فرمول محاسبه می شوند.
، اگر b 2 -4*a*c≤ 0 باشد، در ناحیه اعداد حقیقی هیچ ریشه ای وجود ندارد. اپراتور مشروط، که این الگوریتم را پیاده سازی می کند، به شکل زیر است:

اگر b^2 -4*a*c>= 0 سپس

X1 = (-b + (b^2 - 4*a*c)^(1/2)) / (2*a)

X2 = (-b + (b^2 + 4*a*c)^(1/2)) / (2*a)

X1 = "بدون راه حل"

X2 = "بدون راه حل"

نمونه هایی از نوشتن توابع سفارشی در vba

مثال 1.

اف
تابع y(x)

y = Cos((x + 2) / 2) ^ 2 + Exp(-2 * x) / (x ^ 2 + 1) ^ 0.5

تابع پایان

مثال 2

تابع z(x)

اگر x< 0 Then

z = (1 + x + x^2) / (1 + x^2)

اگر x< 1 Then

z = (1 + 2 * x / (1 + x^2)) ^ (1/2)

z = 2 * Abs(0.5 + Sin(x))

تابع پایان

سخنرانی 2

ویژگی ها، روش ها و رویدادهای اشیاء VBA. نمونه ای از یک رویه با استفاده از خصوصیات فردی شی Application.

کنترل ها

انواع متغیرها در VBA

عبارات حلقه

نمونه ای از برنامه جدول بندی تابع

ویژگی ها، روش ها و رویدادهای اشیاء VBA. نمونه ای از یک رویه با استفاده از خصوصیات فردی شی Application.

بیایید آشنایی خود را با مدل شیء VBA ادامه دهیم. همه اشیا در سازماندهی شده اند ساختار سلسله مراتبی، تابع مفعول سطح بالا"کاربرد".

کاربرد . کتاب های کار ("نمودار") .

اگر کتاب کار (فایل) "Charts" فعال است، کافی است آن را مشخص کنید

کاربرگ ("محتوا"). محدوده ("A1").

اگر روی صفحه "محتوا" کار می کنید، پیوند مانند محدوده ("A1") خواهد بود.

همه اشیا دارای ویژگی ها، روش ها و رویدادها هستند.

ویژگیاین برخی از ویژگی های یک شی است (رنگ، ​​شکل، نام، مکان، دید و غیره) مقدار شی به صورت زیر تنظیم می شود:

Object.Property = ارزش دارایی

روشاین عملی است که روی یک شی (باز کردن، بستن، حذف کردن) انجام می شود. قانون روش نوشتن:

یک شی. روش

رویداداین یک عمل است که توسط یک شی شناسایی می شود (کلیک ماوس، دوبار کلیک، فشار دادن کلید).

ماهیت برنامه نویسی در VBA از دو مفهوم تشکیل شده است - یک رویداد و یک پاسخ به آن. اگر کاربر بر روی سیستم تأثیر بگذارد (فشردن یک دکمه) که یک رویداد است، با استفاده از VBA می توان یک پاسخ - یک عمل پاسخ را برنامه ریزی کرد.

در اینجا برخی از ویژگی ها، روش ها و رویدادهای اشیاء اصلی آورده شده است.

ویژگی های متد Application به شما این امکان را می دهد که مقادیر بسیاری از گزینه ها را به صورت برنامه ریزی شده در پنجره Tools/Options تنظیم کنید. به عنوان مثال، با استفاده از رویه های نشان داده شده در زیر، می توانید ظاهر استاندارد پنجره اکسل را تغییر دهید، عنوان را تغییر دهید و عمل کشیدن و رها کردن فرمول ها را غیرفعال کنید.

این رویه‌ها در ماژول «This Workbook» ثبت می‌شوند و زمانی که یک کتاب کار باز و بسته می‌شود، یعنی زمانی که رویدادهای Open و BeforeClose اجرا می‌شوند، اجرا می‌شوند:

Private Sub Workbook_open()

"عنوان کتاب کار

Application.Caption = "کیسا و اوسیا اینجا بودند"

محدوده رنگ پس زمینه A1:D1 - قرمز

"محدودیت های برد A1:D1 نقطه چین هستند

"کشیدن سلول لغو شد. CellDragAndDrops

Application.CellDragAndDrop = نادرست

نوار فرمول حذف می شود

Application.DisplayFormulaBar = False

نوارهای اسکرول حذف می شوند

Application.DisplayScrollBars = False

«سبک پیوند R1C1 را تنظیم می کند

Application.ReferenceStyle = xlR1C1

"Private Sub Workbook_BeforeClose(Cancel As Boolean)

«کشیدن سلول CellDragAndDrops بازیابی شد

Application.CellDragAndDrop = True

نوار فرمول بازیابی شد

Application.DisplayFormulaBar = True

نوارهای پیمایش بازیابی شدند

Application.DisplayScrollBars = True

«سبک پیوند A1 بازیابی شده است

Application.ReferenceStyle = xlA1

تعداد کمی از مردم می دانند که اولین نسخه از محبوب است محصول مایکروسافتاکسل در سال 1985 منتشر شد. از آن زمان تاکنون، تغییرات زیادی را پشت سر گذاشته و در بین میلیون‌ها کاربر در سراسر جهان مورد تقاضا است. در عین حال، بسیاری تنها با بخش کوچکی از قابلیت‌های این پردازنده صفحه‌گسترده کار می‌کنند و حتی نمی‌دانند که چگونه توانایی برنامه‌نویسی در اکسل می‌تواند زندگی آنها را آسان‌تر کند.

VBA چیست

برنامه نویسی در اکسل با استفاده از زبان برنامه نویسی Visual Basic for Application انجام می شود که در ابتدا در معروف ترین پردازنده صفحه گسترده مایکروسافت ساخته شده بود.

کارشناسان سهولت نسبی یادگیری آن را به مزایای آن نسبت می دهند. چگونه تمرین نشان می دهدحتی کاربرانی که مهارت های برنامه نویسی حرفه ای ندارند می توانند به اصول اولیه VBA تسلط داشته باشند. از ویژگی های خاص VBA می توان به اجرای اسکریپت در محیط برنامه های اداری اشاره کرد.

• به خط "Macros" برگردید.
• "Macro 1" را از لیست انتخاب کنید.
• روی "Run" کلیک کنید (همان عمل با شروع کلید ترکیبی "Ctrl+hh" اجرا می شود).

در نتیجه، عملی که در طول فرآیند ضبط ماکرو انجام شد، رخ می دهد.

منطقی است که ببینید کد چگونه به نظر می رسد. برای انجام این کار، به خط "Macros" برگردید و روی "Change" یا "Login" کلیک کنید. در نتیجه، آنها در محیط VBA قرار می گیرند. در واقع، خود کد ماکرو بین خطوط Sub Macro1() و End Sub قرار دارد.

اگر به عنوان مثال، کپی کردن از سلول A1 به سلول C1 انجام شده باشد، یکی از خطوط کد مانند Range ("C1") خواهد بود. انتخاب کنید. ترجمه شده، به نظر می رسد "Range("C1").Select، به عبارت دیگر، به سلول C1 در VBA Excel منتقل می شود.

قسمت فعال کد با دستور ActiveSheet.Paste تکمیل می شود. این به معنای نوشتن محتویات سلول انتخاب شده (در این مورد A1) در سلول انتخاب شده C1 است.

مثال 2

حلقه های VBA به شما کمک می کند تا ماکروهای مختلفی را در اکسل ایجاد کنید.

حلقه های VBA به شما در ایجاد ماکروهای مختلف کمک می کند. فرض کنید یک تابع y=x + x2 + 3x3 - cos(x) وجود دارد. برای دریافت نمودار آن باید یک ماکرو ایجاد کنید. این کار فقط با استفاده از حلقه های VBA قابل انجام است.

مقادیر اولیه و نهایی آرگومان تابع x1=0 و x2=10 است. علاوه بر این، باید یک ثابت وارد کنید - مقدار مرحله تغییر آرگومان و مقدار اولیه برای شمارنده.

همه نمونه ها ماکروهای VBAفایل های اکسل با استفاده از همان روشی که در بالا ارائه شد ایجاد می شوند. در این مورد خاص، کد به نظر می رسد:

انجام while x1< x2 (цикл будeт выполняться пока вeрно выражeниe x1 < x2)

y=x1 + x1^2 + 3*x1^3 - Cos(x1)

سلول ها (i، 1). مقدار = x1 (مقدار x1 با مختصات (i,1) روی سلول نوشته می شود)

سلول ها (i، 2). مقدار = y (مقدار y با مختصات (i،2) روی سلول نوشته می شود)

i = i + 1 (شمارنده در حال اجرا است).

x1 = x1 + shag (آگومان با مقدار مرحله تغییر می کند).

در نتیجه اجرای این ماکرو در اکسل، دو ستون دریافت می کنیم که ستون اول حاوی مقادیر x و ستون دوم برای y است.

سپس یک نمودار از آنها به صورت استاندارد برای اکسل ساخته می شود.

مثال 3

برای پیاده‌سازی حلقه‌ها در VBA Excel 2010، مانند سایر نسخه‌ها، همراه با ساخت Do while از قبل، از For استفاده می‌شود.

بیایید به برنامه ای نگاه کنیم که یک ستون ایجاد می کند. در هر یک از خانه های آن مربع های شماره خط مربوطه نوشته می شود. استفاده از ساختار For به شما این امکان را می دهد که بدون استفاده از شمارنده، آن را بسیار کوتاه بنویسید.

ابتدا باید یک ماکرو همانطور که در بالا توضیح داده شد ایجاد کنید. بعد خود کد را یادداشت می کنیم. ما معتقدیم که به مقادیر 10 سلول علاقه مندیم. کد شبیه این است.

برای i = 1 تا 10 بعد

این دستور به زبان «انسانی» به صورت «از 1 تا 10 در مراحل یک تکرار کنید» ترجمه می‌شود.

اگر وظیفه به دست آوردن یک ستون با مربع، برای مثال، از همه اعداد فرد از محدوده 1 تا 11 باشد، می نویسیم:

برای i = 1 تا 10 مرحله 1 بعد.

در اینجا مرحله یک مرحله است. در این صورت برابر با دو است. به طور پیش فرض، عدم وجود این کلمه در حلقه به معنای تک بودن مرحله است.

نتایج به دست آمده باید در سلول های شماره (i,1) ذخیره شود. سپس، هر بار که حلقه با افزایش i با اندازه گام شروع می شود، تعداد ردیف به طور خودکار افزایش می یابد. بنابراین، کد بهینه خواهد شد.

به طور کلی، کد به صورت زیر خواهد بود:

برای i = 1 به 10 مرحله 1 (شما می توانید به سادگی برای i = 1 تا 10 بنویسید)

سلول ها (i، 1). مقدار = i ^ 2 (یعنی مقدار مربع i در سلول (i،1) نوشته می شود)

بعد (به یک معنا نقش شمارنده را بازی می کند و به معنای شروع دیگری از چرخه است)

اگر همه چیز به درستی انجام شود، از جمله ضبط و اجرای ماکرو (دستورالعمل های بالا را ببینید)، پس هر بار که آن را فراخوانی می کنید، ستونی با اندازه مشخص شده (در این مورد شامل 10 سلول) دریافت خواهید کرد.

مثال 4

در زندگی روزمره، اغلب نیاز به اتخاذ یک یا آن تصمیم بسته به شرایط خاصی وجود دارد. در VBA Excel نمی توانید بدون آنها کار کنید. نمونه‌هایی از برنامه‌هایی که مسیر بعدی اجرای الگوریتم انتخاب می‌شود و در ابتدا از پیش تعیین نشده است، اغلب از If ...Then ساخت و ساز (برای موارد پیچیده) استفاده می‌کنند. If ...Then ...END If.

بیایید یک مورد خاص را در نظر بگیریم. فرض کنید باید یک ماکرو برای اکسل ایجاد کنید تا موارد زیر در سلول با مختصات (1،1) نوشته شود:

1، اگر استدلال مثبت باشد.

0 اگر آرگومان صفر باشد؛

1 اگر استدلال منفی باشد.

ایجاد چنین ماکرو برای اکسل آغاز می شود به صورت استاندارد، از طریق استفاده از "گرم" کلیدهای Altو F11. در ادامه کد زیر نوشته می شود:

x= Cells(1, 1).Value (این دستور x مقدار محتویات سلول را با مختصات (1، 1) اختصاص می دهد)

اگر x>0 باشد، سلول ها (1، 1). مقدار = 1

اگر x=0 سپس سلول ها (1، 1). مقدار = 0

اگر x<0 Then Cells(1, 1).Value = -1

تنها کاری که باقی می ماند اجرای ماکرو و دریافت مقدار مورد نیاز برای آرگومان در اکسل است.

توابع VBA

همانطور که قبلاً متوجه شده اید، برنامه نویسی در معروف ترین پردازنده جدول مایکروسافت چندان دشوار نیست. به خصوص اگر یاد بگیرید که چگونه از توابع VBA استفاده کنید. در مجموع، این زبان برنامه نویسی که به طور خاص برای نوشتن برنامه های کاربردی در Excel و Word ایجاد شده است، حدود 160 عملکرد دارد. آنها را می توان به چند گروه بزرگ تقسیم کرد. این:

• توابع ریاضی با اعمال آنها در آرگومان، مقدار کسینوس، لگاریتم طبیعی، قسمت صحیح و غیره را به دست می آورند.
• توابع مالی به لطف در دسترس بودن آنها و استفاده از برنامه نویسی در اکسل، می توانید ابزارهای موثری برای حسابداری و محاسبات مالی به دست آورید.
• توابع پردازش آرایه اینها عبارتند از Array، IsArray. LBound; UBound.
• توابع VBA اکسل برای رشته. این یک گروه نسبتاً بزرگ است. برای مثال شامل توابع Space برای ایجاد رشته ای با تعداد فضاهای برابر با آرگومان عدد صحیح یا Asc برای تبدیل کاراکترها به کد ANSI است. همه آنها به طور گسترده مورد استفاده قرار می گیرند و به شما امکان می دهند با ردیف ها در اکسل کار کنید و برنامه هایی ایجاد کنید که کار با این جداول را تا حد زیادی تسهیل می کند.
• توابع تبدیل نوع داده برای مثال، CVar مقدار آرگومان Expression را با تبدیل آن به نوع داده Variant برمی‌گرداند.
• توابع کار با تاریخ آنها به طور قابل توجهی قابلیت های استاندارد اکسل را گسترش می دهند. بنابراین، تابع WeekdayName نام (کامل یا جزئی) روز هفته را با شماره آن برمی گرداند. حتی مفیدتر تایمر است. تعداد ثانیه هایی را می دهد که از نیمه شب به نقطه خاصی در روز گذشته است.
• توابعی برای تبدیل یک آرگومان عددی به سیستم های اعداد مختلف. برای مثال، Oct اعداد را به صورت هشتی نمایش می دهد.
• توابع قالب بندی مهمترین آنها Format است. یک مقدار Variant را با یک عبارت فرمت شده بر اساس دستورالعمل های مشخص شده در اعلان فرمت برمی گرداند.
• و غیره.

مطالعه خصوصیات این توابع و کاربرد آنها به طور قابل توجهی دامنه اکسل را گسترش می دهد.

مثال 5

بیایید سعی کنیم به سمت حل مشکلات پیچیده تر حرکت کنیم. مثلا:

یک سند کاغذی گزارش سطح واقعی هزینه های شرکت داده شده است. ضروری:

• بخش قالب آن را با استفاده از صفحه گسترده اکسل توسعه دهید.
• یک برنامه VBA ایجاد کنید که داده های اولیه را برای پر کردن آن درخواست می کند، محاسبات لازم را انجام می دهد و سلول های قالب مربوطه را با آنها پر می کند.

بیایید یکی از راه حل های ممکن را در نظر بگیریم.

ایجاد یک الگو

تمام اقدامات بر روی یک برگه استاندارد در اکسل انجام می شود. سلول های رایگان برای وارد کردن داده ها بر اساس ماه، سال، نام شرکت مصرف کننده، میزان هزینه ها، سطح آنها، گردش مالی رزرو شده است. از آنجایی که تعداد شرکت ها (جامعه ها) که گزارش برای آنها تنظیم می شود ثابت نیست، سلول های وارد کردن مقادیر بر اساس نتایج و نام متخصص از قبل رزرو نمی شوند. به کاربرگ نام جدیدی داده می شود. به عنوان مثال، "گزارش".

متغیرها

برای نوشتن برنامه ای برای پر کردن خودکار یک الگو، باید نمادها را انتخاب کنید. آنها برای متغیرها استفاده خواهند شد:

• NN - شماره ردیف جدول فعلی؛
• TP و TF - گردش تجاری برنامه ریزی شده و واقعی؛
• SF و SP - مقدار واقعی و برنامه ریزی شده هزینه ها؛
• IP و IF - سطح برنامه ریزی شده و واقعی هزینه ها.

اجازه دهید از همان حروف استفاده کنیم، اما با پیشوند Itog، برای نشان دادن انباشت کل برای این ستون. به عنوان مثال، ItogTP به ستون جدولی با عنوان گردش مالی برنامه ریزی شده اشاره دارد.

حل مسئله با استفاده از برنامه نویسی VBA

با استفاده از نمادهای معرفی شده، فرمول هایی برای انحرافات به دست می آوریم. اگر نیاز به محاسبه در٪ دارید، (F - P) / P * 100، و در مجموع - (F - P) داریم.

بهترین راه برای وارد کردن فوری نتایج این محاسبات در سلول های مربوطه جدول اکسل است.

برای مجموع های واقعی و پیش بینی شده، آنها با استفاده از فرمول های ItogP=ItogP + P و ItogF=ItogF+ F به دست می آیند.

برای انحرافات از = (ItogF - ItogP) / ItogP * 100 استفاده کنید، اگر محاسبه به صورت درصد انجام شود، و در مورد مقدار کل - (ItogF - ItogP).

نتایج دوباره بلافاصله در سلول های مربوطه نوشته می شوند، بنابراین نیازی به تخصیص آنها به متغیرها نیست.

قبل از اجرای برنامه ایجاد شده، باید کتاب کار را به عنوان مثال با نام "Report1.xls" ذخیره کنید.

دکمه "ایجاد جدول گزارش" فقط باید یک بار پس از وارد کردن اطلاعات هدر فشار داده شود. قوانین دیگری وجود دارد که باید بدانید. به طور خاص، دکمه "افزودن ردیف" باید هر بار پس از وارد کردن مقادیر برای هر نوع فعالیت در جدول فشار داده شود. پس از وارد کردن تمام داده ها، باید روی دکمه "پایان" کلیک کنید و سپس به پنجره اکسل بروید.

اکنون می دانید که چگونه با استفاده از ماکروها مشکلات اکسل را حل کنید. توانایی استفاده از VBA Excel (نمونه‌هایی از برنامه‌های بالا را ببینید) همچنین ممکن است برای کار در محبوب‌ترین ویرایشگر متن در حال حاضر، Word، مورد نیاز باشد. به طور خاص، می توانید با نوشتن، همانطور که در همان ابتدای مقاله نشان داده شده است، یا با نوشتن کد، دکمه های منو ایجاد کنید، که به لطف آن می توان بسیاری از عملیات روی متن را با فشار دادن کلیدهای وظیفه یا از طریق "نمایش" انجام داد. برگه و نماد ماکروها.

نمونه های ساده ماکرو اکسل زیر برخی از ویژگی ها و تکنیک های شرح داده شده در آموزش Excel VBA را نشان می دهد.

ماکرو اکسل: مثال 1

در ابتدا این روش زیربه عنوان نمونه ای از استفاده از نظرات در کد VBA ارائه شد. با این حال، در اینجا می توانید نحوه اعلان متغیرها، نحوه کار مراجع سلولی اکسل و استفاده از یک حلقه را نیز مشاهده کنید. برای، عملگر شرطی اگرو نمایش پنجره پیام

"رویال Sub سلولی حاوی رشته مشخص شده" را در محدوده سلول های A1:A100 صفحه فعال Sub Find_String(sFindText As String) Dim i As Integer "یک عدد صحیح از نوع Integer، استفاده شده در حلقه For Dim iRowNumber جستجو می کند. به عنوان Integer "یک عدد صحیح از نوع Integer برای ذخیره نتیجه iRowNumber = 0 "در سلول های A1:A100 یک به یک نگاه می کند تا زمانی که رشته پیدا شود sFindText For i = 1 تا 100 If Cells(i, 1).Value = sFindText Then " اگر مطابقت با رشته مشخص شده پیدا شد، تعداد سطر فعلی را ذخیره کنید و از حلقه خارج شوید. سطر مشخص شده یافت می شود، نشان می دهد که در کدام سلول مطابقت پیدا می شود If iRowNumber = 0 سپس MsgBox "Row" & sFindText & " not found" Else MsgBox "Row" & sFindText & " در سلول A یافت می شود و iRowNumber End If End Sub

ماکرو اکسل: مثال 2

رویه بعدی زیر- مثال استفاده از حلقه انجام در حالی که. همچنین می توانید نحوه اعلان متغیرها، کار با مراجع سلول اکسل و استفاده از یک دستور شرطی را مشاهده کنید. اگر.

"رویه Sub خروجی اعداد فیبوناچی از 1000 زیر فیبوناچی () Dim i به عنوان صحیح "یک شمارنده برای نشان دادن موقعیت یک عنصر در دنباله Dim iFib به عنوان عدد صحیح "مقدار فعلی دنباله Dim iFib_Next را به عنوان عدد صحیح ذخیره می کند" مقدار بعدی دنباله Dim iStep As Integer "اندازه افزایش بعدی را ذخیره می کند" متغیرهای i و iFib_Next را مقداردهی کنید. iFib_Next< 1000 If i = 1 Then "Особый случай для первого элемента последовательности iStep = 1 iFib = 0 Else "Сохраняем размер следующего приращения перед тем, как перезаписать "текущее значение последовательности iStep = iFib iFib = iFib_Next End If "Выводим текущее число Фибоначчи в столбце A активного рабочего листа "в строке с индексом i Cells(i, 1).Value = iFib "Вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub

ماکرو اکسل: مثال 3

این رویه زیرسلول های یک ستون را اسکن می کند آبرگه فعال تا زمانی که با یک سلول خالی مواجه شود. مقادیر در یک آرایه نوشته می شوند. این ماکرو ساده اکسل کار با آرایه های پویا و همچنین استفاده از یک حلقه را نشان می دهد انجام دهید تا. در این مثال، ما هیچ عملی را با آرایه انجام نخواهیم داد، اگرچه در برنامه نویسی واقعی، پس از نوشتن داده ها در آرایه، معمولاً چنین اقداماتی بر روی آنها انجام می شود.

رویه Sub مقادیر سلولی ستون A صفحه فعال را در آرایه Sub GetCellValues() Dim iRow As Integer ذخیره می‌کند. iRow = 1 ReDim dCellValues ​​(1 تا 10) "حلقه را انجام دهید تا به صورت متوالی در سلول های ستون A برگه فعال تکرار شود" و مقادیر آنها را در یک آرایه استخراج کند تا زمانی که با یک سلول خالی مواجه شود Do Until IsEmpty(Cells( iRow، 1)) "بررسی کنید که آرایه dCellValues ​​اندازه کافی داشته باشد. "اگر نه، با استفاده از ReDim If UBound(dCellValues) آرایه اندازه را 10 افزایش دهید.< iRow Then ReDim Preserve dCellValues(1 To iRow + 9) End If "Сохраняем значение текущей ячейки в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop End Sub

ماکرو اکسل: مثال 4

در این مثال، رویه زیرمقادیر را از یک ستون می خواند آکاربرگ ورق 2و بر روی آنها عملیات حسابی انجام می دهد. نتایج به سلول های ستون وارد می شود آدر کاربرگ فعال این ماکرو استفاده از اشیاء اکسل را نشان می دهد. به ویژه، تجدید نظر توسط رویه انجام می شود زیربه شی ستون ها، و نحوه دسترسی به این شی از طریق شی را نشان می دهد کاربرگ. همچنین نشان داده شده است که هنگام دسترسی به سلول یا محدوده ای از سلول ها در برگه فعال، در هنگام نوشتن پیوند، نیازی به تعیین نام این برگه نیست.

"رویه Sub، با استفاده از یک حلقه، مقادیر ستون A از کاربرگ Sheet2 را می خواند، "عملیات حسابی را با هر مقدار انجام می دهد و نتیجه را در "ستون A از کاربرگ فعال (Sheet1) Sub Transfer_ColA() Dim i می نویسد. به عنوان عدد صحیح Dim Col As Range Dim dVal As Double "به متغیر Col ستون A از کاربرگ برگه 2 اختصاص دهید Col = Sheets ("Sheet2"). ستون ها ("A") i = 1 "با استفاده از یک حلقه، مقادیر را می خوانیم . از سلول های ستون Col تا زمانی که "تا زمانی که با یک سلول خالی مواجه شود، انجام دهید تا IsEmpty(Col.Cells(i)) "عملیات حسابی را روی مقدار سلول فعلی dVal = Col.Cells(i) انجام دهید. مقدار * 3 - 1 "دستور زیر نتیجه را در ستون A از کاربرگ فعال می نویسد "نام برگه را در پیوند ضروری نشان دهید زیرا این برگ فعال Cells(i, 1) = dVal i = i + 1 Loop End Sub است.

ماکرو اکسل: مثال 5

این ماکرو نمونه ای از کد VBA را نشان می دهد که یک رویداد اکسل را نظارت می کند. رویدادی که ماکرو به آن متصل می شود، هر بار که یک سلول یا محدوده ای از سلول ها در کاربرگ انتخاب می شود، رخ می دهد. در مورد ما، هنگام انتخاب یک سلول B1، یک پنجره پیام روی صفحه ظاهر می شود.

اگر سلول B1 در کاربرگ فعلی انتخاب شده باشد، این کد یک جعبه پیام را نشان می دهد. Private Sub Worksheet_SelectionChange(ByVal Target As Range) "بررسی کنید سلول B1 انتخاب شده باشد اگر Target.Count = 1 و Target.Row = 1 And Target.Column = 2 سپس "اگر سلول B1 انتخاب شده است، عمل مورد نیاز را انجام دهید MsgBox "شما سلول B1 را انتخاب کرده اید" End If End Sub

ماکرو اکسل: مثال 6

این روش استفاده از عملگرها را نشان می دهد در مورد خطاو از سرگیریبرای رسیدگی به خطا این کد همچنین نمونه ای از باز کردن و خواندن داده ها از یک فایل را نشان می دهد.

«رویال Sub به آرگومان‌های Val1 و Val2 مقادیر سلول‌های A1 و B1 را از Workbook Data.xlsx که در پوشه C:\Documents and Settings قرار دارد، اختصاص می‌دهد Sub Set_Values ​​(Val1 As Double، Val2 As Double) Dim DataWorkbook As Workbook On Error GoTo Error Handling " کتاب کار را با مجموعه داده باز کنید DataWorkbook = Workbooks.Open("C:\Documents and Settings\Data") "تغییرهای Val1 و Val2 را از کتاب کار داده شده اختصاص دهید Val1 = Sheets("Sheet1" ").Cells(1,1) Val2 = Sheets("Sheet1").Cells(1,2) DataWorkbook.Close Exit Sub Error Handling: "اگر فایل پیدا نشد، از کاربر خواسته می شود فایل جستجو شده را قرار دهد" در پوشه مورد نظر و سپس اجرای ماکرو MsgBox "فایل Data.xlsx یافت نشد!" و _ "لطفا کتاب کار را به پوشه C:\Documents and Settings اضافه کنید و روی OK کلیک کنید" Resume End Sub