Declaring Variables In Excel VBA
March 24, 2010 | Leave a Comment
The use of variables is common to all programming languages and are named areas of memory in which you can store data required by your program for its execution. To create a variable, you declare it, in other words, you notify Visual Basic of your intention to use a variable of a given name to store a given type of data. The keyword “Dim” (short for “Dimension”) is used to declare variables; thus, to declare that you will be using a variable called “strDepartment” to hold string (text) data, you would write “Dim strDept as String”.
VBA does not insist that you declare the variable type when declaring a variable; but it is useful to do so, since this prevents you from accidentally placing the wrong type of data into the variable. The “String” data type is used to hold text. For whole numbers, the “Integer” (small whole numbers) and “Long” (large whole numbers) are used. For real numbers (with decimals) “Single” (large real numbers) and “Double” (very large real numbers) are used. There is also the “Currency” data type which is used for large real numbers with exactly four decimal places. Then we have “Boolean”, a data type which always returns true or false and “Date”.
In addition to these primitive (proper) data types, Excel VBA also allows you to place Excel objects into variables. Thus, if you want to perform a number of operations on a given worksheet, you could put a reference to that worksheet into a variable and manipulate the worksheet to your heart’s content simply by using the name of the variable. To declare the worksheet variable, you would use a statement like “Dim wks As Worksheet”. Then, to place a reference to a particular worksheet into your variable, you would use a statement like: “set wks = ActiveWorkbook.Worksheets(”Data”)”.
The location, within the module, where you declare a variable determines its scope: if you declare a variable inside a sub routine, then it will be local to that sub routine and the data it contains can only be used inside that sub. If you declare it at the top of the module, above all of the subs, then it can be accessed by all the subs within the module.
If your Excel VBA application contains several modules and you want all modules to share certain variables, you can also make variables global. Global variables need to be declared at the top of any module but they use the keyword “Public” instead of “Dim”. Thus to declare a global date variable called “dtStartDate”, you would enter “Public dtStartDate As Date” at the top of one of your code modules.
You can get up to date information on Excel VBA training courses, visit Macresource Computer Training, an independent computer training company offering Excel VBA Classes at their central London training centre.
Understanding Object Properties In Excel VBA
March 21, 2010 | Leave a Comment
When you first start learning Excel VBA, one of the first concepts you need to grasp is that in order to automate Excel, you have to refer to the various components and features which the program contains. In order to do this successfully, you need to know the name VBA name assigned to each element and the correct syntax to use in order to accomplish a certain task. Each component which forms part of Excel is an object with a specific name, usually a fairly obvious name, such as workbook, worksheet or range.
Excel offers a kind of dictionary to check the correct syntax to use when working with Excel objects; it’s called the Object Browser. Simply choose View - Object Browser in the Visual Basic Editor. Choose “Excel” from the drop-down menu in the top left of the Object Browser window which is initially set to “All Libraries”. The Object browser will then display a list of all the objects within Excel. Clicking on the name of an object will display the members relating to it; in other words, the syntax which can be used when working with that particular object.
The two main types of syntax used to manipulate Excel objects are properties and methods. Properties are attributes which the object possesses while methods are actions which can be performed on the object. If we compare this syntax to English grammar, you could say that properties are like nouns and that methods resemble verbs.
Certain properties of an object are referred to as read-only; you can check to see what they are but you cannot change them. For example, we could check the version of Excel being used with the syntax “Application.Version”; but we could not set the version. By contrast, we can both read and alter other properties. Thus we could verify the users preferred number of sheets in each new workbook with the code “Application.SheetsInNewWorkbook” and we can alter alter this number with the statement “Application.SheetsInNewWorkbook = 12″, for example. Properties which can be modified in this way are referred to as read/write.
Each object property can only be set with a value of the correct data type. Thus, in the example above, an integer has to be supplied. Some Excel VBA objects also have properties which require an enumeration, one of a fixed number of set keywords beginning with “xl”. For example, if you want to set the location of a chart, you would use one of the three built-in “xlChartLocation” enumerations: “xlLocationAsNewSheet”, “xlLocationAsObject”, or “xlAutomatic”.
If you want more information Excel VBA training courses, visit Macresource Computer Training, an independent computer training company offering Excel VBA training courses at their central London training centre.
Customizing Column Charts In Microsoft Excel
March 20, 2010 | Leave a Comment
In this tutorial will look at the creation and customisation of a column chart. The first step is to select the data that we want to plot, taking care to include any column and row headings. Row headings will be used as the names of the chart series and will be displayed in the legend. Column headings will be used as category labels. If the selection includes two sets of column headings, Excel will automatically recognize this and create two sets of headings on the category axis for us.
The next step is to click on the Insert ribbon tab and from the Column drop-down menu choose the option that we require. The very first option is the omnipresent scattered column chart. Excel creates our chart and places it in the worksheet as an embedded chart. If we want to change it to a standalone chart, click on Move Chart in the Location section and then choose New Sheet and enter a name for the new chart sheet.
Having selected a chart, you can of course customise it to suit your requirements. To change the colour of the columns, simply click once on any column to highlight the whole series and then choose a colour from the Shape Fill drop-down menu in the Format contextual tab. As well as Shape Fill, the drop-down also offers Shape Outline and Fill Effects. The Fill Effects includes preset effects such as shadow, glow and bevel.
There are quite a few subtypes available within the column chart type. The most basic and probably the most widely used is the clustered column chart type. To change the chart type, go to the Design contextual tab and click on Change Chart Type.
In the Stacked Column chart type, it is the overall total of all series within each category which takes precedence over the individual value of each series. The second type of stacked column is 100% Stacked Column. Here, the height of each column becomes 100% and so all columns have exactly the same height. This type of chart shifts the emphasis away from the number or quantity represented by each series to the percentage split between series.
Excel also offers 3-D versions of its three different column chart types. You will notice that these chart tpes are not strictly 3-D charts; they simply have a 3-D effect on the columns. It is the 3-D Column option which gives us an actual three-dimensional chart. Here, Excel adds depth to the chart and places the series along the z axis (the third dimension). The remaining column options are simply variations on these basic themes. For example, if we want to go for a 3-D chart, we might choose to have pyramids instead of rectangular blocks.
The The writer of this article is a trainer and developer with Macresource Computer Training, an independent computer training company offering Microsoft Excel 2007 training courses at their central London training centre.
Excel VBA Can Be Mastered With The Right Training
March 20, 2010 | Leave a Comment
Almost all computer users know Microsoft Excel to some degree and most are aware of macros and VBA. However, the vast majority shy away from VBA assuming it to be the preserve of Excel gurus and professional programmers. Nothing could be further from the truth: having Excel VBA training is a natural progression for anyone who knows Excel well and want to take their knowledge to the next level.
It is important to stress, however, that it’s no good attending Excel VBA training classes if your knowledge of Excel is poor. You need to know Excel itself very well before you undertake any form of Visual Basic excel training; otherwise, the applications you create are bound to be flawed.
If you are going to be able to develop decent Excel applications, you need to create functionality which complements the features built in to the program. Excel applications created by people who are not Excel specialists or simply do not know the program very well tend to perform actions which could be accomplished by using Excel’s own set of features.
If you are fortunate enough to know Excel very well and you can spare the time, attending Excel VBA courses may be a very rewarding experience. You will discover that Excel VBA is not terribly difficult to learn and that there is a great variety of learning materials available. After all, Excel has been around for a couple of decades and it is installed on most business PCs.
One of the reasons why Excel VBA is worth learning is that the applications you write never have to be built from the ground up. All you are doing is leveraging and automating the powerful capabilities inherent in the program. It’s a bit like walking on those long moving walkways that you get at airports. Any effort you put into walking is immediately amplified and you move much faster than you do when walking on static ground.
If you end up becoming serious about developing applications, you will have a guaranteed audience; since there are so many people using Excel. Finding a decent Excel VBA training course can help your career or even help you get a pay rise in your current job. The skills you will learn will stand you in good stead for years to come.
The author is a training consultant with Macresource Computer Solutions, an independent computer training company offering Microsoft Excel VBA Classes in London and throughout the UK.
Getting A Handle On Excel 2007 Column Charts
March 19, 2010 | Leave a Comment
In this tutorial will look at the creation and customisation of a column chart. The first step is to select the data that we want to plot, taking care to include any column and row headings. Row headings will be used as the names of the chart series and will be displayed in the legend. Column headings will be used as category labels. If the selection includes two sets of column headings, Excel will automatically recognize this and create two sets of headings on the category axis for us.
The next step is to click on the Insert Ribbon tab and from the column drop-down menu choose the option that we require. The very first option is the default clustered column. Excel creates our chart and places it in the worksheet as an embedded chart. If we want to change it to a standalone chart, click on Move Chart in the Location section and then choose New Sheet and enter a name for the new chart sheet.
Having selected a chart, you can of course customise it to suit your requirements. To change the colour of the columns, simply click once on any column to highlight the whole series and then choose a colour from the Shape Fill drop-down menu in the Format contextual tab. As well as Shape Fill, the drop-down also offers Shape Outline and Fill Effects. The Fill Effects includes preset effects such as shadow, glow and bevel.
There are a number of subtypes available within the column chart type. The most basic and probably the most widely used is the clustered column chart type. To change the chart type, go to the Design contextual tab and click on Change Chart Type.
In the Stacked Column chart type, it is the overall total of all series within each category which takes precedence over the individual value of each series. The second type of stacked column is 100% Stacked Column. Here, the height of each column becomes 100% and so all columns have exactly the same height. This type of chart shifts the emphasis away from the number or quantity represented by each series to the percentage split between series.
Excel also offers us 3-D versions of these three different column chart types. However, these are not strictly 3-D charts; they simply have a 3-D effect on the columns. It is the 3-D Column option which gives us an actual three-dimensional chart. Here, Excel adds depth to the chart and places the series along the z axis (the third dimension). The remaining column options are simply variations on these basic themes. For example, if we want to go for a 3-D chart, we might choose to have pyramids instead of rectangular blocks.
You can find out more about Excel and Excel VBA training courses, visit On Site Training Courses . Com, a UK IT training web site offering Excel VBA Classes all over the UK.
Identifying The Main Elements Of A Microsoft Excel Chart
March 19, 2010 | Leave a Comment
Charts provide us with quick and easy way of graphically illustrating trends within your data. One glance at a chart can make it very plain where there is a dip in sales figures, a surge in visitor numbers and a host of other trends in whatever data is being represented. In this article we will examine the various components of an Excel chart.
The first thing we need is a set of data which can easily be converted into a readable chart. It is normally best to plot data which is a summary of your information. It is also useful if your data is arranged in columns or rows with headings at the top of columns or on the left of rows.
An example of information which would be easy to convert into a chart is a selection containing two columns with data on the left and the corresponding values on the right. When the chart is created, the labels are placed on what is variously known as the category axis, horizontal axis or x axis; while values are arranged on the y axis. When your data is arranged in this format, the chart that Excel plots will not need much modification.
Charts may either be standalone or embedded. A stand-alone chart has an Excel sheet dedicated simply to the chart. Embedded charts are placed directly on the worksheet, often alongside the data being plotted. This is known as a chart sheet; in contrast to a worksheet.
Whether embedded or standalone, the main elements in a chart are always the same. First of all, you have a chart area. This is the background to the chart as a whole. Next, there is the plot area. This is the area where the graph or chart is actually plotted. Then, as we have seen, we have two or more axes. In a typical, “no frills” chart, there are two axes: the horizontal, or category, axis and the vertical, or value, axis.
Next, we have one or more series of data. In the example cited above, where we select a column of labels and one column of values, there would be only one series of data. In a chart containing more than one series, it is necessary to clarify what each column represents. This is done by using a legend. The legend acts as a key which tells us what each colour within the chart actually stands for.
As well as the text labels associated with the axes and with the legend, we can also include chart titles. In addition to the main chart title, we also have the option of placing titles on the axes. Within the plot area, we can also choose to display grid lines. These make it easy to read the value associated with each point on the chart.
These then are the main elements within a chart. However, Excel allows you to customise each of these elements and add other elements which enable you to create charts which convey exactly the message you have in mind.
Author is a developer and trainer with Macresource Computer Solutions, an independent computer training company offering Microsoft Excel 2007 training courses at their central London training centre.
Microsoft Excel Freeze Panes Tips
March 17, 2010 | Leave a Comment
Most of the worksheets that are created Excel contain headings in the top row of the sheet. Usually, when we scroll down the sheet, any headings at the top will simply disappear. In the same way, if we scroll to the right, any headings on the left will disappear. The Freeze Panes command, which is located in the View Tab of the Excel Ribbon, allows us to freeze our headings so that, as we scroll the worksheet, our headings remain in view.
Excel offers us three options: firstly, we can choose “Freeze Top Row”. A bold horizontal line is then displayed underneath the first row which extends into the row headings. As we scroll down the worksheet, the headings at the top of the sheet remain in view. Similarly, we can choose “Freeze First Column”. This time, the bold line extends to the right of the first column and into the column heading area. Then, as we scroll to the right, the first column remains frozen so that we can see the headings it contains and compare them with the data in the adjacent cells. To return to normal scrolling, we simply choose “Unfreeze Panes” in the “Freeze Panes” drop-down menu.
As well as freezing a single row or column, it is also possible to freeze an arbitrary number of rows and columns. To do this, you simply highlight the cell below the last row you want frozen and to the right of the last column you want frozen. So, for example, if you want to to freeze the first row and the first column, you just select cell “B2″. Once you have highlighted the cell, in the “Freeze Panes” drop-down menu, you would then choose “Freeze Panes”.
This time, you should see two bold lines: one indicating the column that is frozen and one indicating the row that is frozen. Then, when scrolling down, the first row remains frozen and, similarly, when scrolling to the right, the first column remains frozen. Once again, to normal scrolling, simply choose “Unfreeze Panes” in the “Freeze Panes” drop-down menu.
Since the various Freeze Pane commands allow us to freeze any number of rows or columns, when you are working on a large worksheet perhaps containing multiple row and column headings, you will probably find it a fairly essential feature.
The The writer of this article is a trainer and developer with TrainingCompany.Com, a UK IT training company offering Microsoft Excel 2007training courses at their central London training centre.
Could The Project Management Method Be Right For Your Organisation?
March 13, 2010 | Leave a Comment
The Project management arena has undergone some significant development in the last decade. A business project can have a far-reaching effect on the business and result in either tremendous improvement in the businesses ability to function in the marketplace or a significant setback to that business entity.
The idea of a formalized project management approach has been around for quite some time. So it was not uncommon for any manager to find themselves learning the discipline of a structured project management system. That project methodology takes any given business or IT project through the same standardized steps from conception through implementation. Those steps would include…
- Project definition - Needs analysis and requirements definition - Cost benefit analysis - Project scope - Project schedule and budget - Detailed specifications - Development - Testing - Training - Deployment
By utilizing a standardized process of doing all projects the same way, using the same reporting methods and tools, there is an economy of skills in that the project leaders and team members become adept at navigating these steps. Further, by using the same systems and criteria, a scale of evaluation as to the effectiveness of the system is developed so the ability of project teams to do well over time improves.
It was natural that this standardized method would become codified and finally developed into a well-developed system that could that moulds all projects to a single standard. By developing an industry wide method that requires strict training and adherence to the same terms, tool sets and definitions of success, the “intuitive” nature of judging project effectiveness is reduced. And so ‘the Project Management Method” was developed whereby project managers can undergo strenuous and exacting training in a standardized method that would be enforced via certification across the whole of the business community.
Whether or not the PMM represents a curse or a blessing to the business world depends to a large extent on individual applications of the method and measurements and observations on whether the method itself introduces efficiency to the process of project management or just another layer of bureaucracy. .
There are many persuasive arguments to utilizing a methodology that is standardized at an industry level. Those project managers who have gone through the certification process can be depended on to implement that system the same way in each business setting. As such, the process of finding qualified project managers becomes simplified because the certification process alone communicates to the business that it can expect the PMM system to be implemented correctly.
Through the implementation of an external method of certification and measurement of excellence, the project manager career begins to take a high level of professionalism similar to in the legal and medical fields. So the PMM movement represents a maturing of the IT and project management disciplines as they move toward greater levels of accountability and control.
The dangers come in implementation of the PMM methodology on a project by project basis. In order for a PMM certified manager to live by his credentials, all projects must conform to a standard mould. The unique nature of each project may not easily fit into the PMM process of systematization.
In addition, the PMM system is heavily dependent on a large amount of meetings to document that the project is adhering to standards and a methodical documentation process from which there is little room for variation or accommodation. The PMM is a complex methodology so the tool sets that must be used to track the process can be expensive and difficult to use.
As a result of this, the introduction of the PMM system can cause the actual business objectives of the project to take on a secondary priority to the high standards of PMM itself. Project leaders working under the requirements of the PMM can become more accountable to the methodology itself and lose sight of what is good for the business or what is efficient in terms of getting the project completed.
The PMM leaves very little room for creativity or individual judgment and that is problematic because the nature of business problems have historically depended on the judgment and creative problem solving skills of middle management. By dominating the project process with the needs of the PMM methodology, excessive cost is introduced as well as cumbersome requirements that do not benefit the business or the project itself.
Visit Macresource Computer Training for Microsoft Project 2007 training classes
Could On-site Excel Training The Best Solutions For Your Organisation?
March 2, 2010 | Leave a Comment
One of the pivotal components of the Microsoft Office 2007, Excel is a uniquely powerful spreadsheet. If you bought this sophisticated piece of software, it makes sense to ensure that your staff members know how to use it effectively. Having allowed them a week or two to get used to the new environment and go through some online tutorials, you will probably want to get them properly trained. Tutor-led software training has the benefit that delegates are able to ask questions as they learn and have complex concepts explained and demonstrated to them until they fully understand them.
Booking your staff on a public Excel course is of course one possibility. However, increasingly companies are demanding to have this training customised to meet their specific demands. Microsoft Excel can be used for a variety of data analysis and storage tasks: not everyone uses it in the same way. Perhaps you will be using it for complex business modelling. Or, you may be using it to create interactive forms and reports complete with complex calculations. Maybe your staff will be using the program in a database role recording information under column headings. Booking a customised course will ensure that you only pay for instruction which is relevant to your requirements and reflects the way in which you will be using Microsoft Excel.
Before you start contacting Excel training companies, it would be a good idea to ensure that you have a clear idea of what you want to achieve by using Excel and that your expectations are realistic. When you approach training companies, you should make it clear that you do not simply want them to deliver their standard Excel courses but that you require a customised programme of training. Between you, a schedule of topics to be covered should then be drawn up and the duration of the program decided.
The customisation process may also involve identifying different requirements within your own organisation. Different people may need to do different tasks with the program and therefore need different skills. For example, some of your users will be primarily interested in using Excel for business analysis and projection. Their primary areas of interest will be the “What if” analysis tool like goal seek, scenarios and pivot tables. On the other hand, you may have people who are interested in create charts and reports either for printing or for use in PowerPoint presentations.
Most training companies offering customised Excel courses should be willing to accommodate the specific needs of your organisation and the different profiles of the staff members: accounts, sales and marketing, etc. Between you, you can then create a program of study which satisfies the needs of all users. Perhaps this may mean, having different courses for users with different profiles or perhaps the best approach will be a modular one whereby some modules are taken by everyone while others are only attended by certain user groups.
The writer of this article is a developer and trainer with On-SiteTrainingCourses.Com, an independent computer training company offering Microsoft Excel Classes at their central London training centre.
Understand The Microsoft Word Mini Toolbar
February 27, 2010 | Leave a Comment
The Mini toolbar is a floating window which automatically pops up whenever you highlight text. Initially all the options it contains are conveniently dimmed. However, as soon as you position the cursor over its controls, the Mini toolbar becomes activated.
It contains a number of options for formatting your text. All of these options can be found in the Home tab of the Word Ribbon. The top row of icons allows you to change the font, size and colour of our text and also contains Word’s nifty Format Painter. The Format Painter allows you to copy the format of the selected text to other areas of your document.
The second row of the Mini toolbar has buttons for making text bold, italic, centre aligned. It also has tools for changing the colour of the highlighted text, indenting text and converting your text to bullet points.
The Mini toolbar provides users with a very convenient way of quickly formatting your text without having to go back to the Ribbon to find the options they are looking for. However, there are times when it can be an inconvenience. This is particularly true when using the very useful drag-and-drop facility within Microsoft Word.
This useful feature allows you to highlight text and simply drag it to a new location. However, what you sometimes find is that, as you are about to drag, your cursor collides with the Mini toolbar and instead of dragging your text you end up changing its format.
As a user of Word 2007, only you can decide whether you like the Mini toolbar facility or not. If you decide it’s not for you, Word allows you to deactivate the Mini toolbar facility. Choose Word Options from the Office button. The very first option in the Popular category is “Show Mini Toolbar on selection”. If you decide that the you not like the Mini toolbar, simply switch off this option. Now, whenever you highlight text, no Mini toolbar palette will appear.
Even if you have asked Word not to show the Mini toolbar, it is still possible to display it by simply right-clicking on selected text.
The writer of this article is a developer and trainer with an independent computer training company offering Microsoft Word 2007 training courses, as well as Word VBA training in London and throughout the UK.