Pro-business Excel VBA Programming

I am a little bit off topic with this one but if someone in my office is having this kind of problem then most likely some of the million of users of MS Word around the world are also searching for a way to solve it.


Here’s the scenario:

You have many pages, let’s say 30, of enumerated items using “Bullets and Numbering” function of Word having two or more sub-bullets in each bulleted items and you want to change the Tab spacing of one of the particular level of all the bulleted items. If you select each and adjust the spacing one by one, that is a tedious task to do, will take you forever to finish and will probably cause you to make your hands to have carpal tunnel syndrome.


Solution:

Right click on the items and choose ‘Select Text with similar formatting’. It will highlight them all and you can change the entire tab spacing of items with same formatting at one time.

Sometimes in your business application programming using VBA, you'll find yourself in a situation wherein you need to rename a file. Macro Recording at this point will not be helpful as you cannot do the renaming in Excel interactivity. Here are two ways of doing it at a VBA level:

Sub DoRename()
Name "c:\test.xls" As "c:\test2.xls"
End Sub

Another way of doing it is by using the CreateObject function:

End Sub

Another function of this two routines is that it can be used to move a file from folder to another. Here are examples:

Sub DoMove()
Name "c:\folder1\test.xls" As "c:\folder2\test.xls"
End Sub

Moving a file using CreateObject function:

End Sub

A few days ago, somebody asked me how to limit the scrolling of a worksheet as soon as he opened his workbook file. He intended to make the first sheet as a Title Page so he should find a way to do it. At first, I thought it was just a simple Excel "setup" workaround at the Tools menu. But after a few minutes of looking around it, I didn't find a way to do the task (or maybe there was one but I didn't find it). So, I ended up making a simple Macro program at the workbook's "Open" event. Here's how I did it:

Private Sub Workbook_Open()
Sheets("Sheet1").ScrollArea = "a1:f10"
End Sub

The code limits the scrolling of the worksheet from cell A1 to F10 only.

Try it.

In my other blog Blogging Startup Make Money, I have discussed how I made my Firefox faster than usual. Please bear with me, but I believe that I don't have to re-post it here so I will point you to my other blog site for the tips. It's worth it anyway.

By the way, If you are not yet using Firefox, this is your chance to get one for free by clicking the orange Firefox button at the lower part of the right sidebar.

Click here for the tips: 6 Simple Steps To Speed Up Firefox

One good thing that Microsoft included in MS Excel is the capability to validate data entered in a cell without macro programming. This saves time. The programmer can concentrate on the business logic of the program and not data validation coding. Here's a video from YouTube that teaches how to do the validation:

Is your Excel and other application running slowly? I came across this very good tips on how to make Windows XP run fast. Watch the video below: