فيجوال بيسك / إكسل - تطبيقات متقدمة
ناقشتُ في عدة مقالات سابقة فوائد الفيجوال بيسك ومبادئ استخدامه. في هذه المقالة أحاول استعراض كيفية استخدام أدوات التحكم في صفحة إكسل مع بعض البرمجة.
افترض أنك تريد أن تستعرض بيانات المبيعات والمصاريف الشهرية -كالتي بالجدول أدناه - على صورة رسم بياني يربط بين متغير واحد من هذه المتغيرات مع الزمن (الشهور). يمكننا أن نرسم العديد من الرسومات البيانية عن طريق إكسل ولكن هذا يجعل شكل الصفحة غير مريح ويجعل التنقل بين الرسومات أمرا عسيرا
يمكننا استخدام أدوات التحكم مع برنامج فيجوال بيسك صغير للقيام بنفس المهمة بشكل جميل ويسير.
ابدأ برسم العلاقة بين الشهور ومبيعات المنتج الأول
ابدأ بتسجيل ماكرو ثم قم بتغييرمصدر البيانات Source Data للرسم البياني بحيث تكون العلاقة بين الشهور ومبيعات المنتج الثاني. أوقف تسجيل الماكرو ثم تصفح هذا الماكرو في محرر فيجوال بيسك VBE فتجده الآتي
Sub Macro1()
ActiveSheet.ChartObjects(”Chart 1″).Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets(”Sheet1″).Range(”B4:B16,E4:E16″), _
PlotBy:=xlColumns
End Sub
عن طريق تسحيل هذا الماكرو نستطيع التعرف على الأوامر اللازمة في فيجوال بيسك لتغيير المتغيرات في الرسم البياني. وهذا ما سوف نحتاجه في خطوة لاحقة.
لنبدأ في استخدام أدوات التحكم وفيجوال بيسك لكي نستطيع استعراض العلاقة بين أي متغير والشهور حسب رغبتنا
تأكد أنك في حالة التصميم Design Mode ثم افتح قائمة أدوات التحكم كما فعلنا في المقالة السابقة
اختر زر اختيارات Option Button ثم قم بوضعه في صفحة إكسل بنفس الطريقة المستخدمة في رسم الأشكال
قم بنسخ هذا الزر وقم بعمل أربع نسخ منه بحيث يكون لديك خمس زر اختيارات Option Buttons
قف على أول زر اختيارات واضغط على الفأرة يمينا ثم اختر خواص Properties. ابحث عن خاصية Caption أي اسم الزر وقم بتغيير اسم الزر إلى مُسمى المتغيرات في الجدول أعلاه بحيث تبدأ بـ Sales of Product A وتنتهي بـ Cost
لاحظ أن الخلفية الحمراء التي في الشكل أعلاه هي لون خلايا إكسل. يمكنك تغييرها للون غير اللون الأبيض لكي يكون الشكل واضحا إن أردت
نريد الآن أن نقوم بكتابة ماكرو يقوم بتغيير المتغير في الرسم البياني عند اختيار كل زر من هذه الأزرار.
قم بالضغط مرتين على زر الاختيارات Double Click -وأنت في حالة التصميم- فيظهر لك محرر فيجوال بيسك VBE
هذا الماكرو يتم تنفيذه عند اختيار هذا الزر Click كما هو واضح من عنوانه. نود الآن كتابة جملة مشابهة لتلك التي حصلنا عليها عندما قمنا بتسجيل ماكرو - في أعلى الصفحة. ولكن سوف نقوم باختصاره وتعديلها كالتالي
ChartObjects(”Chart 1″).Chart.SetSourceData Source:=Sheets(”Sheet1″).Range(”B4:B16,c4:c16″)
سوف نقوم بكتابة نفس الجملة لكل زر اختيارات Option Button ولكن مع تغيير مصدر المعلومات في كل حالة حسب العمود الذي نرغب في إظهاره عند اختيار كل زر فنحصل على البرنامج التالي
Private Sub OptionButton1_Click()
ChartObjects(”Chart 1″).Chart.SetSourceData Source:=Sheets(”Sheet1″).Range(”B4:B16,c4:c16″)
End Sub
Private Sub OptionButton2_Click()
ChartObjects(”Chart 1″).Chart.SetSourceData Source:=Sheets(”Sheet1″).Range(”B4:B16,d4:d16″)
End Sub
Private Sub OptionButton3_Click()
ChartObjects(”Chart 1″).Chart.SetSourceData Source:=Sheets(”Sheet1″).Range(”B4:B16,e4:e16″)
End Sub
Private Sub OptionButton4_Click()
ChartObjects(”Chart 1″).Chart.SetSourceData Source:=Sheets(”Sheet1″).Range(”B4:B16,f4:f16″)
End Sub
Private Sub OptionButton5_Click()
ChartObjects(”Chart 1″).Chart.SetSourceData Source:=Sheets(”Sheet1″).Range(”B4:B16,g4:g16″)
End Sub
يُمكننا الآن تجربة عمل هذه الأزرار والتي سوف تتسبب في تغير محتويات الرسم البياني حسب الاختيار فعندما تختار زر التكلفة Cost تجد أن الرسم البياني يرسم العلاقة بين التكلفة والشهور وعندما تختار المنتج الثاني تجد أن الرسم البياني يرسم العلاقة بين مبيعات المنتج الثاني والشهور.
يمكنك تصور عِظم فائدة هذا البرنامج عندما يكون لديك الكثير من المتغيرات والتي تريد تحليلها عن طريق الرسم البياني. لاحظ أنك تستطيع بسهولة استخدام نفس البرنامج مع أي ملف آخر بإجراء بعض التعديلات البسيطة.
هذا المثال أوضح لنا كيفية برمجة أدوات التحكم وذلك بكتابة ماكرو مصاحب لبعض الأحداث التي تحدث على هذه الأدوات. في هذا المثال اكتفينا بماكرو يعمل عند الضغط Click على الزر Option Button. وهناك الكثير من الأحداث الأخرى التي قد نحتاج في أمثلة أخرى أن نبني لها ماكرو مصاحب لحدوثها.
تطبيق 2:
لو افترضنا في المثال السابق أن عدد المتغيرات كبير جدا - أكثر من 10 على سبيل المثال- فإن أزرار الاختيار Option Buttons تصبح كثيرة جدا بشكل مزعج. دعنا نُجرب استخدام الصندوق المتدلي في هذه الحالة.
افتح قائمة أدوات التحكم واختر الصندوق المتدلي Combobox وقم برسمه كالمعتاد. اضغط على الفأرة يمينا واختر خواص Properties.
سنقوم بتحديد مصدر معلومات الصندوق المتدلي عن طريق خاصية ListFillRange. ولكن يجب أن يكون مصدر المعلومات عمودا وليس صفا لأن هذه الخاصية ListFillRange لا تقبل أن تكون صفا. لذلك سنقوم بنسخ وإعادة لصق الخلايا C4:G4 في أي عمود وليكن العمود Z عن طريق
Copy…Pate /Transpose
كما تعلم فإن Transpose تقوم بنسخ الصف في عمود وبالتالي يكون لدينا نفس المعلومات في عمود.
نفترض أننا وضعنا الخلايا المنسوخة في العمود Z فقم بتغييرخاصية ListFillRange إلى Z1:Z5.
عند الخروج من حالة التصميم يكون شكل الصندوق المتدلي عند فتحه كالتالي
علينا أن نكتب ماكرو يَقوم بتغيير الرسم البياني بناء على الاختيار في الصندوق المتدلي. لابد أن نتعرف أولا على رقم الاختيار بمعنى أنه الاختيار الأول أو الثاني أو الثالث….من القائمة وبالتالي يمكننا تحديد رقم العمود المناظر لهذا الاختيار. بعد ذلك يتم تغيير مصدر المعلومات كما في المثال السابق
Private Sub ComboBox1_Change()
j = 3 + ComboBox1.ListIndex
myrange = Sheets(”Sheet1″).Range(Cells(4, j), Cells(16, j)).Address
ChartObjects(”Chart 1″).Chart.SetSourceData Source:=Sheets(”Sheet1″).Range(myrange)
End Sub
المتغير J يتم حسابه بإضافة ثاثة لرقم الاختيار من الصندوق المتدلي. رقم الاختيار يتم تحديده عن طريق Combobox1.ListIndex والذي يبدأ من الصفر ثم واحد وهكذا أي أنه عند اختيار مبيعات المنتج الأول تكون قيمة الاختيار هي صفر وبالتالي قيمة المتغير J هي 3. يتم إضافة ثلاثة لأن الاختيار الاول مناظر للعمود الثالث في صفحة إكسل وهكذا.
بهذه الطريقة أمكننا استخدام الصندوق المتدلي Combobox بدلا من أزرار الاختيارات Option Buttons وكما ترى فكلا منهما له مزاياه.
تطبيق 3:
لنفترض أننا نريد - في المثال السابق- أن يكون لدينا وسيلة لإظهار وإخفاء الرسم البياني
اضغط على أيقونة حالة التصميم Design Mode
افتح قائمة أدوات التحكم
أضف زر أوامر Command Button ثم أضف واحد آخر فيكون لدينا اثنان Command Button
قف على أحدهما واضغط يمينا على الفأرة ثم اختر خواص Properties
قم بتغيير مسمى الزر Caption الأول إلى View ثم اختر الزر الثاني وغيِّر المسمى إلى Hide
اضغط مرتين Double Clickعلى الزر الأول لكي تكتب الماكرو المصاحب للضغط عليه ثم اكتب السطر التالي داخل الماكرو فيكون الماكرو كالتالي
Private Sub CommandButton1_Click()
ChartObjects(”Chart 1″).Visible = -1
End Sub
السطر الأوسط يعني أن الرسم البياني ظاهرا. الآن قم بعمل نفس الشيء مع الزر الآخر ولكن الماكرو سيكون كالتالي
Private Sub CommandButton1_Click()
ChartObjects(”Chart 1″).Visible = 0
End Sub
القيمة 0 تعني أن الرسم البياني غير ظاهر
اخرج من حالة التصميم بالضغط على أيقونة Exit design Mode
قم بتجربة عمل الزرين وينبغي أن تجد أن View تُظهر الرسم بينما Hide تُخفي الرسم
تطبيق 4:
حاول تنفيذ عملية إظهار وإخفاء الرسم البياني عن طريق صندوق اختيار Check box بدلا من زر الأوامر Command Button
تحول إلى حالة التصميم بالنقر على الأيقونة الخاصة بحالة التصميم ثم افتح قائمة أدوات التحكم
أضف صندوق اختيار إلى صفحة إكسل
قم بتغيير مسمى الصندوق Caption من Checkbox1 إلى View / Hide
اضغط مرتين لكتابة الماكرو المصاحب لاختيار وعدم اختيار صندوق الاختيار وهو كالتالي
Private Sub CheckBox1_Click()
m = CheckBox1.Value
If m = True Then
ChartObjects(”Chart 1″).Visible = -1
Else
ChartObjects(”Chart 1″).Visible = 0
End If
End Sub
وأترك للقارئ فهم خطوات هذا الماكرو
بذلك نكون قد استعرضنا العديد من الأدوات وكيفية برمجنها مع برنامج إكسل. هناك أدوات أخرى والتي يمكنك تجرتها وفهم وظائفها. كما ترى فإن الاستخدام البسيط والسريع لفيجوال بيسك يساعدنا على دراسة المتغيرات بسهولة وهو ما ينعكس على قدرتنا على اتخاذ القرارات. حاول التفكير في الأمور التي تمنيت لو استطعت تنفيذها في إكسل بشكل أكثر كفاءة ولم تستطع. حاول استخدام هذه الأداوت لتنفيذها.
يمكنك تنزيل الملف من الرابط التالي:
رابط الملف
مواضيع ذات صلة في هذا الموقع:
فائدة تعلم فيجوال بيسك
فيجوال بيسك من إكسل- مقدمة
فيجوال بيسك من إكسل - تطبيقات
فيجوال بيسك / إكسل - أدوات التحكم
shamsi قال,
سبتمبر 10, 2007 @ 3:03 م
بارك الله فيكم … والله يرعاكم .
خالد صديق قال,
سبتمبر 15, 2007 @ 10:08 م
الي الامام
وبارك الله فيكم وجعل هذا العمل في ميزان حسناتكم
ابومساعد قال,
أكتوبر 3, 2007 @ 4:01 ص
لدي عمود في اكسل وبه معادله تنفذ عند تحقق شرط.. للتوضيح.. عمود A و عمود B مثلا.. وكل خليه من العمود الاول فيها معادلة تنتج ناتجا وتضعه في القيمه.. اريد اذا وضعت القيمه في العمود الاول اقرأها مرة واحده فقط واضعها في الخليه التي تقابلها في العمود الثاني.. بحيث أني لا اريد ان اقرأهها اذا تغيرت .. اعطيك مثال اكثر وضوح.. لدي ثلاثة اعمده في الاكسل..
العمود الاول: فيه قيمه يتم حسابها كأن تقول.. خلايا العمود الاول فيه المعادلة: رقم ويتغير كل لحظه
العمود الثاني: فيه معادلة شرطيه اذا تحقق الشرط يضع فيها كلمة “نعم”
السؤا: تم وضع نعم في خليه العمود الثاني.. اريد ان اقرأ القيمه التي في العمود الاول واضعها في العمود الثالث هذه المره فقط أي اول مايتحقق الشرط وليس كل مره يتم فيها التحديث اقوم بوضع قيمه أخرى وكاتعلم فإن القيم تتغير في كل مره يتم التحديث فهل هذا ممكنا؟
تحياتي لكم
يحيى قال,
أكتوبر 11, 2007 @ 6:32 ص
جزاك الله كل خير
يا ريت يا اخي لو تكمل موضوعك وتعمله على نظام دورة كاملة نتعلم فيها من الصفر
اربع مواضيع جميلة جدا
و جزاك الله كل خير
سامح قال,
أكتوبر 11, 2007 @ 8:37 ص
الأستاذ يحيى
شكرا على هذا التشجيع. إن شاء الله احاول إضافة أكثر من مقالة لهذا الموضوع
شكرا
شريف حسن قال,
أكتوبر 11, 2007 @ 8:50 ص
الجملة المكتوبة لاعداد الرسم البيانى بها خطا لانى كل ماجى ادخلها يتحول لون الجملة الى اللون الاحمر وارجو الرد ضرورى وبسرعة
سامح قال,
أكتوبر 11, 2007 @ 9:00 ص
الأستاذ شريف حسن
الجملة صحيحة ولكن يبدو أن عملية النسخ من الموقع إلى محرر فيجوال بيسك يحدث بها بعض التغيير. لقد قمت بتجربة ذلك الآن فوجدت ان علامة ” التي تأتي قبل بعد كل اسم في هذه الجملة تتغير، فالأولى تتحول إلى شيء يشبهها ولكنه ليس في الحقيقة ” والثانية تتحول إلى علامة استفهام أي ؟
أنصحك ان تقوم بمراجعة كل qoutation marks “…” وتعيد كتابتها بنفسك في محرر فيجوال بيسك
مع الأسف لا يمكنني وضع ملفات إكسل في هذا الموقع وإلا كنت وضعت الملف هنا
رجاء أن تخبرني إن نجحت في تشغيل البرنامج
شكرا
سامح قال,
أكتوبر 11, 2007 @ 11:44 ص
الأستاذ أبو مساعد
يبدو انني نسيت ان اجيب على سؤالك. معذرة ….إن شاء الله أحاول الإجابة عليه قريبا
شكرا
السيد قال,
أكتوبر 13, 2007 @ 7:55 م
قام محاسب سابق فى الشركة التى اعمل بها بعمل صفحة ايكسيل لحساب مديونية العميل فى الحد اعمتها يكتب مسحوبات العميل وفى الأخر يكتب سدادة وعن طريق الجمع والطرح يحصل على رصيدة ثم يضغط على الماكرو فيمسح ماكتبة فى التحصيل ومسحوبات العميل دون ان يتغير الرصيد وصفحة اخرى للتاخيرات الن كتيت التاخير جمع على التاخيرات السابقة وعند تشغيل الماكرو تمسح تاخرات اليوم ولا ينقص رصيد التاخيرات وهذا الزميل لا يريد تعليمى ذلك وارجوا منكم تعلمونى ذلك وجزاكم اللة كا خير رجاء ارسال الرد على الأميل الخاص بى وهذا للأهمية
شكرا وجزاكم الله كل خير
سامح قال,
أكتوبر 25, 2007 @ 5:34 م
الأستاذ السيد
أعتذر عن تاخر الرد
يمكنك ان تقرأ محتويات الخلية وتكتبها في نفس الخلية
Sub countdelete()
Worksheets(”sheet1″).Cells(5, 3) = Worksheets(”sheet1″).Cells(5, 3)
End Sub
ولكن هذا يمسح المعادلة ولابد من كتابتها عند الرغبة في القيام بالحساب مجددا
ولكن الأبسط من ذلك هو أن تقوم بعمليات الجمع عن طريق VB فتقوم الجمع والطرح ثم كتابة الناتج ثم تقوم بمسح ما كتبته إن شئت
Sub countdelete()
Worksheets(”sheet1″).Cells(5, 3) = Worksheets(”sheet1″).Cells(3, 3) - Worksheets(”sheet1″).Cells(4, 3)
Worksheets(”sheet1″).Cells(4, 3).Clear
Worksheets(”sheet1″).Cells(3, 3).Clear
End Sub
فهذا يطرح خلية من أخرى ويكتب الناتج في الخلية التي تحتها
أرجو ان يساعدك هذا. وحاول تطبيق الأمثلة الموجودة في المقالات لكي تستطيع بناء ماكرو بنفسك
شكرا
نافذ جلال قال,
ديسمبر 27, 2007 @ 7:09 ص
السلام عليكم ورحمة الله وبركاته
كيف اضع Combobox داخل الخلية عندما نختار الخلية تظهر combo مثالا نختار الخلية A1 تظهر علامة combo وعلى A2 تظهر combo ايضا يعني داخل الخلية لا على ظهر الخلايا والسلام عليكم ورحمة الله وبركاته 0
سامح قال,
ديسمبر 27, 2007 @ 8:31 ص
الأستاذ نافد
وعليكم السلام ورحمة اللله وبركاته
لا اعرف. هناك طريقة لذلك لا أتذكرها ولكنه يمكنك ان تجعل الخلية تأخذ نفس قيمة الصندوق المتدلي ويالتالي يصبحان كما لو كانا نفس الشيء
شكرا
عدي قال,
يناير 22, 2008 @ 7:17 ص
السلام عليكم - عملت في اكسل نظام لحساب رواتب المنتسبين وهو يعمل بشكل جيد في هذه السنة حصلت تعليمات جديده وهي اضافة مخصصات للموظف حسب شييئين هما حدود راتب الموظف وكذلك اذا كان لديه اطفال وكم عدد الاطفال ، حيث عند راتب 200-209 يضاف مخصص للذي ليس لديه طفل 1 دينار وللذي لديه طفل 3 دنانير وللذي لديه طفلان 6 دنانير وهكذا الى الطفل العاشر ، ثم تعاد العملية على حدود الراتب من 210-219 وكذلك مقارنة مع الاطفال ، ثم تستمر الى حدود الراتب من 900-919 مع المقارنة مع الاطفال ، وهي ضمن جدول محدد فهل ممكن المساعدة في كتابة مايكرو يحل هذه المسالة ، او المساعدة في اخباري ب دالة تعمل هذا العمل في اكسل ، مع الشكر والامتنان الجزيل لمن يحاول المساعدة في هذا الامر .
سامح قال,
يناير 22, 2008 @ 8:04 م
الأستاذ عدي
نعم يمكن استخدام فيجوال بيسك في هذه الحالة.
أرسلت لك الرد على بريدك الإلكتروني. أرجو ان يفيدك
شكرا
ايمن محمد امبارك قال,
فبراير 28, 2008 @ 11:55 ص
الرجاء تحميل مرفقات للشغل ده
سامح قال,
فبراير 28, 2008 @ 12:41 م
الأستاذ أيمن محمد
المشكلة هي أن هذه ملفات إكسل وهذا الموقع (wordpress.com) لا يسمح بتحميل ملفات إكسل. أتمنى ان أجد حلا لهذا الأمر ربما بتحميل الملفات في موقع آخر وإضافة الرابط هنا
شكرا
سامح قال,
مارس 1, 2008 @ 4:23 م
الأستاذ أيمن
الآن يمكنك تحميل الملف من خلال الرابط في نهاية المقالة. شكرا على اقتراحك
شكرا
غصون قال,
مارس 6, 2008 @ 7:22 م
كيف احمل هذا البرنامج
سامح قال,
مارس 7, 2008 @ 4:20 ص
الأستاذ غصون
اضغط على رابط الملف ثم قم بتنزيله ثم قم بتغيل الماكرو
إن كانت هناك صعوبة في تشغيل الماكرو فارجع إلى المقالة التالية
http://samehar.wordpress.com/2007/08/17/a170807/
شكرا
على ايوب قال,
أبريل 8, 2008 @ 10:35 ص
معادله لتحويل العمله من دولار الى مصرى ومن يورو الى مصري مع التقريب لاكبر عدد صحيح من اليورو والدولار( هام جدا)
سامح قال,
أبريل 12, 2008 @ 10:03 ص
الأستاذ علي أيوب
لو كان التقريب لأقرب عدد صحيح فيمكنك اتباع التالي
Sub eur()
Dim y As Integer
x = Worksheets(”sheet1″).Cells(1, 1)
y = x * 1.5823
Worksheets(”sheet1″).Cells(2, 1) = y
End Sub
لاحظ أن y تم تعريفها عدد صحيح
أما إن كنت تريد التقريب لأكبر عدد صحيح فتحتاج ان تضيف متغيرا يأخذ قيمة التحويل بالكسور ثم تقارنه بالرقم الصحيح فإن كان الرقم الصحيح أقل فإنك تزيده واحدا
Sub eur()
Dim y As Integer
x = Worksheets(”sheet1″).Cells(1, 1)
g = x * 1.5823
y = g
If y < g Then y = y + 1
Worksheets(”sheet1″).Cells(2, 1) = y
End Sub
بالطبع عليك ان تستخدم أرقام التحويل المناسبة
ويلاحظ أنه يمكنك فعل ذلك من خلال إكسل بدون فيجوال بيسك
شكرا
عبدالرحيم فاروق قال,
مايو 27, 2008 @ 5:34 م
اعمل محاسب , وقد قمت بعمل ورقة اكسيل يكون فيها الصف ( مكونات الفاتورة ) و الاعمدة ( تحليلى المبيعات ) الاصناف التى بيعت بالفاتورة
وبواسطة دالة VLOOKUP يتم البحث عن كل محتوى خلية فى العمود المحدد بناء على رقم الفاتورة الذى يحدد الصف فى جدول المبيعات , وكذلك يتم تحديد الصنف بناء على تغيير رقم العمود فى المعادلة VLOOKUP وذلك فى تصميم الفاتورة
والوطلوب
توفير كتابة ارقام الاعمدة التى تحتوى على الاصناف المباعه فى كل مره تتغير فيها رقم الفاتورة
سامح قال,
مايو 30, 2008 @ 7:04 ص
الأستاذ عبد الرحيم
لم أستطع فهم الموضوع من التعليق. يمكنك ان ترسل لي نسخة مصغرة من الملف على البريد الإلكتروني
ar.appllications@gmail.com
حتى أستطيع فهم الملف وبالتالي مساعدتك فيما تسأل إن شاء الله.
شكرا