USA ComNet

Sometimes, in your Excel VBA code, you need to have a variable which always returns the same value. For example, if your application makes reference to a tax rate, having the rate in a variable will offer you a mechanism for changing the rate wherever you have used it in your application. Thus, instead of entering code like “TaxAmount = SubTototal * 0.15″, you would use statements like “TaxAmount = SubTototal * TaxRate”, where tax rate would be a constant.

In VBA, constants must be declared and initialised in one statement: for example “Const TaxRate as Currency = 0.15″. This is the only time that a value can be placed in the constant and herein lies one of its key benefits; the value it contains cannot later be accidentally overwritten because the programmer confuses this variable with another. The other key benefit is the ease with which constants allow us to update our applications. Thus, in the tax rate example, when the tax rate changes, we only need to modify one line of code and that change will update our entire application.

VBA offers us another variable technique which allows us to “tighten up” the values which may be entered into a variable is to use User Defined Types (UDTs) which, essentially, allow you to define your own variable types. You can then declare as many variables of this given type as you need. Type definitions must be placed at the top of a module; they cannot be placed inside sub routines or functions. The definition consists of a code block inside which the various elements of the type are detailed as well as the data type associated with it. Here is an example of a type definition.

Type Employee

Name As String

Department As String

JobTitle As String

DateOfBirth As Date

StartDate As Date

Salary As Long

End Type

To use create and initialise variables of this type in our code, we use statements like the following:

Dim empNew As Employee

With empNew

.Name = “Gillian Spencer”

.Department = “Information Technology”

.JobTitle = “Web Developer”

.DateOfBirth = 12/05/1982

.StartDate = 25/03/2010

.Salary = 27500

End With

UDTs provide a very useful way of storing related variables in one place. In this regard, they resemble arrays; but, unlike arrays, the elements they contain are named rather than simply referred to by a numerical index.

You can find out more about Excel VBA training courses, visit Macresource Computer Training, an independent computer training company offering Excel VBA Classes in London and throughout the UK.

Microsoft Excel 2007 has many differences from Excel 2003. Therefore, when you or your staff upgrade, you will almost certainly need training. But what kind of training will work best? Do you need an upgrade course that shows you the new features? Do you need a training course that compares Excel 2003 and 2007 and shows you how features have changed and evolved in the new version? Or do you need Excel training to take your skills to a new level, whatever the version?… Well, why not find a course that does all of the above?

One of the first things you should look for in having training on Excel 2007 is a full explanation of how the new interface works. You should be shown the new way of working and learn useful tips and shortcuts which will enable you to become at least as productive in Excel 2007 as you were in 2003.

However, after being trained on 2007, you will want to do more than just get back to where you were with 2003. You will want the training to show you what new features have been added to Excel 2007 and how these new features can benefit you.

One fundamental new feature in Excel 2007 is the dimension of a worksheet which is now about 1000 times bigger (in terms of the number of cells) than previous versions. A good Excel 2007 training course should show you how to fully exploit the space available and how to quickly navigate and manage the larger worksheets that will result.

Pivot tables are a key area where Excel 2007 has made improvements. Ensure that any Excel 2007 training that you book includes first of all an explanation of pivot table essentials (pivot tables often confuse users) and then moves on to the new ways of creating and manipulating pivot tables offered in Excel 2007.

Do you or your organisation use charts and graphics when creating Excel reports? If so, be sure that your Excel 2007 training covers the new and improved charting engine within this new version. You should learn how to fully exploit the new chart ribbons: format, layout and design. You should come away from the training knowing all about SmartArt graphics, adding trendlines, scatter charts and pivot charts.

Another feature that has been developed in Excel 2007 is conditional formatting. Be sure that any training you have on Excel 2007 includes some insight into the use of Excel 2007’s powerful new conditional formatting features especially Data Bars and Color Scale.

The ability to enter formulas and functions into the cells of an Excel spreadsheet is the key feature that has made the program so essential to so many organisations. Excel 2007 has added several new features relating to functions and several new functions. You should insist that any training course you book on Excel 2007 demonstrates these new features and functions such as IfError, SumIfs and AverageIf.

If you want more information Excel VBA training courses, visit Macresource Computer Training, an independent computer training company offering Excel VBA Classes in London and throughout the UK.

The alignment section of the Home Tab of the Excel ribbon contains a number of options relating to the way in which your data is position within the cell. Perhaps the most familiar and the most frequently used buttons in this section are the three relating to the horizontal position your data: left, centre and right. However you’ll notice that when you click in the cells of an unformatted worksheet, none of these three icons is highlighted, which indicates that none of them is the default. The reason for this is that Excel treats data differently depending on the data type.

If you type text in a cell, your text is aligned on the left; if you type a number, the number is aligned on the right; if you type a date, it is also aligned on the right. To change the horizontal alignment, either select a range of cells or click on a column letter to highlight the entire column then click on one of the alignment icons.

Haven chosen one type of horizontal alignment, you can change it in two ways. You can either click on a different form of alignment or click again on the already selected alignment. For example, if your text is centred and you click on the Centre button a second time, this deactivates centre alignment and returns you to the default alignment which, for text, is left. Thus we have, effectively, four types of horizontal alignment: left, centre, right and unspecified (or default), which is the alignment that applies when none of the alignment buttons is highlighted.

Excel also allows you to specify vertical alignment. This setting normally only becomes apparent when you increase the height of the cell and this time there is a definite default which is that text is aligned at the bottom of the cell. This setting applies to text, dates and numbers alike.

To change vertical alignment, either make a selection or click on the row number to select the entire row then click on one of the buttons to make the change: align middle, align top and so forth.

The alignment option also includes the ability to change the orientation of text within the cell. This is particularly useful in those situations where the headings are wider than the data within the cells. To change the vertical orientation of your text, you simply select the cells in question and then choose the appropriate orientation in the Alignment dialogue.

Having changed the orientation of the headings, you can probably make the columns much narrower. Excel offers a very useful way of doing this: simply select all the columns that contain data then in the Cell group of the Home Tab of the Excel Ribbon, choose Format and then AutoFit Columns. This option makes each of the highlighted columns no wider than it needs to be to display all the data it contains.

If you would like to learn more about Excel VBA training courses, visit Macresource Computer Training, an independent computer training company offering Excel VBA training courses at their central London training centre.

Upgrading to Excel 2007 may be something of a shock to you and your staff. The initial reaction of most people is: “where is everything?” Bearing this in mind, you may well find that a training course on Excel 2007 is a good investment. The training should first of all get you past the initial state of confusion caused by the fact that 2007 looks so different from previous versions. Then it should give you some guidance on the new features in Excel 2007 such as the enhancements to charting and graphics, functions and conditional formatting.

At the end of any training on Excel 2007, you should feel that you have grasped the logic of the new interface and be able to navigate the ribbon and find all the basic commands. You should be getting used to doing things the Office 2007 way rather than the Office 2003 way.

However, naturally, your training should not simply return you to the same level of knowledge that you had in version 2003 or 2003. On the contrary, you should be looking to get some guidance on what else Excel 2007 has to offer: the new features and the enhanced features.

The number of cells in an Excel 2007 worksheet is now about 1000 times bigger than in previous versions. If you book training on Excel 2007, you should learn ways of taking advantage of this new space and also pick some tips on efficient navigation and management of this huge area.

Your training should also include demonstrations and explanations of pivot tables, a feature which has been revamped and improved in Excel 2007. If you or your colleagues have not used pivot tables to any great extent before, the training should begin by reviewing basic pivot table concepts before moving on to look at what Excel 2007 has to offer.

Charts have been given a big overhaul in version 2007. So be sure your training includes coverage of the special effects available in Excel 2007 charting and graphics. Your training course should cover such features as the format, design and layout ribbons and how to use SmartArt graphics and shapes. If charting is particularly important in your organisation, then make sure your training covers things like trendlines, scatter charts and pivot charts.

Your Excel 2007 training course should also cover conditional formatting. This is a feature that has been much enhanced in Excel 2007 and your training should show you how to exploit the new features available. Make sure you will come away from the training knowing all about Data Bars and Color Scale.

Formulas are the lifeblood of Excel. They analyse the raw data entered in other cells and provide most of Excel’s awesome power. You should definitely request that your Excel 2007 training shows you the improvements that have been made to the entry of formulas in the formula bar and the key new functions that have been introduced, especially AverageIf, SumIfs, and IfError

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 On-site Excel training all over the UK.

To create a chart in Microsoft Excel 2007, begin by selecting the data that you wish to plot including any headings. You then have the choice of creating either a stand-alone chart or an embedded chart. To create a stand-alone chart, simply right-click on one of your worksheet tabs and choose Insert. Next, click on the chart icon and click OK. Excel creates a chart using the default chart type.

Having just created a chart, Excel will display three contextual tabs headed “Chart Tools”. The contextual tabs consist of Design, Layout and Format. The Design contextual tab enables you to change the chart type and the basic information about the chart such as the underlying data.

The Layout tab provides us with a more intricate level of control over the various components of your chart. For example, by default, Excel adds a legend to each new chart. If a chart has only one series of data, we don’t need a legend to tell us what the chart colours mean. We can therefore click on the Legend drop-down menu and choose None.

The Format contextual tab offers us the most primitive control over the chart elements. It is here that we can to click on individual elements and change them at the object level. In other words, we are working on these elements purely as drawing objects rather than as elements of the chart. However, even at this primitive level, Excel still shows us the relationship between the chart elements and the underlying data. Thus, for example, when an individual data point is highlighted, Excel displays the corresponding worksheet address in the formula bar.

When creating a standalone chart, you are not given the option of choosing the chart type at any point during the chart creation process. If you want to change the chart type, you have to do so at a later stage by choosing a Chart Type option in the Design contextual menu.

As for the chart sheet which contains the chart, this behaves in much the same way as an Excel worksheet. It can be renamed or deleted by right-clicking the sheet tab and choosing Rename or Delete from the context menu.

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 training courses all over the UK.

A Microsoft Excel workbook is actually a container, a bit like a folder. Each Excel workbook can contain one or more worksheets and it is the worksheet that is the actual container of information. Worksheets are identified by a tab which bears the name of the sheet. Clicking a tab activates that particular sheet.

In exactly the same way that Microsoft Excel allows you to hide columns, it is also possible to hide entire worksheets. Hiding a worksheet is particularly useful where you have a workbook that contains a lot of sheets. Naturally, hidden worksheets can be made visible again by using the Unhide command. It is possible to hide either an individual sheet or to hide a group of sheets. However sheets can only be unhidden one sheet at a time.

To hide just one sheet, just right-click on the sheet tab and choose Hide. The corresponding worksheet will then simply disappear. There is also a ribbon command which will do exactly the same thing. First, you highlight the sheet by clicking on its tab and then, in the Cells section of the Home Tab of Excel Ribbon, choose Format-Visibility-Hide and Unhide-Hide.

To hide more than one sheet at a time, simply select the sheets by clicking on the first, holding down the Control key on your keyboard and clicking on each of the others. Next, right-click on any of the highlighted sheet tabs and choose Hide.

To make a hidden worksheet visible again, you can right-click on any sheet tab and choose Unhide. The Unhide dialog then appears. Unfortunately, it isn’t possible to select more than one sheet to unhide; if you try Control-click or Shift-click, you’ll see that only one sheet can be highlighted. Highlight the name of the sheet that you want to make visible and click OK.

If you prefer, you can also use the Excel Ribbon command Format-Visibility-Hide and Unhide-UnHide Sheet. When the Unhide dialog box appears, highlight the sheet you would like to unhide and click OK. You will notice that when sheets are unhidden they very conveniently return to the position that they originally occupied.

You can get up to date information on Excel training courses, visit Macresource Computer Training, a UK IT training company offering Excel Classes in London and throughout the UK.

categories: Microsoft Excel 2007,spreadsheets,Excel 2007 training courses,Excel 2007,Microsoft Office 2007,tutorial,training,computer software,computers,software programs

Often, when you enter numbers into a spreadsheet, you need to ensure that the number format is consistent. For example, if these numbers are prices, you may want to display the currency symbol or you may simply want to specify that the number of decimals displayed is always two.

Unless you specify otherwise, all numbers in Excel are rendered in the “General” format. This means that numbers are displayed exactly as you enter them: if you enter two decimals, two decimals are displayed; if you went to one decimal, one decimal is displayed; and so on.

When setting the number format, the best idea is usually to highlight the whole column. To do this, click on the letter or letters at the top of the column. (Any text contained in the highlighted column will not be affected by the number format you specify.)

Number formats are displayed in the “Numbers” section of the Home Tab of the Ribbon. There are three main formats related to numbers: the first is called “Number”, the second “Currency” and the third “Accounting”. To gain access to the full range of number formats, choose “More Number Formats” from the drop-down menu. Another way of accessing the same dialog box is to click on the launch button in the “Numbers” section of the Home Tab.

When you click on one of the number formats on the left of the “Numbers” dialogue box, you are given a series of choices which enable you to refine the way that the format will work. Thus, if your numbers refer to an hourly rate, you would click the “Number” category in the left column and then specify two decimal places. The option to “Use Thousands Separator” will add the appropriate separator to demarcate thousands. The separator used will depend on your locality: for example, in the UK or USA, a comma will be used; in European countries, a dot will be used.

The final option in the “Number” category controls the display of negative numbers. The default is to display a minus sign in front of the number and leave the colour of the number unchanged. However, you can also dispense with the minus sign and change the colour of negative numbers to red; or you can both change the colour of negative numbers to red and display the minus sign.

Clicking the “Currency” category, reveals pretty much the same choices as “Number” with the addition of a currency symbol. You can specify which currency symbol is used or you can dispense with the currency symbol altogether.

The “Accounting” category is pretty much the same as “Currency”. Once again, you can choose a particular currency symbol. However, you will notice that you do not have any choices relating to negative numbers. The convention in accountancy circles is to always place negative numbers in brackets.

As an alternative to using the number dialog box, you can also click on one of the series of handy buttons which are used to apply each of the number formats with single click. There are also two buttons for decreasing and increasing the number of decimals displayed in the highlighted cells.

Finally, there may be times where you enter a number into a cell but do not want Excel to treat it as a number. For example, if you have a column of data representing an ID, although the ID may be numeric, you may not want Excel to regard it as a number or to modify it in any way. You will probably want it to simply stay exactly as it was entered. Whenever that’s the case, it’s best to format the number as “Text”. The simplest way of doing this is to highlight the appropriate column and in the number dialog box choose “Text” as the category.

If you would like to learn more about Excel and Excel VBA training courses, visit On Site Training Courses . Com, a UK IT training web site offering Excel training courses all over the UK.

categories: Microsoft Excel 2007 data entry,Excel tutorials,Excel 2007 training courses,Excel 2007,Microsoft Office 2007,training,computer software,computers,software programs

PowerPoint audience handouts offer a way of giving the attendees of your presentations something to remember you by. They normally consist of printouts of the presentation; one, two, three, four six or nine slides to a page. Naturally, however, whether or not the essence of your presentation can be captured by this kind of printout depends on the nature of the presentation.

To personalize the look of your handouts, click on the View tab of the PowerPoint ribbon and then click on the Handout Master button. In PowerPoint, masters allow you to determine the format of the three main elements within a presentation; slides, speaker notes and handouts. When you are in handout master mode, the Handout Master contextual tab appears. It contains a Page Setup section which allows you to choose the orientation of both the page as a whole and of the individual slide miniatures. It also contains buttons for activating or deactivating the header, footer, date and page number as well as for formatting the background of the slide.

Given that Microsoft PowerPoint allows you to produce three separate elements (slides, speaker notes and handouts), when the print command is used, you need to specify which of these elements you wish to print. This is done by choosing an option from the Print What drop-down menu. In addition to the three elements mentioned above, you can also print the outline of the presentation.

If you have created a presentation with a fair amount of important detail, it may be more useful to print out the slide outline and distribute it to the audience in place of PowerPoint’s usual handouts. Better still, you can export your presentation into Microsoft Word and then customise it for your audience. To export an outline, from the Office button, choose Publish and then Create Handouts in Microsoft Word.

Using the Create Handouts in Microsoft Word command brings up a dialogue box which allows you to choose one of five page layout options. Firstly, you can have speaker notes next to slides. This will create a two column layout with a slide miniature in column one and speaker notes next to it in column two. If you have used the speaker notes feature in your presentation, this may be a useful solution. The second option is Blank Lines Next to Slides: this produces the same two column layout as the first option but the right hand column is blank, so that you can enter notes next to each slide.

The options Speaker Notes Next to Slides and Blank Lines Next to Slide don’t offer you much room for text. If you have made or wish to make extensive notes on each slide, options three and four (Notes below Slides and Blank lines below Slides) provide a layout with the text below the slide miniature and leaves approximately 60 percent of the page free for notes.

Perhaps your main aim is for the audience to take away a summary of the content of the presentation. In this case you can choose the final option: Outline Only. This simply exports the text on each slide into Microsoft Word.

As is often the case when transferring data from one Microsoft Office application to another, you have the choice of activating the Paste Link option. This will create a link between the exported file and the original PowerPoint presentation, such that, if the presentation is modified, the exported Word file will also be updated.

For more information on Microsoft PowerPoint training courses, visit On-site Training Courses . Com, a UK IT training web site offering PowerPoint Classes in London and throughout the UK.

