Explore a blend of Excel expertise and beyond. From Visual Basic Applications for corporate MIS to blogging tips, education insights, and more. Join me in discovering diverse subjects as I share 14 years of experience and skills in Python, Machine Learning basics, Product development, and Coding.
In this blog we will learn hierarchy of VBA Objects and will discuss about Shape Object. Let's see in below image example of Shape Object. Shape Object use to create shapes in Excel as per our requirements. Here we are taking example of a rectangle shape.
With Worksheets(1).Shapes.AddShape(msoShapeRoundedRectangle, _
150, 70, 70, 80)
.Name = " ShapeObjectinVBA"
End With
End Sub
Sub SelectShapesinActiveExcelSheet()
ActiveSheet.Shapes.SelectAll
End Sub
Output : Refer first image to see created rectangle its created after Run the first code. Also take look on created rectangle in first image for second code out put when we run the selector code it select the created image automatically.
Here we are taking a example to understand the hierarchical use of VBA Objects . Let's assume we need to select a Range("D4") in a Sheet1 of Workbook (Book1) .
After Run Above code we got selected D4 Range in Sheet1 of Book1 Workbook. Let's fill the Range D4 with 100 Number using a VBA code. Take a look on below image.
Other Services : We provide Automated Tools for reporting and analysis your business Data Automatically using some simple steps in User from and Command buttons.
The Excel Application itself is a Object. As we know every thing is Object which is actionable. So MS Excel also have some Objects like workbook, worksheets, Range etc. We are listing some types of Objects below in MS Excel.
In below image you can see when we write (Application. ) keyword in visual basic editor we got entire excel objects . We can use these all excel objects with Application. Object accordingly we are taking example of Application.ActiveSheet.Range Object.
A. Code
Sub Application_ActiveSheet()
Application.ActiveSheet.Range("A1").Value = 100
End Sub
B. Output
We click Run button for Run the written code. Refer Cell A1 we got result in value 100 in A1 Range.
In below image you can see when we write (Workbooks. ) keyword in visual basic editor we got entire excel objects . We can use these all excel objects with Workbooks. Object accordingly we are taking example of Workbooks.Add Object. We can add new workbook using Workbooks.Add code.
We click Run button for Run the written code. We got a new Workbook created Name Book2.
3. Workbook Object
The workbook Object use to manage worksheets collections. For Active workbook we use ActiveWorkbook. Object use to work on Active workbooks. Here we taking example of ActiveWorkbook.Save. Below code will save Active workbook.
A. Code
Sub workbook_object()
ActiveWorkbook.Save
End Sub
B. Output
We click Run button for Run the written code. Active workbook will be saved on default location.
In below image you can see when we write (Activeworkbook.Sheets.) keyword in visual basic editor we got entire excel objects related to Sheets Object. We can use these all excel objects with ActiveWorkbook.Sheets.Object accordingly.
We are taking example ofActiveWorkbook.Sheets.Add . We can Add new worksheet in active workbook using this Object.
Worksheet Object work with Worksheets of entire excel workbooks. We use Object Activeworkbook. for work with Active workbooks. Let;s see below example for Copy paste Data of Range("A1:B3") at Range("D4") .
A. Code
Sub Activeworkbook()
ActiveSheet.Range("A1:B3").Copy
ActiveSheet.Range("D4").Select
ActiveSheet.Paste
End Sub
B. Output
We click Run button for Run the written code. Range ("A1:B3") Data copy and pasted in Range("D4:E6").
6. Worksheets Object
In below image you can see when we write (Worksheets.) keyword in visual basic editor we got entire excel objects related to Worksheets. Object. We can use these all excel objects with Worksheets.Object accordingly.
We are taking example of Worksheets.Add . We can Add new worksheet in active workbook using this Object.
A. Code
Sub Activeworkbook()
Worksheets.Add
End Sub
B. Output
We click Run button for Run the written code. New sheet added in Active workbook having Name (Sheet3)
Other Services : We provide Automated Tools for reporting and analysis your business Data Automatically using some simple steps in User from and Command buttons.
How to use Visual basic Editor for Create Macros (codes) in MS Excel VBA
1. How to Open Visual Basic Editor
In this blog we will learn about visual basic editor, So first we click on developer tab in MS Excel Ribbon. Here we find a sub tab ( Visual Basic). Now we click on Visual Basic tab for access visual basic editor.
Now we find a Insert tab in Visual Basic editor. Here we click on Insert tab and got a list. After that we click on module tab. Refer below 2nd Image we got inserted module for write VBA code.
3. Can we write code in This Workbook and Sheet1 Objects
Yes we can write VBA code in This workbook and sheet 1 and sheet 2 etc also. We do write codes in Sheets when we want get execute our code on some events like (with Open file, close file, etc). Normally we write codes in modules.
4. Properties Window
Using properties window we can change Name of inserted module . We changed name (computation) of inserted module. See in project explorer New assigned Name has been changed.
5. Run , Break and Reset Button in Visual basic Editor
We use Run button for execute the VBA Macro (code) for perform task and see results. Break button (Ctrl + Break) we use to break VBA Code in between while it is running. Reset Button we use to get exit from running mode if code gave error and stopped in between or we got entered wrongly in interpretation mode.
Other Services : We provide Automated Tools for reporting and analysis your business Data Automatically using some simple steps in User from and Command buttons.
In this blog we will learn how to record Macro in MS Excel VBA. First we click on Developer Tab in Excel Ribbon. After That we click on Record Macro tab.
Now we do write a relevant Name for our Macro and assigned a short key ( Note: Short key would be new one else it will be replaced and it will impact excel basic short key's uses). Take a look on below image how we have added Name and short key. After fill Required details we clicked on Ok button for start recording.
3. Start Recording:
Now VBA Macro recording is in progress and its recording steps whatever we are doing in Excel workbook manually. We have written Hello World in Range("A1") at Sheet 1. Now this process is recorded in VBA lets See Visual Basic Code in Visual Basic Editor.
4. Drag a Command Button for Execute the Recorded Code:
We clicked on Developer Tab and dragged a command button from insert Tab. Also did some Color and formatting for make this attractive.
Other Services : We provide Automated Tools for reporting and analysis your business Data Automatically using some simple steps in User from and Command buttons.
2. Excel Options Tabs : First we click on File tab in MS Excel Ribbon. After that we do click on Options Button. After click on Options button we got a new window Excel Options.
3. Let's take a look on above image. We find whatever we want, There is a Developer Tab in Main Tabs List. Now after click on check box of developer tab click on Ok button. Let's see on below image.
4. After click on Ok button Let's take a look on MS Excel sheet We successfully got added Developer Tab in MS Excel Ribbon.
5. Use of Developer Tab: We do automate our Manual work in MS Excel using VBA Macros. We can access Visual Basics in MS Excel in Developer Tab only.
Other Services : We provide Automated Tools for reporting and analysis your business Data Automatically using some simple steps in User from and Command buttons.
In this blog we will learn how to automate manual works in MS Excel VBA. Let's create first Macro in MS Excel VBA. Here in the below example we will use command button to run our first macro.
1. Let's create a Macro enabled file : We open a new Excel file and save the same as Excel Macro-Enabled Workbook.
2. Add Command button: We dragged a command button on sheet1.
3. View / Write VBA Code : Double click on Command button or Right click CommandButton1 (make sure Design Mode is selected).
4. After double click on Command button Visual Basic Editor appears. Just place our cursor between Private Sub CommandButton1_Click() and End Sub. Now we add our VBA Code as shown in below image.
Other Services : We provide Automated Tools for reporting and analysis your business Data Automatically using some simple steps in User from and Command buttons.
भारत में ब्लॉगिंग की शुरुआत कैसे करें: एक पूरी गाइड
ब्लॉगिंग एक माध्यम है जिससे हम अपने विचारों, अनुभवों और ज्ञान को लोगों तक पहुँचा सकते हैं। भारत में ब्लॉगिंग की शुरुआत करना बहुत ही आसान है और इसके लिए कुछ सरल कदम हैं जो हम यहाँ पर विस्तार से देखेंगे।
1. प्लेटफ़ॉर्म चुनें:
पहला कदम होता है उस प्लेटफ़ॉर्म का चयन करना जिस पर आप अपना ब्लॉग होस्ट करना चाहते हैं। भारत में प्रमुख ब्लॉगिंग प्लेटफ़ॉर्म्स में WordPress और Blogger शामिल हैं। WordPress उपयोगकर्ताओं के बीच बहुत लोकप्रिय है जबकि Blogger Google का अधिकांश सेवा मुक्त ब्लॉगिंग प्लेटफ़ॉर्म है और इसे उपयोग करना भी बहुत आसान है।
2. टॉपिक चुनें:
एक अच्छा ब्लॉग बनाने के लिए उस विषय का चयन करें जिसमें आपका दिल लगा हो और जिसे आप प्रेरित होकर लिख सकें। विशेषज्ञता या रुचि के विषय में लिखने से आपके पाठक आपके ब्लॉग को पसंद करने लगेंगे और आपकी प्रतिष्ठा में मदद मिलेगी।
3. ब्लॉग नाम और डोमेन चुनें:
अपने ब्लॉग के लिए एक अनूठा और यादगार नाम चुनें जो आपके ब्लॉग की पहचान बने। उसके लिए एक सुचारू और अच्छा डोमेन पंजीकरण करें।
4. डिजाइन और लेआउट तैयार करें:
अपने ब्लॉग के लिए एक अत्यधिक आकर्षक और पेशेवर डिजाइन चुनें। यह आपके पाठकों को आकर्षित करेगा और आपकी ब्लॉगिंग शैली को प्रकट करेगा।
5. अच्छी सामग्री प्रकाशित करें:
ब्लॉगिंग का मुख्य हिस्सा है अच्छी सामग्री। आपकी सामग्री को SEO अनुकूलित करने के लिए विशेष ध्यान दें ताकि आपके पाठक आपके ब्लॉग पर आते रहें और आपकी प्रतिष्ठा बढ़े।
6. साझा करें और प्रमोट करें:
अपने ब्लॉग को सोशल मीडिया पर साझा करें और इसे प्रचारित करने के लिए नियमित रूप से अपडेट करें। अपने ब्लॉग के सब्सक्राइबर्स को नए पोस्ट्स की सूचनाएँ दें और उनके साथ संवाद बनाएं।
7. निरंतरता बनाए रखें:
ब्लॉगिंग का एक महत्वपूर्ण तत्व है निरंतर लिखना। यह आपके पाठकों के लिए विशेषज्ञता और विश्वसनीयता की भावना बनाए रखता है।
8. समुदाय बनाएं:
अपने ब्लॉग के चाहने वालों के साथ समुदाय बनाएं। इससे आपके ब्लॉग की प्रतिष्ठा और उपस्थिति में वृद्धि होगी।
ब्लॉगिंग एक सफल और संतोषजनक माध्यम है जो आपको अनगिनत अवसर प्रदान कर सकता है। अब, जोर लगाएं और अपने सपनों को हकीकत में बदलने के लिए ब्लॉगिंग की दुनिया में कदम रखें! शुरू करने के लिए आप तैयार हैं?