Quality, Affordable & Flexible

Take advantage of our public IT courses’ Buy 2 Get 1 FREE package!!
Register 2 Pax & the 3rd Pax will be FREE!

Quality & Satisfaction Guaranteed!

Ex-participants who require a ‘refresher’ course of the same title are welcome to join the public classes at Centrilinc again for FREE!
There will be no time limit, as long as the required courses are available in the public schedule!

Only experienced & certified trainers with above average participants’ ratings will be assigned to the classes.

Affordable

The ‘Essential Series’ courses work out to an average of only RM116 per day!

VBA - Errors in macros* _^1/2/2002

Ever had a macro running perfectly one day and the next day errors keep on popping...

Formulas - File Name

It is always a good idea to insert the file name & path and include it in the print range so that you will know the filename & it's location when you need to find the file. Just type in this formula =cell("filename")...

VBA - Killing Files* _^1/12/2001

Killing or deleting files is easy. However the files must not be in used.

Sub Killfile()Read more

Overcoming common problems - VLOOKUP

Unable to do a lookup? Entries look the same but lookup returns #N/A error. This is a formatting problem. To overcome this you have a couple of options. One is to copy the data from Excel and paste it to Notepad. This strips the...

Overcoming common problems - Date

Getting wrong date formats when entering a date? Let's say you entered 1st April 2010 in excel, but upon pressing the Enter key the date shows 4th January 2010 instead. This is because the window setup for dates is in the American date sytle....

Formatting - Number Formats

Let's say your numbers need to be 4 digits long. 1 to be shown as 0001, 2 as 0002 and so forth. Click Format/Cells/Number/Custom go to the type text box and type in 0000 & click ok. The advantage of this...

Formulas - Dates

Excel recognises dates whether you use slash ("/") or hyphen ("-"). However if you use full stop (".") Excel would assume the date as a text. Therefore you will not be able to customise the date styles or even perform calculations with the date. So dates...

VBA - Carriage Return* _^10/11/2002

Sometimes you may want to put a line of text on the next row and not let...

VBA - Excel Functions* _^8/2/2002

Using Excel functions in VBA is almost the same as using them in a spreadsheet. For example...

VBA - Current Cell Content* _^24/12/2001

Sometimes we need to know what the cell contains ie dates, text or formulas before taking a course...

Moving Around Excel

Everybody knows how to ride a mouse (er... move a mouse). Sometimes the keyboard is faster. Well here's a list of controls.

Page Down = To move 1 page down
Page Up = To move 1 page up
Alt Page Down...

Rows - How to Adjust Row Height

Equally simple. Just like adjusting columns, point between the row headings (That's the numbers on the left of the rows) the pointer will turn into a double arrow head. When you see the arrow head, click & drag up or...

VBA - Deleting Range Names* _^15/3/2002

To delete all the range names in your workbook, this macro will do the trick.

Read more

Formulas - More Totals

Lets say you need to total numbers that are not in a range, example a1, a5, a10. Well normally the formula would be =a1+a5+a10. If you have 10 cells to total up that would be slow coz you need to type in "+"....

VBA - Deleting Empty Rows* _^27/10/2001

To delete empty rows in a selected range we can use the following macro. The macro...

Standard toolbar & Formatting toolbar sharing one row

Again Office 2000 applications tries to save space buy cramping both toolbars into one row. Land is an expensive commodity these days!!! So, just point at the light grey vertical line at the begining of the Formatting toolbar until you see...

VBA - Goto (a range)* _^27/10/2001

To specify a macro to go to a specific range you can use the Goto method....

VBA - Current Cell Position* _^10/3/2002

Sometimes we need to know the current cell position. This would do the trick.

Sub...

VBA - Current Date* _^24/12/2001

It's a good idea to insert the current date when you save the file so that you...

More Tool bar

If you're sharing a computer, there's always a smart alec who likes to mess up your toobars. To reset them back to the default style, Right Click any tool bar, Customise, Click the Tool bar tab, select the tool bar...

VBA - For, Next Loop* _^15/1/2002

A point to note is, try not to use the For, Next loop because this method...

VBA - Error Trapping* _^4/1/2002

Trapping errors are important as users can do marvelous things to mess up you macros. Here you...

Formulas - Text / Strings

There are times you need include numbers or dates together with your text. Typing it in doesn't do much if the numbers keep on changing because it's a formula and you want it linked to your text. Example you need your text to say...

VBA - Close All Files* _^23/3/2009

Sometimes you may want to close all files without saving. Doing it manually is a hassle...

Formatting - Auto Format (more suitable for tables)

For those who may not have time to format or simple have got an awful sense of colour selection, you could use Excel's Auto Format. First select you range, Click Format / Auto Format and select your choice of...

Formatting - Text Formatting

There are time you just want to squeeze everything into one cell. Other than adjusting the column width, you could reduce the font size either manually or automatically. To do it automatically, go to format cells (Ctrl 1), select Alignment, and...

VBA - Duplicates (Highlight duplicates in Bold Red)* _^27/10/2001

There are times you need to highlight duplicate data in your worksheet. This macro does...

Quick adjustments to a column (Auto Fit)

If the contents of the cell cannot fit the column & you want it to fit just nicely, you could use auto fit. Just point between the border (just as before) and double click. Wow!

VBA - Current Cell Address* _^30/6/2010

To get the current cell address (or current range) to perhaps incorporate into your formula, you...

Copying & Pasting

There's more to it than just copy (Edit/Copy or ctrl C) & paste (Edit/Paste or ctrl V). You can copy and control exactly what you want to paste. Example you need to copy a total (where you used a formula)...

VBA - Going to the 1st Sheet* _^27/10/2001

You can select the first sheet of the workbook without knowing the name of...

Disappearing Toolbars

How do you turn on your toolbars? Very simple, point at any tool bar & RIGHT CLICK and select the tool bar that you want to appear. Like I always say, when in doubt point & right click.

Row Height related problems (Text Alignment)

When your row height is much higher the your font, you will notice the contents will be anchored to the bottom of the cell. And that's a bit ugly. What if I want it to be in the centre instead. No problem. Select...

VBA - Hiding Sheets* _^27/10/2001

To hide your worksheet from users you can use the following code.

Sub HideSheet()
Sheet1.Visible =...

VBA - Joining Text Together* _^10/3/2003

There are times where we import text file into Excel an we get text that are...

Formulas - More Dates

Do you want excel to use the system date? Use this formula =today() and presto you can see the system date. Cool huh? But if you just want the system date without using the formula just press ctrl : and Excel...

VBA - Functions* _^31/1/2002

Creating function is useful as complicated formulas can be made easier in code than in a spread sheet....

VBA - Hiding Excel* _^3/9/2002

You can hide the Excel application with this macro. This disables the user from using the excel...

Formulas - Totals

Everybody uses “totals”, right? See the example above. Most users would create the total on row 3. If they need to expand to insert more rows, generally they would tend to insert above row 3....

VBA - Counting Rows & Columns & Sheets* _^27/10/2001

When you have selected a range, it is sometimes useful to know how...

VBA - Flickering Screen* _^27/10/2001

Sometimes when you run a macro, the screen flickers a lot due to the screen updating itself....

VBA - Input Box* _^27/10/2001

When you need to get input from users, you can use input boxes. This macro will ask...

Formatting - Borders

Need to draw tables? If you noticed your formatting tool bar, the border icon has pretty limited border styles. Where the heck do you go to if you want some fancy borders with colours? It's actually under our nose. Click Format/Cells/Border and here you...

VBA - Inserting Rows* _^4/1/2002

To insert rows required by a user is easy. Here the input box is used so that...

There are lots of short cuts in Excel. Like I always say to my students, when in doubt, Right Click. Whenever you point & right click at an object, a menu associated with the object will pop up. There is no need to remember the item on menu bar. Moreover,...

Columns - How to Adjust Column Width

This is a simple job. Don't sweat it. When you point between the column headings (That's the Alphabets at the top of the columns A, B, C .... etc) the pointer will turn into a double arrow head. When you see the...

