وقتی برای اولین بار به کدهای وی بی ای (VBA) نگاه میکنیم، ممکن است سطرهای کد، کمی پیچیده و ترسناک به نظر برسد. اما کدهای وی بی ای (VBA) دارای ساختار مشخصی است که با فهم این ساختار درک کدهای نوشته شده و همچنین نوشتن کدهای ویژوال بیسیک بسیار ساده تر خواهد شد.
فهم ساختار کدنویسی وی بی ای (VBA)
وی بی ای (VBA) نوعی زبان برنامه نویسی است که در گروه زبان های برنامه نویسی شی گرا قرار میگیرد. اما برنامه نویسی شئ گرا به چه معنی است؟ از یک مثال ساده شروع میکنیم. از اشیاء درون خانه شما بعنوان یک مثال شروع میکنیم. شما در خانه خود چند اتاق دارید که در هرکدام از اتاق ها چندین وسیله وجود دارد و هرکدام از این وسیله ها مجموعه ای از کارها را انجام میدهند. برای مثال اگر بخواهیم یخچال را در خانه شما باز کنیم می بایست به شکل زیر عمل کنیم:
خانه > آشپزخانه > یخچال > باز کردن
به همین شکل اگر قصد خوابیدن داشته باشید:
خانه > اتاق خواب > تخت خواب > خوابیدن
در نمونه اول، یخچال فقط در آشپزخانه قرار دارد و در اتاق خواب هیچ یخچالی وجود ندارد، به همین دلیل شما نمیتوانید از کد زیر استفاده کنید:
خانه > اتاق خواب > یخچال
اگر سعی کنید با رفتن به اتاق خواب در یخچال را باز کنید، خطا رخ خواهد داد. اکسل نیز از الگویی دقیقا شبیه به همین الگو در برنامه نویسی وی بی ای (VBA) استفاده میکند. اگر بخواهیم سلول A1 را در یک شیت خاص انتخاب کنیم می بایست از ساختار کد زیر استفاده کنیم:
Excel -> ExampleWorkbook.xlsx -> Sheet1 -> Cell A1 -> Select
اگر کدهای خود را با باز کردن یک سند ورد شروع کنید یا یک ورک شیت اشتباه را وارد کنید قادر به انتخاب کردن سلول مورد نظر نخواهید بود. در نتیجه ترتیب اشیاء در برنامه نویسی وی بی ای بسیار اهمیت دارد.
فیلم آموزشی درک ساختار برنامه نویسی در اکسل
اگر دوست دارید با برنامه نویسی شی گرا و ساختار برنامه نویسی وی بی ای بیشتر آشنا شوید می توانید این فیلم آموزشی رو ببینید.
فرض های وی بی ای
اگر یک ماکرو ضبط کنید و سپس با مراجعه به صفحه وی بی ای (VBA) کدهای آن را مشاهده کنید، متوجه خواهید شد که سطرهای کد ساخته شده با نمونه های بالا متفاوت است. دلیل این موضوع مجموعه فروضی است که وی بی ای (VBA) در نظر می گیرد.
یکبار دیگر به مثال یخچال برمیگردیم.شما تاکنون میدانید که یخچال در آشپزخانه قرار دارد. بنابراین اگر میخواهید در یخچال را باز کنید کافیست از کد زیر استفاده کنید:
یخچال > باز کردن
در بسیاری از موارد همین مقدار از کد کافیست و اکسل فرض میکند که منظور شما از یخچال، یخچال خودتان است و قصد باز کردن یخچال همسایه را ندارید. برای باز کردن یخچال همسایه می بایست از فرمول زیر استفاده کنید:
خانه همسایه > آشپزخانه > یخچال > باز کردن
به همین شکل VBA همیشه دارای فرض هایی است که شما را در کدنویسی یاری میکنند. برای مثال شما میتوانید به جای کدهای زیر:
Application.Workbooks("ExmpleWorkbook.xlsx").Sheets("Sheet1").Range("A1").Select
از کد کوتاه شده زیر استفاده کنید:
Range("A1").Select
نوع نرم افزار
وی بی ای (VBA) در اکسل فرض میکند ما از اکسل بعنوان نرم افزار میزبان استفاده میکنیم و نه word یا power point . بنابراین شما نیازی نیست در ابتدای کدهای خود نوع نرم افزار را مشخص کنیم. مگر اینکه بخواهیم از طریق برنامه نویسی در اکسل نرم افزارهای دیگری را کنترل کنیم که در مطالب آینده در مورد آن توضیح خواهیم داد.
ورک بوک
وی بی ای (VBA) در اکسل، همچنین فرض میکند که ورک بوک فعال که شما در حال کدنویسی در آن هستید، همان ورک بوکی است که میخواهیم از آن استفاده کنیم. بنابراین اگر میخواهید آدرس یک سلول را در کدهای خود استفاده کنیم، نیاز به مشخص کردن ورک بوک نیست و به صورت پیش فرض ورک بوک فعال مورد استفاده قرار میگیرد. با اینحال، اگر میخواهیم ورک بوکی غیر از ورک بوک فعال را در کدهای خود مورد استفاده قرار دهیم، می بایست در کدنویسی به آن اشاره کنیم.
ورک شیت
شبیه به ورک بوک، VBA ورک شیت فعال را نیز بعنوان میزبان در نظر میگیرد و شما برای استفاده از محدوده های ورک شیت فعلی نیاز به وارد کردن نام ورک شیت در ابتدای کدهای خود ندارید. اما برای استفاده از دیگر ورک شیت ها می بایست به شکل زیر عمل کنید:
- (“Sheets(“sheetname: با استفاده از این دستور به یک شیت با نام Sheetname اشاره خواهد شد.
- (Sheets(4: با این دستور به چهارمین ورک شیت از سمت چپ اشاره خواهد شد.
- ActiveSheet: این دستور به ورک شیت فعال اشاره میکند.
مثال:
اگر بخواهیم سلول A1 را از شیت فعال انتخاب کنیم به شکل زیر عمل میکنیم:
Range("A1").Select
اگر بخواهیم سلول A1 را از شیت چهارم یک ورک بوک به نام “Example.xlsx” انتخاب کنیم،کدهای ما به شکل زیر خواهد بود:
Workbooks("Example.xlsx").Sheets(4).Range("A1").Select
اگر بخواهیم شیت 1 از ورک بوکی که در حال کدنویسی در آن هستیم را انتخاب کنیم، از کدهای زیر استفاده میکنیم:
ThisWorkbook.Sheets("Sheet1").Range("A1").Select
فهم مشخصه های اشیاء در برنامه نویسی وی بی ای
مشخصه ها، صفت های اشیاء هستند. در خانه شما، اتاق ها، یخچال و دیگر وسیله ها دارای صفت هایی از قبیل وزن، طول، ارتفاع و … هستند. در VBA نیز اشیاء دارای چنین مشخص هایی هستند.
برای مثال اگر بخواهیم ارتفاع ردیف 9 را که یکی از مشخصه های آن است تغییر دهیم به شکل زیر عمل میکنیم:
Rows(9).RowHeight = 30
برای تغییر رنگ سلول به صورت زیر کدنویسی میکنیم( 65535 کد رنگ زرد است):
Range("A1").Interior.Color = 65535
مشخصه های اشیاء با هم یکسان نیستند و هر شئ مشخصه های خاص خود را دارد. برای مثال ممکن است مشخصه هایی که یخچال دارد، کمد نداشته باشد.
متد (method) در برنامه نویسی VBA
متد فعالیت هایی است که یک شئ میتواند انجام دهد. برای مثال شما میتوانید یخچال را باز کنید ولی نمیتوانید تلویزیون را باز کنید. هر عملی که میتوانید بر روی یک شئ انجام دهید، متد آن شئ محسوب میشود. درست شبیه به مشخصه ها، متدها نیز مخصوص هر شئ هستند و از یک شئ به یک شئ دیگر با هم تفاوت دارند.
انتخاب یک سلول، یک متد محسوب میشود:
Range("A1").Select
حذف یک ورک شیت نیز متد است:
Worksheets(4).Delete
مطالب عنوان شده در این قسمت ممکن است کمی نسبت به قسمت های اولیه پیچیده تر باشد ولی با رعایت قانون طلایی عنوان شده در ابتدای این دوره و استفاده عملی از هزکدام از این مفاهیم به درک عمیقتری نسبت به آنها خواهید رسید. البته نیازی به حفظ کردن تمام اشیاء و مشخصه ها و متدها وجود ندارد و اکسل ابزارهایی را برای تسهیل کار در اختیار شما قرار داده است:
Object Browser
با مراجعه به پنجره Visual basic editor آیکن نشان داده شده در تصویر زیر را انتخاب کنید:
ابزار object browser باز خواهد شد. در این ابزار لیست کاملی از اشیاء، مشخصه ها و متدها نمایش داده میشود.
خاصیت Autolist
این ابزاربه صورت اتوماتیک متدها و مشخصه ها اشیاء را همزمان که کدهای خود را تایپ میکنید نمایش میدهد. شما میتوانید در حین کدنویسی برای سادگی کار، بر روی پیشنهادهای این ابزار دابل کلیک کنید. در صورتی که این ابزار غیر فعال است میتوانید به شکل زیر آن را فعال کنید:
Tools -> Options -> Editor -> Auto List Members
البته اگر در درک مفاهیم بالا دچار سردرگمی شدید، جای هیچ نگرانی نیست. در قسمت های آینده بارها به این مفاهیم اشاره خواهیم کرد و به تدریج با این مسائل آشنا خواهید شد. برای مطالعه مطالب قبل در زمینه برنامه نویسی VBA در اکسل میتوانید به صفحه زیر مراجعه کنید:
دوره رایگان برنامه نویسی VBA در اکسل
سلام استاد دایی
در این قسمت سوالی مطرح کردم که بعد از گذشت یک هفته جواب داده نشده و حذف گردید
دوباره عنوان می کنم که ان شاء الله وقت کردید محبت کنید جواب بدین
در این آدرس سایت شما که یک گیف هست و فاکتوری رو نمایش می ده که با زدن شماره فاکتور ردیفها و جمع کل به طور خودکار به همراه قالب بندی آپدیت میشن مشکل من فقط قسمت جمع کل هست! چجوری جمع کل با اضافه و کم شدن ردیفهای فاکتور بالا و پایین می ره؟!
https://skillpro.ir/%D8%AF%D9%88%D8%B1%D9%87-%D8%A2%D9%85%D9%88%D8%B2%D8%B4-%D9%81%D8%B1%D9%85%D9%88%D9%84-%D9%86%D9%88%DB%8C%D8%B3%DB%8C-%D9%BE%DB%8C%D8%B4%D8%B1%D9%81%D8%AA%D9%87-%D8%A7%DA%A9%D8%B3%D9%84/
سلام
باید با استفاده از تابع if شرایط سلول های کناری رو بررسی کنید و اگر ستون ردیف خالی بود، میاد و جمع کل رو نشون میده در غیراینصورت محاسبه رو انجام میده.
استاد من این فرمول آرایه ای رو نوشتم برای برگردانی آیتمهای فاکتور که به درستی عمل می کنه ولی این شرطی که شما می فرمائید رو درست متوجه نمیشم لطف می کنید بیشتر توضیح بدین؟!
=IFERROR(INDEX($A$1:$K$1163,SMALL(IF($A$2:$K$1163=$A$1171,ROW($A$2:$K$1163),””),ROW(F1)),6),””)
متاسفانه فرصت تحلیل فرمول شما رو ندارم، فرض کنید برای انجام محاسبات از یه تابع vlookup ساده استفاده کردید.
=if(A5<>"",vlookup(-----),sum(------))
استاد نمی خواستم شما دقیقا فرمول درخواستی منو بنویسید(لقمه آماده!) مشکلم اینه که درفرمول آرایه ایم شرط if یه بار اعمال شده وقتی if چک کردن سطوررو می نویسم تمام عملیات بهم می خوره! ازتون فقط راهنمائی می خوام
از اینکه جواب کامنتهای حقیر رو میدین بینهایت ممنونم
استاد اگر وقت ندارید فرمولی که خودتون در گیف استفاده کردید رو بنویسید خودم روش کار می کنم تا مصدع اوقاتتون نشم
تشکر فراوان
فرمول متن «مبلغ کل فاکتور»:
=IF(A14=$D$1,$M$13,IF(A15=$D$1,$M$11,IF(A16=$D$1,$M$12,IF(A17=$D$1,$M$10,IF($A18<=$D$1,OFFSET(Sheet4!$B$1,MATCH(Sheet5!$C$5,Sheet4!$G:$G,0)+$H18,E$9),"")))))
فرمول محاسبه مبلغ کل فاکتور:
=IFERROR(IF(E18="مبلغ قابل پرداخت",ROUND(F15-(F15*F16)+F17,0),IF(E18="تخفیف روی فاکتور",VLOOKUP($C$5,Sheet6!$A$1:$F$20,5,FALSE),IF(E18="هزینه پیک",VLOOKUP($C$5,Sheet6!$A$1:$F$20,6,FALSE),IF(E18="مبلغ کل فاکتور",SUM($F$10:F17),IF($A18<=$D$1,OFFSET(Sheet4!$B$1,MATCH(Sheet5!$C$5,Sheet4!$G:$G,0)+$H18,F$9),""))))),"فاکتور موجود نیست")
ممنون از وقتی که گذاشتید
چرا در فرمول به ستون m اشاره شده؟
دوم اینکه در شیتهای4و5و6شما چه اطلاعاتیه که در فرمول بهش اشاره کردید
ساختار فرمول نویسی به نحوه ذخیره سازی و فراخوانی داده ها بستگی داره و هر کسی با توجه به دانش خودش کار رو انجام میده. اینکه چه ساختاری ایجاد شده و نحوه فراخوانی چطور هست یک کلاس هست که در بحثش در اینجا نمی گنجه.
استاد دایی اگر امکانش هست می خواستم این محصول رو ازشما خریداری کنم لطفا راهنمائی بفرمائید. بینهایت سپاسگزار و قدردان محبتهای شما هستم
سلام
از منو سایت گزینه «محصولات» رو انتخاب کنید.
متاسفانه پیام من حذف شد
عرض کردم این محصول در سایت شما موجود نیست منظورم همین فاکتوریه که به شکل گیف نمایش داده می شه
https://skillpro.ir/%D8%AF%D9%88%D8%B1%D9%87-%D8%A2%D9%85%D9%88%D8%B2%D8%B4-%D9%81%D8%B1%D9%85%D9%88%D9%84-%D9%86%D9%88%DB%8C%D8%B3%DB%8C-%D9%BE%DB%8C%D8%B4%D8%B1%D9%81%D8%AA%D9%87-%D8%A7%DA%A9%D8%B3%D9%84/
خیر موجود نیست.
سلام و خسته نباشید
ستون بدهکار دفترم رو با ستون بدهکاربانک در تاریخ خاص مقایسه کردم و مغایرتها رو پیدا کردم
در تاریخ یکم ماه در دفتر عدد 100 و در همان تاریخ در بانک عدد120 مغایرت پیدا شده می خوام اختلاف این دو تا در یه ستون نوشته بشه
یعنی در تاریخ سوم خرداد من با بانک20ریال مغایرت دارم در تاریخ فلان مقدار ایکس و الی آخر
سلام
در حالت ساده می تونید یک لیست از تاریخ ها در یک ستون به صورت یکتا ایجاد کنید، در مقابل هر تاریخ با تابع vlookup (اگر برای هر تاریخ یک مقدار دارید) یا با تابع sumifs اگر برای هر تاریخ چند مقدار دارید، از هر دو جدول مقدارها رو فراخوانی کنید. بعد توی ستون چهارم با هم مقایسه کنید و اختلاف بگیرید.
یا اگر مشکلی با تغییر دیتای فایل ها ندارید، در مقابل تاریخ هر فایل با تابع vlookup مقدارها رو پیدا کنید و بعد اختلاف بگیرید.
استاد دایی سلام و خسته نباشید
یه فایل اکسل از یه سایت دانلود کردم در قسمت کد نویسی هیچ کدی نداشت در خود شیتها هم هیچ فرمولی موجود نبود!در صورتیکه یه یوزر فرم داشت که وقتی اجراش می کردی اطلاعات اشخاص رو در یه شیت ثبت ,تصحیح یا حذف می کرد!! چطور همچین چیزی ممکنه؟! ضمنا یه فایل اکسس هم کنارش دانلود شد. اگه فایل اکسس همه کارس این فایل اکسل چی می گه؟!
سلام
باید فایل رو ببینم.
فایل رو فرستادم
مشکل اصلی و مهم اینه که وقتی در شیت3فاکتور اصلاحی درست کردم چجوری دیتابیس اصلاح بشه؟
همون طور که توی شیت دیتا به جدول منتقل میشه، اول باید سطرهای مربوط به فاکتور قبلی رو حذف کنید و بعد فاکتور جدید رو اضافه کنید.
سلام و خسته نباشید
در شیت1فاکتوری هست که وقتی سیو می کنم به شکل دیتابیسی در شیت2ذخیره میشه
آیا برای اینکه از بین انبوه فاکتورها یکی رو اصلاح کنم راهی هست؟راه خودم اینه که در شیت3 یه فاکتور درست کنم و با وی لوک آپ یکی یکی آیتمها رو فراخوانی کنم ولی به نظرم خیلی حرفه ای نیست! نظر شما چیه؟!
سلام
اگر توی اکسس بود به راحتی می تونستید یک فرم برای اینکار طراحی کنید. توی اکسل همین راهی که گفتید ساده ترین راه هست.
سلام و خسته نباشید
یه کد نوشتم اکسل در حالت فول بازبشه و قابل تغییر هم نباشه برای کاربر ولی با زدن کلیدESC هر چی رشتم پنبه میشه!!!
با دستور زیر می تونید دکمه esc رو غیر فعال کنید.
برای فعال کردن مجدد این دکمه از دستور زیر استفاده کنید.
ممنون و متشکر
سلام امیر عزیزی خسته نباشی
یه کد هست تفاوت دو عدد رو در statusbar می نویسه
چجوری بهش بگم اعداد رو با کاما از هم جدا کنه؟(سه رقم سه رقم)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Cells.Count = 2 Then
On Error Resume Next
If WorksheetFunction.Count(Range(Selection.Address)) = 2 Then
Application.StatusBar = “The difference is ” & _
WorksheetFunction.Max(Range(Selection.Address)) _
– WorksheetFunction.Min(Range(Selection.Address))
Else
Application.StatusBar = “The difference is ” & _
WorksheetFunction.Max(Range(Selection.Address))
End If
Else
Application.StatusBar = False
End If
End Sub
سلام
برای فرمت کردن اعداد می تونید از تابع Text استفاده کنید. یک متغییر تعریف کنید و محاسبه مورد نظر رو توی اون انجام بدید. مثلا اسم متغییر diffValue باشه. بعد از کد زیر استفاده کنید:
سلام میشه لطف کنید بگین این کد دقیقا باید کجا نوشته بشه؟
برای محاسبه ای که میخواید انجام بدید اول یک متغییر تعریف کنید. مثلا اختلاف دو سلول یا بیشترین مقدار دو سلول یا …
بعد هر کجا که میخواید نمایش بدید (توی استوس بار یا فرم یا …) به جای اینکه محاسبه رو نمایش بدید، متغییر که محاسبه رو توش انجام دادید به صورت بالا بنویسید.
امیرجان عذر خواهی می کنم! تلاشم بی نتیجه است!! اعداد سه رقم سه رقم از هم جدا نمیشن
از هر سایتی که می تونید یک دوره برنامه نویسی vba تهیه کنید و ببینید تا با مفاهیم برنامه نویسی آشنا بشید و بتونید کارهایی که میخواید رو انجام بدید.
برای شروع این سایت چطوره؟
https://learn.microsoft.com/en-us/office/vba/library-reference/concepts/getting-started-with-vba-in-office
اگر فیلتر شکن دارید توی یوتیوب می تونید دوره های رایگان بهتری پیدا کنید. سایت آفیس و سایت های مشابه بیشتر حالت منبع هستن تا سایت های آموزشی. اگر وقت داشته باشید و از اول بتونید مطالب رو بخونید سایت های ارزشمندی هستند ولی معمولا ترتیب یادگیری مناسبی ندارند.
خودم هم توی یوتوب چندتایی دیدم ولی محبت کنید چند تاشون از یوتیوب معرفی کنید که برای من که مبتدی هستم گمراه کننده نباشه بالاخره شما تجربه بیشتری دارید و بهتر می دونید از کجا شروع کنم بهتر و مفیدتره
سپاس فراوان
سلام آموزش هاتون بسیار عالی هستن اما سایتتون بسیار ابتداییه و این جالب نیست. در همین صفحه، قسمت دوم هر خط دیده نمیشه و لذا جمله کامل دیده نمیشه. بازم تشکر
سلام
خوشحال میشیم برای بهتر شدن سایت اگر شما در این زمینه تخصصی دارید به ما کمک کنید.