چگونه یک ماکرو آماده را در کتاب کار قرار دهیم؟ نحوه فعال کردن ماکروها در اکسل ماکروها در اکسل: مثال ها

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

VBA چیست

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

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

نقطه ضعف برنامه مشکلات مرتبط با سازگاری است نسخه های مختلف. آنها به دلیل این واقعیت هستند که کد برنامه VBA به آن اشاره دارد عملکرد، که در نسخه جدیدمحصول، اما در قدیمی وجود ندارد. همچنین از معایب آن می توان به باز بودن بیش از حد کد برای تغییرات اشاره کرد. توسط یک خارجی. با این اوصاف مایکروسافت آفیسو همچنین IBM Lotus Symphony به کاربر این امکان را می دهد که کد اولیه را رمزگذاری کرده و برای مشاهده آن رمز عبور تعیین کند.

اشیاء، مجموعه ها، ویژگی ها و روش ها

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

اصلی ترین برنامه، مربوط به بیشتر است برنامه اکسل. سپس Workbook ها، Worksheets و همچنین Range می آیند. به عنوان مثال، برای دسترسی به سلول A1 در یک کاربرگ خاص، باید مسیری را مشخص کنید که سلسله مراتب را در نظر بگیرد.

در مورد مفهوم "مجموعه"، این گروهی از اشیاء از همان کلاس است که در رکورد دارای شکل ChartObjects است. او عناصر منفردنیز اشیاء هستند.

مفهوم بعدی خواص است. آنها ویژگی ضروری هر شی هستند. به عنوان مثال، برای Range مقدار یا Formula است.

متدها دستوراتی هستند که نشان می دهند چه کاری باید انجام شود. هنگام نوشتن کد در VBA، آنها باید با یک نقطه از شی جدا شوند. برای مثال، همانطور که بعدا نشان داده خواهد شد، اغلب هنگام برنامه نویسی در اکسل، از دستور Cells(1,1).Select استفاده می شود. به این معنی است که شما باید یک سلول با مختصات را انتخاب کنید

همراه با آن، اغلب از Selection.ClearContents استفاده می شود. اجرای آن به معنای پاک کردن محتویات سلول انتخاب شده است.

چگونه شروع کنیم

سپس باید به برنامه VB بروید که برای آن کافی است از کلیدهای ترکیبی "Alt" و "F11" استفاده کنید. به علاوه:

• در نوار منو واقع در بالای پنجره، روی نماد کنار نماد اکسل کلیک کنید.
• دستور Mudule را انتخاب کنید.
• با کلیک بر روی نماد همراه با تصویر ذخیره کنید.
• یک طرح از کد بنویسید.

به نظر می رسد این است:

زیربرنامه()

"کد ما

توجه داشته باشید که خط "کد ما" با رنگ دیگری (سبز) هایلایت می شود. دلیل آن آپستروف در ابتدای خط است که نشان می دهد یک نظر دنبال می شود.

اکنون می توانید هر کدی را بنویسید و برای خود ایجاد کنید ابزار جدید V VBA Excel(نمونه های برنامه را در زیر ببینید). البته برای کسانی که با اصول اولیه ویژوال بیسیک آشنا هستند، کار بسیار راحت تر خواهد بود. با این حال، حتی کسانی که آنها را ندارند، در صورت تمایل، می توانند به سرعت به راحتی راحت شوند.

ماکروها در اکسل

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

مثال 1

وظیفه: برنامه ای بنویسید که مقدار محتویات یک سلول را کپی کرده و سپس در سلول دیگر بنویسد.

برای این:

• برگه "View" را باز کنید؛
• به نماد "Macros" بروید؛
• روی "ضبط ماکرو" کلیک کنید؛
• فرم باز را پر کنید

برای سادگی، "Macro1" را در قسمت "Macro Name" رها کنید، و برای مثال، hh را در قسمت "Keyboard Shortcut" وارد کنید (به این معنی که می توانید برنامه را با دستور blitz "Ctrl + h" اجرا کنید). Enter را فشار دهید.

اکنون که ضبط ماکرو از قبل شروع شده است، محتویات یک سلول در سلول دیگر کپی می شود. به نماد اصلی برگردید. روی "Record macro" کلیک کنید. این عمل نشان دهنده پایان برنامه است.

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

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

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

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

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

مثال 2

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

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

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

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

زیربرنامه()

مرحله = 0.1

انجام while x1< x2 (цикл будет выполняться пока верно выражение 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 (آگومان با مقدار step تغییر می کند).

انتهای زیر

در نتیجه اجرای این ماکرو در اکسل، دو ستون دریافت می کنیم که ستون اول حاوی مقادیر 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

بعد (به یک معنا، به عنوان یک شمارنده عمل می کند و به معنای یک شروع دیگر از حلقه است)

انتهای زیر

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

مثال 4

در زندگی روزمره، اغلب نیاز به تصمیم گیری بسته به شرایطی وجود دارد. در VBA Excel نمی توانید بدون آنها کار کنید. نمونه‌هایی از برنامه‌هایی که در آنها مسیر بعدی الگوریتم انتخاب شده است، و در ابتدا از پیش تعیین نشده است، اغلب از ساختار 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 Excel برای ردیف. این یک گروه نسبتاً بزرگ است. برای مثال شامل توابع Space برای ایجاد رشته ای با تعداد فاصله های برابر با یک آرگومان صحیح یا Asc برای تبدیل کاراکترها به کد ANSI می شود. همه آنها به طور گسترده استفاده می شوند و به شما امکان می دهند با ردیف ها در اکسل کار کنید و برنامه هایی ایجاد کنید که کار با این جداول را بسیار آسان تر می کند.
• توابع تبدیل نوع داده برای مثال، CVar مقدار آرگومان Expression را با تبدیل آن به نوع داده Variant برمی‌گرداند.
• توابع کار با تاریخ آنها به طور قابل توجهی موارد استاندارد را گسترش می دهند بنابراین، تابع WeekdayName نام (کامل یا جزئی) روز هفته را با تعداد آن برمی گرداند. حتی مفیدتر تایمر است. تعداد ثانیه هایی را که از نیمه شب تا یک نقطه خاص در روز سپری شده است را نشان می دهد.
• توابعی برای تبدیل یک آرگومان عددی به سیستم های اعداد مختلف. به عنوان مثال، Oct نمایش هشتگانه یک عدد را می دهد.
• توابع قالب بندی مهمترین آنها Format است. مقداری از نوع Variant را با یک عبارت فرمت شده بر اساس دستورالعمل های داده شده در اعلان فرمت برمی گرداند.
• و غیره.

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

مثال 5

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

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

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

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

یک قالب ایجاد کنید

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

متغیرها

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

• 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" ذخیره کنید.

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

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

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

دو مثال را در نظر بگیرید:

1. جستجو و استفاده از یک تیم

به عنوان مثال، ما به دستوری نیاز داریم که عدد "1" را در سلول های انتخاب شده درج کند. موتور جستجو را راه اندازی می کنیم، عبارت جستجو را تایپ می کنیم، نتایج را دریافت می کنیم، شروع به مرور می کنیم، کد را در چیزی شبیه به این پیدا می کنیم:

Sub Insert1() Dim q As Object On Error Resume بعدی مجموعه q = سلول ها برای هر q در انتخاب q = 1 q بعدی پایان فرعی

این کد را انتخاب کنید (بدون شماره گذاری خط، با کلمه شروع شود زیر) و فشار دهید ctrl+c. به کتاب کار MS Excel خود بروید و میانبر صفحه کلید را فشار دهید Alt+F11، یک پنجره ویرایشگر VBA باز خواهید داشت:

در پنجره سمت چپ "Project - VBA Project" کتاب کار ما را انتخاب کنید (کلیک کنید) که می خواهید یک ماکرو در آن وارد کنید، به عنوان مثال، "VBAProject (Book2)":

در آیتم منو "Insert" "Module" را انتخاب کنید:

در پنجره سمت چپ "Project - VBA Project" باید یک پوشه جدید "Modules" و یک شی جدید "Module1" در آن داشته باشید:

مکان نما را به قسمت سمت راست ورودی "Large" ببرید و فشار دهید ctrl+v، ماکرو کپی شده در ماژول قرار می گیرد:

ماکروها در MS Excel را می توان در مکان های زیر درج کرد:

• در ماژول، آنها معمولا کد ماکروها را وارد می کنند که با فشار دادن یک دکمه توسط کاربر (به عنوان مثال، در مورد ما) یا کد توابع (فرمول ها) راه اندازی می شود.
• در کاربرگ، آنها معمولاً کد ماکروها را وارد می کنند که راه اندازی آنها باید به طور خودکار بسته به اقدامات کاربر یا تغییرات داده های موجود در برگه انجام شود (داده ها تغییر کرده اند، ماکرو اجرا شده است).
• در Workbook معمولا کد ماکروها را درج می کنند که بسته به اقدامات انجام شده روی کتاب (فایل) باید به طور خودکار راه اندازی شود. به عنوان مثال، یک ماکرو که هنگام باز یا بسته شدن کتاب کار یا ذخیره آن اجرا می شود.
• ماکروها همچنین می توانند بخشی از فرم کاربر باشند.

معمولاً شخصی که کد را منتشر می کند، محل قرار دادن آن را در یک ماژول، در یک برگه یا در یک کتاب کار مشخص می کند.

برای درج کد در یک کاربرگ، برگه مربوطه را در پنجره سمت چپ ویرایشگر VBA انتخاب کنید، با دکمه سمت چپ ماوس روی آن دوبار کلیک کنید، مکان نما را به قسمت ورودی سمت راست ببرید و کد را جایگذاری کنید.

برای درج کد در کتاب، «ThisBook» را انتخاب کنید:

بیایید تمرین کنیم. کد زیر را در "Sheet1" قرار دهید.

Worksheet_Change Sub Private (ByVal Target As Range) در خطا برو به یک If Target = 2 سپس VBA.MsgBox("Cell" & Target.Address & "=2") پایان اگر a: خروج Sub End Sub Sub

اگر "2" را در هر سلولی از برگه وارد کنید، این ماکرو یک پیام اطلاعاتی نمایش می دهد.

به کتاب کار برگردید، به "Sheet1" بروید و عدد "2" را در سلول "A1" وارد کنید و فشار دهید. وارد، پس از آن باید پیام زیر را مشاهده کنید:

اگر این پیام را می بینید، پس همه چیز را درست انجام داده اید. اگر نه، پس کد را در جایی اشتباه چسبانده‌اید، دوباره امتحان کنید.

هنگام چسباندن کد، باید مراقب باشید که آن را کجا قرار می دهید. این را می توان با نگاه کردن به آنچه در عنوان پنجره ویرایشگر VBA نوشته شده است انجام داد:

2. جستجو و استفاده از یک تابع

ما در اینترنت کد VBA یک تابع را می یابیم که به عنوان مثال تعداد کلمات یک سلول را می شمارد:

تابع عمومی CountWordsInCell(Cell As Range) Dim q As Variant Application.Volatile q = VBA.Split(Application.WorksheetFunction.Trim(Cell.Value)، " ") CountWordsInCell = UBound(q) + 1 Function End

کد را کپی کنید، میانبر صفحه کلید را فشار دهید Alt+F11، ویرایشگر VBA باز می شود:

ما یک ماژول جدید به کتاب خود اضافه می کنیم و کد کپی شده را در این ماژول قرار می دهیم:

ویرایشگر VBA را ببندید و به کتاب کار خود بروید. یک برگه جدید (اختیاری) در یک سلول ایجاد کنید A1متن "مامان قاب را شست" را وارد کنید. ما در سلولی که می خواهیم نتیجه (تعداد کلمات) را دریافت کنیم، در منوی "Formulas" روی دکمه "Insert Function" کلیک کنید:

در پنجره "Insert function" که باز می شود، در قسمت "Category"، "User-defined" را انتخاب کنید.

در لیست توابع موجود، "تعداد کلمات در سلول" را انتخاب کنید، روی "OK" کلیک کنید:

آرگومان های مورد نیاز را وارد کنید و روی "OK" کلیک کنید:

نتیجه را می گیریم:

مهم:

اگر کتاب کار را که در آن ماکرو را درج کرده‌اید به‌عنوان «کتاب کار با ماکرو فعال» ذخیره نکنید، همه ماژول‌های دارای ماکرو حذف می‌شوند و باید بعداً همه این کارها را دوباره انجام دهید.

اگر هنگام درج یک ماکرو در یک ماژول، به جای مقداری متن، علائم قرمز رنگ مشاهده کردید. ???????? "

این بدان معناست که شما مشکل رمزگذاری دارید که با کپی کردن متن سیریلیک از برخی مرورگرها مشاهده می شود. برای غلبه بر این مشکل، سعی کنید کد کپی شده را در یک برگه خالی MS Excel به عنوان "متن یونیکد" قرار دهید. برای انجام این کار، به کتاب کار MS Excel بروید، یک برگه خالی را انتخاب یا ایجاد کنید، در سلول "A1" بایستید و میانبرهای صفحه کلید را فشار دهید. Ctrl+Alt+V. منوی "Paste Special" ظاهر می شود، "Unicode Text" را انتخاب کرده و روی "OK" کلیک کنید.

کد باید بدون علامت سوال در کاربرگ جایگذاری شود:

پس از آن، یک بار دیگر کد چسبانده شده را در برگه کپی کرده و آن را در ماژول قرار دهید.

اگر پنجره "Project - VBA Project" را در ویرایشگر VBA نمی بینید، به برگه منوی "View" رفته و "Project Explorer" را در لیست انتخاب کنید یا میانبر صفحه کلید را فشار دهید. ctrl+r:

ماکرو برای برجسته کردن روی هر صفحه در کتاب کار فعال سلول A1. همچنین صفحه نمایش را حرکت می دهد.

زیر A1SelectionEachSheet() Dim i As Integer Application.ScreenUpdating = False برای i = 1 To Sheets.Count Sheets(i).انتخاب ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = 1 Range("a1"). برگه های بعدی را انتخاب کنید(1) Application.ScreenUpdating = True End Sub را انتخاب کنید

ماکرو برای کپی کردن برگه فعلی چند بار مشخص. برای آزمایش نوعی ماکرو مفید است - آنها تغییراتی ایجاد کردند، آنها را روی یک کپی از داده ها بررسی کردند. تمام شدن نسخه ها - ماکرو را دوباره اجرا کنید

Sub SimpleCopy() Dim i به عنوان عدد صحیح، j به عنوان عدد صحیح i = Application.InputBox("تعداد کپی های برگه فعلی را وارد کنید") Application.ScreenUpdating = False برای j = 1 به i ActiveSheet.Copy after:=Sheets(Sheets .Count) ActiveSheet .Name = "Copy" & j بعدی j Application.ScreenUpdating = True End Sub Sub

برگه هایی با عناوین از یک محدوده مشخص در یک برگه ایجاد کنید

زیر () CreateFromList سلول کم نور به عنوان محدوده برای هر سلول در برگه های انتخابی.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = cell.Value سلول بعدی پایان زیر

مارکروس ایمیل ها را با تاخیر ارسال می کند. ماکرو اصلاح شده از کتاب John Walkenbach Professional VBA Programming

Sub SendMail() Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon در خطا رفتن به پاکسازی Set OutMail = OutApp.CreateItem(0) در خطا از سرگیری بعد با OutMail .To = " [ایمیل محافظت شده]" .موضوع = "گزارش فروش" .پیوست ها.افزودن "C:\Test.txt" .Body = "متن پیام" .DeferredDeliveryTime = Replace(Date, ".", "/") & " 11:00:00 " .send ".نمایش دهید تا ایمیل شکل بگیرد و آن را باز کنید پایان با خطای روشن برو به 0 تنظیم OutMail = پاکسازی هیچی: تنظیم OutApp = هیچ چیز پایان فرعی

فهرست مطالب ماکرو اندکی تغییر یافته از نیکولای پاولوف.
اگر کتاب قبلاً یک برگه فهرست مطالب دارد، ماکرو پیشنهاد می‌کند آن را حذف کنید. اگر نه، یک برگه "فهرست فهرست مطالب" ایجاد می کند و پیوندهایی با نام برگه ها درج می کند

صفحه زیر TableOfContent() کم نور به عنوان کاربرگ سلول کم نور به عنوان محدوده کم نور پاسخ به عنوان عدد صحیح Application.ScreenUpdating = False با ActiveWorkbook برای هر کاربرگ در ActiveWorkbook.Worksheets If Worksheet.Name = "Table of Contents" سپس پاسخ Moxets در کتاب کاری با نام Table of contents. حذف آن؟ (1)).Select Sheets.Add Sheets(1).Name = "Table of Contents" With ActiveWorkbook for Her sheet In ActiveWorkbook.Worksheets If sheet.Name<>"Table of Contents" سپس سلول را تنظیم کنید = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell، Address:=""، SubAddress:=""" & sheet .Name & """ & "!A1" cell.Formula = sheet.Name End If Sheet Next End With Rows("1:1").Delete Application.ScreenUpdating = True End Sub Sub