Overcoming common problems - Sorting

Unable to sort your dates correctly? If the dates are entered as a text (eg key in as '1/4/2010) then sorting would give you undesired results. You could use the TRIM function to get rid of all the ' in your dates.

VBA - Copying A Range* _^5/1/2002

Copy data from a specific range can be done with this macro. Here data is copied...

Overcoming common problems - Data Entry

Entering data in rows & column can be pretty tedious especially when you have wide columns and the great number of columns. Excel has a data form feature that makes data entry a breeze. Select any part of your table and Click on...

VBA - GoTo Sheet* _^18/3/2005

Sometimes we have many sheets or sheets with long names & we cannot view them all. You...

Formatting - Centres Text Across Columns

When you need to centre text across cell, the most common method used would be the merge & centre tool. However merging cells could cause other problems when selecting a range or when trying to perform a copy and paste. I would prefer...

Formatting - Clearing Formats

Sometimes we inherit files belonging to someone else who has a bad taste in colour, fonts & borders. You want to clear all formats quickly before you get sore eyes. What do you do? Simple, highlight the range to clear, click Edit/Clear/Formats and...

Formatting - Date Formats

What if you want your dates to show Sunday, 25 December? Simple, select the cell with the date, click Format/Cells/Number/Custom (If you are too lazy just press Ctrl 1 remember?), go to the TYPE text box and type in Read more

VBA - Adding Items to a combobox* _^15/3/2002

To populate a combobox or a listbox is the same. You could add from...

Sheets - How to deal with them

To move a sheet, simply click on the sheet tab & drag to the left or right.
To rename a sheet, double click on the sheet tab & type in the new name.
To copy a...

VBA - Emailing Workbook* _^2/12/2001

To email your current workbook the following code.

Sub Email()
ActiveWorkbook.SendMail recipients:="julsn@yahoo.com"
End Sub

Formatting - Quick Formatting

It's a rat race out there sometimes. Short cuts do help & is faster then reaching for the mouse. Especially if your mouse is not working properly (pretty common problem for some of us huh?)

Bold = Ctrl B
Italic = Ctrl...

Text Boxes (for exact text placement)

When you need place your text on an exact spot, (for instance when you need to print on pre-printed forms) adjusting the row height & column width can be a big headache coz when you adjust, all other text will move. The best...

VBA - Active Cell* _^5/1/2002

An active cell is the current cell that is selected. This term is used in many macros....

VBA - Auto Run* _^24/12/2001

Making your macros run automatically when opening your workbook. You can either use the Auto Open method or the...

Menu Bar

The menu bars for Office 2000 applications tend to help you save time by displaying the regularly used commands instead of the full list of commands and to display the full list you would have to point & wait a couple of seconds or click the double...