categories: Microsoft PowerPoint 2007,slide presentations,PowerPoint 2007 training courses,Microsoft Office 2007,tutorial,training,computer software,computers,software programs

When you upgrade to Excel 2007, you and your staff may be in for quite a shock. The initial reaction of most people is: “where is everything?” Bearing this in mind, you may well find that a training course on Excel 2007 is a good investment. The training should first of all get you past the initial state of confusion caused by the fact that 2007 looks so different from previous versions. Then it should give you some guidance on the new features in Excel 2007 such as the enhancements to charting and graphics, functions and conditional formatting.

One of the first things you should look for in having training on Excel 2007 is a full explanation of how the new interface works. You should be shown the new way of working and learn useful tips and shortcuts which will enable you to become at least as productive in Excel 2007 as you were in 2003.

However, naturally, your training should not simply return you to the same level of knowledge that you had in version 2003 or 2003. On the contrary, you should be looking to get some guidance on what else Excel 2007 has to offer: the new features and the enhanced features.

The Excel 2007 worksheet is much bigger than its 2003 counterpart; about a thousand times bigger in fact. Your Excel 2007 training should show you how you can exploit this huge area when designing your spreadsheets. Some decent navigation tips would also be helpful.

Pivot tables have been considerably improved in Excel 2007. However, given that so many users are a bit vague on getting the best out of pivot tables, why not ask that your training on pivot tables begins with a review of fundamental pivot table concepts before moving on to look at how Excel 2007 implements pivot table features.

Charts have been given a big overhaul in version 2007. So be sure your training includes coverage of the special effects available in Excel 2007 charting and graphics. Your training course should cover such features as the format, design and layout ribbons and how to use SmartArt graphics and shapes. If charting is particularly important in your organisation, then make sure your training covers things like trendlines, scatter charts and pivot charts.

Conditional formatting has been around in Excel for some time. However, this feature has now been considerably improved. If you are getting trained on Excel 2007, you should definitely make sure that you will learn about these improvements and how to use the Color Scale and Data Bars features.

The ability to enter formulas and functions into the cells of an Excel spreadsheet is the key feature that has made the program so essential to so many organisations. Excel 2007 has added several new features relating to functions and several new functions. You should insist that any training course you book on Excel 2007 demonstrates these new features and functions such as IfError, SumIfs and AverageIf.

For more information on Excel VBA training courses, visit Macresource Computer Training, a UK IT training company offering Excel VBA Classes in London and throughout the UK.

categories: Microsoft Excel 2007 training,Excel 2007 training courses,Excel 2007,Microsoft Office 2007,training,computer software,computers,software programs

As well as macros that can be run manually, in Excel VBA, it is possible to create macros which are executed automatically when a certain event takes place in the Excel environment. One such event is the opening of a workbook. There are two principal methods of creating macros which run when a particular workbook is opened, both of which involve placing code inside the workbook itself.

Perhaps the simplest technique is to create an event-handling macro within the code module of the workbook. The second is to create a macro in a regular module and give it the special name “Auto_Open”.

If you are taking the event-handling approach, double-click the “ThisWorkbook” object in the Project Explorer window of the Visual Basic Editor. This opens the code window of the workbook object. Next, choose “Workbook” from the drop-down menu in the top left of the code window. Excel will automatically create the default event-handling subroutine for a workbook object which just happens to be the “Open” event. Your code window should now contain the following subroutine:

Private Sub Workbook_Open()

End Sub

Now you can simply insert the code you would like to run when the workbook is opened.

For method two, you must begin by inserting a regular VBA module, by choosing Module from the Insert menu. Inside the module, enter the following code:

Sub Auto_Open()

End Sub

Once again, just insert any code you would like to execute when the workbook is opened.

Both of the above techniques achieve a similar result; but there is one key difference between them. The “Auto_Open” macro will only execute if you manually open the file: if the file is opened programmatically by another macro, the “Auto_Open” macro will be ignored. By contrast, the event-handling macro will run whenever the workbook is opened, either manually or programmatically.

If you are writing code which opens a workbook with an “Auto_Open” macro inside it, you can still launch the macro by writing a line of code similar to the following.

Application.Workbooks(”WorkbookX.xlsm”).RunAutoMacros xlAutoOpen

If you would like to learn more about Excel VBA training courses, visit Macresource Computer Training, an independent computer training company offering Excel VBA training courses at their central London training centre.