Program in excel sample

Copy Code. Dipjyoti12 Jul Member 9-Jul Thomas Edmundson DS1nt Sep Member 9-Feb Member 6-Sep Member Jun Georgian Pirvu Feb Member May Member 6-May PraveenKumarReddyChinta Nov Go to top. Layout: fixed fluid. First Prev Next. Message Closed Aug Member Message Closed. Password Member 9-Jul Copy Code Dear sir, Regarding to your excel file. Blue balance sheet. Blue invoice. Blue sales receipt. Blue mileage and expense report. Blue timesheet.

Activity costs tracker. But first This example builds on the previous example and has quite a few new elements. This could be used in many, many ways. The value and versatility of this functionality is more so defined by what the secondary subroutine does. For example, maybe you have a file that is used to generate 3 different weekly reports.

These reports are formatted in dramatically different ways. For loops are very useful if you need to perform repetitive tasks on a specific range of values - arrays or cell ranges.

Save and navigate back to the Developer tab of Excel and select the Macros button. Run the LoopExample macro. The For-Next loop is one of the most powerful functionalities of VBA; there are numerous potential use cases. This is a more complex example that would require multiple layers of logic, but it communicates the world of possibilities in For-Next loops.

Maybe you have a list of all products sold at your bakery in Column A, the type of product in Column B cakes, donuts, or muffins , the cost of ingredients in Column C, and the market average cost of each product type in another sheet. You need to figure out what should be the retail price of each product. A For-Next loop would allow you to do this type of calculation.

See if you can answer these questions. I'm Chloe Tucker, an artist and developer in Portland, Oregon. As a former educator, I'm continuously searching for the intersection of learning and teaching, or technology and art. If you read this far, tweet to the author to show them you care.

Tweet a thanks. Learn to code for free. Get started. Forum Donate. Chloe Tucker. In , there were over 30 million users of Microsoft Excel source. Remember that you will perform this task only once as each time you will open the VBE it will remain setup.

Step 1: Close all the windows that are open in the VBE to end up with this:. Step 2: Go to the menu bar "View" and click "Project Explorer". The result will be somewhat like the image below:. If the project window already appears as a column on the left side of the screen there is nothing else that you have to do for now.

If the project window appears in the middle of the gray area like above, right-click in the white space in the middle of the project window and check "Dockable". Then click on the top blue bar of the Project window, hold and drag it left until the cursor white arrow touches the middle of the left side of the screen.

When you let go of the mouse button the end result should be like shown in the image below. Congratulations you have setup the first major window of the VBE. Step 3: Move your cursor on the line separating the project window and the gray rectangle. When it turns to two small parallel lines and arrows click, hold and move the lines sideways. Resize the two windows as you want them.

Step 4: Go back to the menu bar "View" and click "Properties Window". The Properties window will appear somewhat like in the image below. If the Properties window is already located below the Project window there is nothing left to do. If it shows like in the image above, right-click in the white space in the middle of the Properties window and check "Dockable". Then click on the top blue bar of the Properties window and drag it left and down until the cursor white arrow touches the center of the bottom of the Project window.

When you let go of the mouse button the end result should be as the image below. Congratulations you have setup the second major window of the VBE. Step 5: Move your cursor on the line separating the project window and the properties window.

When it turns to two small parallel lines and arrows click, hold and move the lines vertically. Step 6: To add the code window to the setup, you just have to double click on the name of a component in the Project window Sheet1, Sheet2, Sheet3 or ThisWorkbook and its code window appears within the gray rectangle. You can maximize any Code window by clicking on its "Maximize" button. The final result looks like the image below.

The words "Option Explicit" might not be present in your Code window. We will address this issue later in the lesson on variables Lesson XLAM in the project window.

Forget about this project for now. Step 6: Now go to Excel and close it. Congratulations, you are now ready to work in the Visual Basic Editor. We will discover more about each of these three windows in chapter s 2 Project Window , 3 Properties Window and 4 Code Windows. Close the VBE and close Excel without saving anything. Here is a sample of what you will find in chapter 2 of the downloadable Tutorial on Excel macros.

Note: Print this page, open Excel and a open a new workbook. As you can see, the Project window shows you all the workbooks that are open "Book1" in the example below and their components. A new Excel workbook includes three sheets and another component named "ThisWorkbook". As we will see later in lesson 9 on events "ThisWorkbook" is a component in which you will store the macros also called VBA procedures that should start automatically when the workbook is opened.

Working within the Project Window. We will now complete a brief exercise to learn how easy it is to work within the Project Window. Exercise 2 Create your first macro and use it. Step 2: Add a sheet. Right-click on the tab of Sheet2 and select "Insert".

Step 3: In the dialog window that appears, click on "OK". Notice that the worksheets are sorted alphabetically in the Project window even if they are not in the workbook. If you have purchased and downloaded the course on Excel Macros and opened the Excel file " vba-tutorial-editor. The workbook " vba-tutorial-editor. You can export and save these modules to be used later in other workbook.

In the complete lesson 2 you will learn how to add any type of components and how to remove, import, export and manage them from the Project window. Here is a sample of what you will find in chapter 3 of the downloadable Tutorial on Excel macros. The Properties window shows you the properties of the component that is selected in the Project Window single click. For example in the new workbook if you single click on "Sheet1" in the Project Window you see the properties of sheet1 in the Properties Window like in the image below.

As you can see, a worksheet has 12 properties that you can change in this Properties window. Notice that there are 2 "Name" properties. On the first line there is the programmatical name of the sheet Sheet1. You will discover later the advantages and disadvantages of changing this property.

The second "Name" property 9th line is the name or caption that appears on the tab of the sheet in Excel. Changing the "Name" Property. Exercise 3 Create your first macro and use it. Step 2: We will change the name Caption on the tab of "sheet1" to "Introduction".

To do so right-click on the tab of the sheet and the following dialog window appears:. Step 3: Select "Rename". The menu disappears and the name of Sheet1 is highlighted. Enter "Introduction" and this new name will replace "Sheet1" when you click "Enter".

The end result is illustrated in the image below. As you have now learned the name of the sheet can be changed from Excel. We will now complete another smal exercise to change the name from the VBE Properties window. Exercise 4 Create your first macro and use it. On line 9 of the Properties window double-cllck on "Sheet2" and enter the name Spreadsheet. Click "Enter". Step 2: Go to Excel and notice that you now have a sheet named "Spreadsheet".

Setting and modifying properties of objects in the Properties Windows is something that you will have to do a lot when you start developing userforms see lessons 24 to Until then you will change a small number of properties including the very important "Visible" property of the sheets to one of its three values. To see the equivalent of the image below, select Sheet2 Spreadsheet in the Project window. Click on the word "Visible" on the 12th line of the Properties window. A dropdown arrow appears in the cell to the right.

Click on the arrow and you can select one of the three properties. This property of a sheet can be used -- for example, to hide salaries in a budgeting application or prices in an estimation application -- making sensitive data inaccessible to the unauthorized users of your workbooks.

You will also learn how to name your modules and work with a few other properties of the objects appearing in the Project Window. Close the VBE an. Here is a sample of what you will find in chapter 4 of the downloadable Tutorial on Excel macros. To illustrate everything that you can do in the Code window we will start by creating a small macro in an empty workbook. Exercise 6 Create your first macro and use it.