Microsoft Excel 2007 VBA programming. Automated IP Address Checker

As a service to the Excel user community, I’ve written a niffy little Excel VBA program. This program checks a valid IP address and it returns Country, City and Organization information in seconds!
It works for Microsoft Excel 2007 or Microsoft Excel 2010.

Download AEternus IP Address Checker

See how it works:

Screen Shot:


Disclaimer:
Aeternus is not responsible for any loss or damage arising from the use of this free program. Source codes are provided “as is”. To ensure that this program is free from maliciouse codes, make sure you obtain this program direct from Aeternus’s website.

Aeternus answers Excel questions on Yahoo Answers!

True to our mission of “Serving the Excel Community”, we have been answering Excel related questions on Yahoo Answers.
Check out some of the questions we have helped answer:

If you got any Excel questions feel free to post them to us here:

General Enquiries

1. Your Name:(required)
2. Tel:(required)
3. Email:(valid email required)
4. Message

 

Excel 2010. A quick review

Just like driving a new car, I will report the new facelifts Microsoft has done to this spreadsheet program!

Zippy startup and the familar Ribbon

The Excel 2010 starts up quickly and I finally saw how Excel 2010 looks like. There were no earth shattering changes to the command layout and colour scheme. Instead of the familiar blue in Excel 2007, the new Excel 2010 has an off-white color scheme.

Mystery of the Refreshing Ribbons

Excel 2010 had a strangely refreshing look thou I can’t put my fingers on the reasons why.  Excel 2010 felt more organized even thou the commands layout was exactly the same as Excel 2007. The ribbon names and command group names seem to leap off the screen. I could scan the entire ribbon with less confusion and less frustration compared to Excel 2007.

I did a careful screen-to-screen lineup with Excel 2007 and I discovered the reason for this mystery. The team at MS actually did a subtle “cleanup” of the ribbons! There is less color junk and useless lines were removed in the new ribbons.

• The commands are no longer “trapped” in boxes. They are now laid out cleanly with a single line separting the command groups.
• A single background color is used in Excel 2010 instead of double color scheme in Excel 2007.
• The box surround the extire ribbon had been removed in Excel 2010.

A new Office Button

There was more eye candy in store when I clicked on the new green Office button in Excel 2010. The Excel 2010 Office menu tries to simplify the Excel 2007 Office menu. The “Send” command in Excel 2007 was gone. Excel 2010 rearranged “Save”, “Save As”, “Open” and “Close” to the top. The “Excel Options” and “Exit” button in Excel 2007 was placed sensibily along the up and down axis of the new Excel 2010 Office menu.

I love the new Office menu for the following reasons:

• You only need to travel up and down along the new menu system. Previously I had to move my eye and mouse all over the menu to perform the same tasks.
• The most frequent commands are place at the top.
• The confusing “Prepare”, “Send” and “Publish” Excel 2007 commands with its 2 level menus were gone.
• The Exel 2010 menu is sleek, easy to understand and really zippy to use.

You can make your own Ribbons (I saved the best for last)!
I was pleasantly surprised to find a new category on the Excel 2010 options menu: “Customize Ribbon”.

Finally some control over the darn ribbons! I can now remove any existing ribbons from the interface and create new ribbons. This means I can throw away the default ribbons and remake them in my own image! No more confusing ribbons. Halleluah!

Excel 2010. A quick look

Excel 2010 is coming in a few months and I am really excited by the new features. I am waiting anxiously for it to be GA.

Some of the exciting improvements in Excel 2010:

Better Excel Charts

• Sparklines, improved data bars, better chart UI, cross-sheet conditional formatting, improved PivotChart interactivity.
• What does it mean: I expect that you can present great looking charts in your business reports. I’ll definately be presenting what new tricks you can do in dashboarding.

Faster & more responsive performance

• Excel 2010 will feature multi-threaded calculations.
• 64-bit version of Excel, which allows Excel to address more memory.
• What does it mean: Workbooks with complex formulas will update much faster. I even expect my macros to work faster since the calculation engine has been souped up to take advantage of multiple processors on my laptop.

More accurate functions

• Improvments to the accuracy of some math, financial, and statistical functions.
• New functions are added to Excel’s rich function library.

Improvements in programmability

• Improved macro recording support.
• Enabling new integration scenarios with High Performance Computing clusters.
• What does it mean: Overall, I think the new Excel 2010 will bring spreadsheet computing to the next level. I can only imagine the impact it will have on financial modelling with the improvements in programmability.

I’ll be taking the new Excel 2010 for a spin and will write a more detailed review over the weekend. Stay tuned.