مرتب سازی برگه ها از جادوگران VBA. ماکرو برگه های مخفی را نیز مرتب می کند. اگر کتاب دارای محافظ ساختار باشد کار نخواهد کرد

Sub SORT_ALL_SHEETS() Application.ScreenUpdating = False: Application.EnableEvents = False Dim iSht As Worksheet, oDict As Object, i%, j% Set oDict = CreateObject("Scripting.Dictionary") "وضعیت دید هر برگه را به خاطر بسپارید و انجام دهید همه چیز قابل مشاهده برای هر iSht در ActiveWorkbook.Sheets oDict.Item(iSht.Name) = iSht.Visible: iSht.Visible = True Next With ActiveWorkbook " مرتب سازی برگه های قابل مشاهده برای i = 1 به .Sheets.Count - 1 برای j = i + 1 به .Sheets.Count If UCase(.Sheets(i).Name) > UCase(.Sheets(j).Name) سپس .Sheets(j).Move Before:=.Sheets(i) بعدی j بعدی i پایان با "بازیابی وضعیت نمای اولیه هر برگه برای هر iSht در ActiveWorkbook.Sheets iSht.Visible = oDict.Item(iSht.Name) بعدی Application.EnableEvents = True: Application.ScreenUpdating = True End Sub Sub

