Online PC Support

OPS Technical Solutions : +1 833-522-1003

Welcome

How to Develop Microsoft Excel through VBA Programming

Microsoft Excel is just not a spreadsheet application but if you have gone through it thoroughly, then you will be amazed to know what all can be achieved in Microsoft Excel. To be true what is visible in Excel is just a part of it and it carries a lot more behind the windows. It is a fully programmable and customizable database application and one can keep the data in Excel for backend needs and develop applications and interfaces which will work as frontend. All you need is to know how Windows and its applications work and how to use the active controls. Moreover you should know the programming language known as Visual Basic only then you can develop interface using the same in Microsoft Excel.

In this post I will discuss how to start with VBA programming on Microsoft Excel. Even if you know Visual Basic still you would need extra study material to know how Excel cells are referred and called in Visual Basic scripting.

Step1:

By default, the developer tab is hidden in Microsoft Excel so you need to get it available for you. To do the same click on the Excel options button and then in the Excel options window check the Show Developer Tab check box.

Step2:

Open a new Excel file and then press Alt+F11. This action will open a new Window which will look much like Visual Basic editor. This is the place where on the left side you will get list of all available sheets in the Excel Sheet. You can select any sheet and then in the console which opens on the right can be used for designing or writing macros.

Step3:

If you wish to have extra Windows in your Excel file then click on Insert and insert a new Form. Designing and controls of this Form will exactly be the same as you do in Visual Basic.

Step4:

Add controls and ActiveX items in your form. These additions and selection of controls will depend upon what you want to achieve and what your project is all about.

Step5:

Once you are done with the design and coding make sure that you save the file with an extension of XLSM which defines the file to be opened and used as programmable file.

Steps given above are simple steps to start the Visual basic project in an Excel file. You should always draw the flow of what you want to program as a flow chart on paper considering all the input, output and logical and arithmetic calculations. Drawing a flow chart on paper is the prerequisite for any programming to be done as it nearly makes the coding bug free. Also please note that once you use the Excel cells in your coding then do not delete rows and columns in your spreadsheet because coding uses a different approach for referring to Excel cells and the code doesn’t get auto updated on deleting or inserting rows and columns in Excel spreadsheet.

Write an Excel VBA Program

First let us discuss, what is VBA. VBA is an implementation of Microsoft’s event driven programming language Visual basic 6. It is an acronym of Visual Basic for Applications and it is also an implementation of associated integrated development environment. IDE is built into most of the MS office applications. There are so many features of VBA like it enables developers to develop user defined functions, automate processes and access Win32. It isbuilt into several office applications apart from version 2008 for Apple’s Mac OS X, other Microsoft applications such as Microsoft Map Point and Microsoft Visio. VBA is also can be partially integrated with some other applications like AutoCAD, WordPerfect and ArcGIS. VBA can also be used to control several aspects of the host applications, including manipulating user interface features like menus and toolbars and working with custom user forms and dialog boxes. You can also use VBA to integrate with other programs also like PDF.

If you want to use VBA with an application such as Excel, you will need terminology and language constructions to interact with an application.This is called the object model for the application. You can get online this model for Excel and for Word. You can easily found it by opening the Macro/VBA editor in the target application and then using view to open the object browser.

You can also learn VBA easily and with a little practice you can learn and be a controller of this programming language and you can save time with adding the time-saving automation to your spreadsheets.

Now in this article we will discuss how to write an Excel VBA program. You just have to follow the steps given below.

Step1:

To write Excel program, the first step is to open the visual basic editor. Press ALT+F11 or you can also select macros from tools menu to open the visual basic editor.

Step2:

Now the second step is to open the module from the insert menu.

Step3:

In the insert menu; just type sub and name of your program. Now press enter.

Step4:

Now between sub and end sub, you need to type your Excel VBA program for example we will see an example program:
Sub Macro1()” Macro1 Macro’ Macro recorded 12/04/2006 by TechGinie ”Sheets(“Sheet1”). Select Sheets. Add Active Cell. Formula R1C1 = “Monday”Range(“A1”).SelectSelection.AutoFill Destination:=Range(“A1:A7”),Type:=xlFillDefaultRange(“A1:A7”).SelectEnd Sub

Step5:

After typing the program, you want the program to run. So, to run the program, press F5 and click play button on the toolbar or you can also click Run Sub/User Form from the Run menu.
If you want to learn VBA in a suitable way then try to record Macros and study the code. So, it is not that tough to learn the Excel programming and you can learn it also via online. All the codes are available online, you just need to download and study the codes. You can get the exact idea from this article.

How to Use the Wizard to Add Command Buttons to a Microsoft Access Form or Report