Where Microsoft Excel Fails

Your company may have spent thousands of dollars on a fancy financial application but nothing beats the comfort and familarity of a spreadsheet. They are indispensible tools for budgeting and analysis in all sorts of corporate functions like human resource, finance, accounting and the list goes on.

Now imagine among all the thousands of spreadsheets in your department what are the chances that you find errors in them? As reported in Computer World (May 2004) as much as 20% to 40% of Excel spreadsheets have errors. Some researchers would even put it as high as 91%!

Think about the odds next time you are working on a worksheet. Be very very careful.

Learn more about our Excel training program Robust Spreadsheets with Checks and Controls.

Great week of Microsoft Office Power Tips and Tricks

It has been an extremely fun week running Microsoft Office Power Tips seminars. We were invited by NTUC, Wincor-Nixdorf and Calyon between 24th and 26th June 2009.

Registration for both NTUC @ One Marina Boulevard and Wincor-Nixdorf far exceeded than anticipated. The HRD had only expected 20 to 30 pax maximum to sign up. However, registration and attendance exceeded 100. We had NTUC staff, from the different unions all over Singapore, taking time off to attend the seminar. We had Wincor-Nixdorf managers and heads end their meetings before 3 p.m. to make it on time for the seminar. I was well chuffed when Sharifah of Wincor-Nixdorf presented me with a token of appreciation of a certificate and a thumbdrive.

After the last 2 two days, it was a welcomed return to small and intimate sessions with Calyon.

Presenting the Microsoft Office Power Tips and Tricks Seminar at NTUC

It was great to hear from the participants how much they learnt from the seminars. Short but impactful and of immediate productivity. That has always been Aeternus’s message and purpose: It’s not about mouse clicks and features. It’s about showcasing HOW and WHAT magic Excel, Word and PowerPoint can do beyond the standard mouse clicks.

Develop Robust Excel Spreadsheets with Checks & Controls

The use of spreadsheets has crept into daily business operations at the highest levels. Given that many spreadsheets are developed by self taught professionals, there is a likelihood that many critical spreadsheets (eg. balance sheets, portfolio investment models, risk analysis models, revenue calculations) have material errors.

There is a clear and present need to develop error free spreadsheets and adopt these electronic documents into a checks and controls framework to prevent future errors.

In this training program, we learn Excel techniques to:

• Develop robust spreadsheets.
• Prevent formula and user operation errors.
• Audit spreadsheets with powerful formula auditing techniques.
• Foil attempts to conceal data and formulas.

Learn more about our Excel training program Robust Spreadsheets with Checks and Controls.

Control your Excel spreadsheets!

Consider these shocking stories on how a simple Excel error can cause you sleepless nights:

• A cut and paste error cost TransAlta $24 mil when it underbid an electricity supply contract.

• A missing minus sign caused Fidelity Magellan Fund to overstate projected earnings by $2.6 bil and miss a promised dividend.

• Falsely linked spreadsheets permitted fraud totalling $700 mil at Allied Irish Bank.

We tend to use Excel spreadsheets like pieces of paper without care and consideration. I’m willing to bet that many of our most important Excel spreadsheets have material errors.

Learn more about our Excel training program Robust Spreadsheets with Checks and Controls.

Problem of Labour Intensive Reporting in Excel

If you are a business analyst like I was, chances are you create and compile tons of reports in Excel. The business of generating reports is a business itself. The process of collecting your raw data and distilling it into the report are routine and involves many delicate steps. In other words, reporting is laborious. The labor involved will become a natural limit to the amount of meaningful insights you can generate.

Added to this work volume, managers often demand reports to be sent on time, on a regular frequency.  The higher the reporting frequency, the higher the workload.  Any wonder you need to have multiple heads and arms to deal with this?

Learn more about our Excel training course Painless Business Reporting in Excel.

Work with multiple Excel worksheets at the same time

Imagine when you open a workbook, multiple windows will be opened and each window allows you to work on 1 worksheet and they get arranged nicely across a large LCD monitor.  Sounds like a dream?  Well this is easy to do.

1. Open any workbook and click on Window->New Window.
2. Do this 3 more times.
3. Click Window->Arrange. Choose “Tiled” from the Arrange Window dialog box.

At this point you should see 4 windows all pointing to the same workbook. Use each window on the screen to access different worksheets, effectively you are working on 4 sheets at the same time!  Any save command you perform will save all your work to the same workbook.  And get this: next time you open the same file, you get this set up just where you left off!

« Previous Page