ستون های "Field1" و "Field2" را از برگه "Sheet1" فایل اکسل "C:\Manager.xls" از طریق اتصال ADODB وارد کنید و محتوا را از سلول A1 صفحه فعلی درج کنید.

صفحات گسترده اکسل یکی از محبوب ترین روش ها برای ذخیره و سازماندهی اطلاعات است. اگر ماکروها را در اکسل پیاده سازی کنید، قابلیت های برنامه تقریبا دو برابر می شود. به عنوان مثال، ماکروها به خودکارسازی فرآیندهای روتین و سرعت بخشیدن به کار یکنواخت کمک می کنند. آنها اغلب توسط متخصصانی استفاده می شوند که گزارش ها را از 1C به Excel ترجمه می کنند. با کمک کدهای تعبیه شده، به راحتی می توان آنها را به یک قالب استاندارد واحد رساند.

ماکروهای اکسل چیست؟

ماکروها نه تنها در صفحات گسترده، بلکه در سراسر مایکروسافت آفیس استفاده می شوند. آنها کد با استفاده از ویژوال بیسیک برای برنامه ها تولید می شوند. اگر تا حدی با برنامه نویسی وب آشنایی دارید، می توانید با جاوا اسکریپت یک قیاس ترسیم کنید. ماکروهای اکسل تقریباً همان کاری را انجام می دهند که جاوا اسکریپت با داده های HTML انجام می دهد.

بخش کوچکی از کارهایی که یک ماکرو می تواند در برنامه های آفیس انجام دهد:

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

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

خطرات ماکروها در اکسل چیست؟

فایل های خراب در رایانه شخصی؛
. سرقت اطلاعات محرمانه

معرفی ویروس در حین عملکرد یکی از برنامه های اداری - Word یا Excel اتفاق می افتد. پس از توقف کار، ویروس به طور مستقل در آنها شروع به کار می کند و کل سیستم را با آن آلوده می کند.

روش دیگری که یک ماکرو مخرب کار می کند، واسطه گری برای تزریق تهدید به سیستم است. در این صورت به نوعی دروازه ای برای معرفی و تولید نرم افزار تروجان می باشد. دیگر توسط یک ماکرو اصلاح شده کنترل نمی شود، بلکه توسط خود سیستم عامل کنترل می شود و بنابراین، تهدید گسترده تر می شود. درمان چنین ویروس هایی همیشه امکان پذیر نیست، گاهی اوقات باید OC ویندوز یا هارد رایانه شخصی را به طور کامل تغییر دهید (بسته به وظایفی که هکر هنگام نوشتن کد تعیین می کند).

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

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

نحوه ضبط ماکروها در اکسل

ساده ترین راه برای ایجاد یک ماکرو برنامه ریزی مستقیم در اکسل است. برای این کار به جدولی با داده های وارد شده و کمی دانش نیاز دارید تا امکان ضبط چنین کدهایی در برنامه وجود داشته باشد.