Microsoft Access can be used for creating a command button and adding actions to it. This can easily be done by using the command button wizard given in Microsoft Access. Command button is a control that runs a macro, calls a Visual Basic function or runs an event procedure.

Let us see the basic steps involved in creating a Command Button in MS access form or report:

Step1: Open a form or a data access page in the Design view ( A view that shows the design of these database objects: tables, , forms, queries, reports, and macros. In Design view, you can create the new database objects and modify the design of existing objects.).

Step 2: Make sure that the Control Wizards Button image tool in the toolbox is pressed in.

Step 3: In the toolbox, click Command Button Control tool.

Step 4: On the form or data access page inside MS Access, click where you want to add the command button.

Step 5: Follow the directions in wizard dialog boxes. In last dialog box, click on Finish to display the command button in Design view.

Now lat us see the basic steps involved in modifying a command button without a wizard.

Step 1: Open a form or a data access page in the Design view ( A view that shows the design of these database objects: tables, forms, queries, reports, and macros. In Design view, you can create the new database objects and modify the design of existing objects.).

Step 2: Make sure that the Control Wizards Button image tool in the toolbox is not pressed in. This will turns off the wizard.

Step 3: In the toolbox, click on the Command Button tool Control.

Step 4: On the form or data access page in MS Access, click where you want to add the command button.

Step 5: Make sure that command button is selected, and then click on Properties Button image on the toolbar to open command button’s property sheet (A pane that is used to view or modify the properties of various objects such as tables, fields, queries, forms, data access pages, reports, and controls.).

Step 6: In the property box of the form, enter the name of macro or event procedure that you want to run when button is clicked or click on the Build button to use Macro Builder or Code Builder. On a data access page, you can attach the code written in either JScript (A scripting language that can be used to add functionality to a Web site or a Web page or, or to enhance the look of one) or Microsoft Visual Basic Scripting Edition to a command button by using Microsoft Script Editor.

Step 7: Now do one of the following:

  • To display the text on a command button on a data access page: Type text in command button’s Value property box.
  • To display the text on a command button on a form: Type text in command button’s Caption property box.
  • To display the picture on a command button on a form: In command button’s Picture property box, type the path and the file name for a .bmp, .dib, or .ico file.

This is the whole procedure for creating and modifying a command button in MS Access.

How to Insert a Timer in MS Excel

Microsoft excel is an application developed by Microsoft using a programming language called Visual Basic for Applications or VBA. MS Excel is a spreadsheet program used for accounting purposes. Here you will find a very large number of cells forming a grid like structure of rows and columns. You can enter text, numeric values, alphanumeric text as well as formulas and symbols in these cells. Excel uses timer function for setting up reminder for certain jobs which is to be done.

The timer function is a part of the Visual Basic for Applications or VBA language. Since VBA is the developing language for MS Excel, you can insert this function in your Excel worksheet.

To perform this task, follow the step by step information given below:

Step 1: This includes opening the Excel spreadsheet.

Step 2: Click on the Developer tab on the main menu bar. If you cannot find Developer tab there, go to Microsoft office logo on the top left corner of the screen and click on it. A new window will open and in this window, click on the Excel options button. Choose popular in the Excel options window. Then check the Show Developer tab in the ribbon option. The Developer tab will appear in the main menu bar.

Step 3: clicking the Developer tab will open a list of menus. Click on Insert in the Controls group. This action will give you a drop down menu and from this menu, select Procedure. You will be asked give this procedure a name .Type the name you like, but it will be helpful if you give this procedure some relevant name something like Exceltime. Select the type as Sub.

Step 4: Here you should be a bit careful while typing the text given below. The text

should be on separate lines. And they should be between the Public Sub ExcelTime() and End Sub in the right pane and type the following:

Dim Pause_Time, Start, Finish, Total_Time

If (MsgBox(“To Get a Break Reminder Press Yes… “, 4)) = vbYes Then

PauseTime = 10

Start = Timer

Do While Timer < Start + Pause_Time

DoEvents

Application.Speech.Speak “It is Break time!”

Loop

Finish = Timer

TotalTime = Finish – Start

MsgBox “Total Break time= ” & Total_Time & ” seconds”

Else

End

End If

End Sub

Step 5: Press the function 5 key on the keyboard. An Excel worksheet containing a small window will open. In this window you will see the message To Get a Break Reminder, Press Yes and wait for few seconds. If this little window does not appear, go and check if you have inserted the right code mentioned in step 4 fully and correctly. If not, then type it just as it is given there.

Step 6: you can also assign this action a shortcut key. For this, click on the macro option listed under the Developer tab. A Macro dialog box will appear. Select it there and click on Options.

By following the step by step procedure given above, you can insert a timer in your Excel spreadsheet.

Call Now: +1 833-522-1003
Call Now: +1 833-522-1003
Call Now: +1 833-522-1003