ایجاد با منوی "View" آغاز می شود. در آن باید موقعیت "Macros" و دستور "Record Macro" را انتخاب کنید. در مرحله بعد، باید نامی را بدون فاصله مشخص کنید، به عنوان مثال، "Format" (اگر کد مسئول تغییر قالب متن یا سلول ها باشد).

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

نحوه فعال کردن ماکروها در اکسل

برای گنجاندن یک ماکرو ضبط شده در اسناد دیگر، باید پس از پاک کردن جدول، آن را ذخیره کنید. قالب سند باید روی XLTM (که از ماکروها پشتیبانی می کند) تنظیم شود.

دفعه بعد که به سند دسترسی پیدا کردید، برای فعال کردن ماکروها در اکسل، ابتدا باید استفاده از آنها را در نوار بالا باز شده فعال کنید. سپس باید داده هایی را که توسط ماکرو ویرایش می شود از یک جدول CSV معمولی وارد کنید. در پایان وارد کردن، باید دوباره به منوی "View" بروید، "Macros" را در آنجا انتخاب کنید و نام کدی که ساخته اید را در لیست پیدا کنید. در پایان، روی دکمه "Run" کلیک کنید. پس از آن، مشاهده خواهید کرد که چگونه برنامه طبق الگوریتمی که قبلاً مشخص کرده اید، محاسبات را انجام می دهد و سند را قالب بندی می کند.

چگونه کد برنامه ماکرو را ببینیم؟

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

برای انجام این کار، باید دستورات زیر را به صورت متوالی اجرا کنید:

برگه "View" را گسترش دهید؛
. "Macros" را باز کنید؛
. نام ماکرو مورد نیاز را انتخاب کنید.
. با استفاده از دکمه "تغییر" یک پنجره را با کد آن فراخوانی کنید.

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

نحوه شروع یک ماکرو با کلیک سلول

فرض کنید می خواهید زمانی که سلول A1 انتخاب شده است، ماکرو اجرا شود. برای اینکه سلول انتخاب شده در اکسل ماکرو را اجرا کند، باید کد برنامه را به ماژول اول اضافه کنید:

کاربرگ فرعی خصوصی_SelectionChange(ByVal Target As Range)
اگر Target.Address = "$A$1" سپس: با module1 تماس بگیرید
پایان فرعی

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

از کجا می توانم کار با ماکروها را یاد بگیرم

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

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

مثال ها

کدهایی وجود دارند که به شما امکان می دهند کارهای مختلف را خودکار کنید. بنابراین، به عنوان مثال، ماکرو ردیف درج را در نظر بگیرید. به نظر می رسد این است:

Sub Macro1()

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

این سایت قبلاً چندین مقاله در مورد VBA دارد، از جمله، اما من فکر می کنم مفید است که لوسیون های کدی که اغلب استفاده می شوند در یک مکان نگهداری شوند.

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

Sub Makros1() Application.ScreenUpdating = 0 "کد شما Application.ScreenUpdating = 1 End Sub

فراموش نکنید که تابع را در انتهای ماکرو قرار دهید

چگونه پس از اجرای ماکرو انتخاب کپی را حذف کنیم؟

اگر محدوده ای را برای کپی و چسباندن در ماکرو خود اختصاص دهید. فراموش نکنید که انتخاب را در انتهای ماکرو غیرفعال کنید.

Sub Makros1() "کد شما Application.CutCopyMode = 0 End Sub

ماکروهای مفید چگونه آخرین سطر یا ستون یک محدوده را پیدا کنیم

این ساختار به شما کمک می کند تا به راحتی شماره آخرین سطر یا ستون محدوده پر شده را پیدا کنید. استفاده از آن در چرخه ها به خصوص راحت است، شما نیازی به تنظیم 1000 خط چرخه با حاشیه ندارید، خود اکسل متوجه می شود که انتهای محدوده با استفاده از این ساختار کجاست:

Sub makros1() Dim mLastRow As Long Dim nLastCol As Long mLastRow = Cells(Rows.Count, 1).End(xlUp).Row "تعداد آخرین سطر یک محدوده پر شده را پیدا می کند nLastCol= Cells(1, Columns.Count .End(xlToLeft ).Column "آخرین شماره ستون پر شده محدوده End Sub را پیدا می کند

علاوه بر این، من بلافاصله متغیر را به عنوان Long (طول 2 147 483 647) اعلام می کنم تا در موقعیتی قرار نگیرم که ممکن است عدد صحیح محبوب (32 767) برای جداول بزرگ کافی نباشد.

همچنین باید توجه داشته باشید که جستجو در ستون اول یا خط اول انجام شود، عدد مورد نظر را برای تعریف صحیح انتخاب کنید.

برای حلقه و وضعیت بررسی در حلقه

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

Sub makros1() Application.ScreenUpdating = 0 Dim mLastRow As Long Dim Kol As Long "Count Variable Dim i As Long "Loop Variable mLastRow = Cells(Rows.Count, 1).End(xlUp).Row Kol = 0 For i = 1 به mLastRow If Cells(i, 1).Value = "" سپس Kol = Kol + 1 End If Next i MsgBox Kol Application.ScreenUpdating = 1 End Sub

در اینجا از Msgbox نیز استفاده می شود؛ با این ویژگی می توانید داده ها را در یک پنجره جداگانه نمایش دهید. برای مثال من به این شکل خواهد بود:

شمارش زمان اجرای ماکرو

MsgBox خروجی زیر را می دهد:

ردیابی زمان اجرای ماکروها برای بهینه سازی بعدی بسیار راحت است.

اینها ماکروهای مفیدی هستند که من اغلب از آنها استفاده می کنم. من به تدریج این لیست را گسترش خواهم داد. تمام اتوماسیون موثر!