Excel Pragma

Without Protection

You can protect a spreadsheet so that data is shielded from inadvertent entries.
There is another way to set up a scroll area that does not involve protection.
Open the Control Toolbox (right-click any existing tool bar) and click on the Properties icon.

In the Scroll Area text box, type the scroll area range, or type the defined Name for the range.
To cancel the Scroll Area restricted range, clear the Scroll Area text box.

If you are using Office 2007, the Property icon is on the Developers tab.

Multiple areas can be selected using Protection, but only one area is allowed using the Scroll Property.

Via Unofficial Microsoft Office Stuff

Here’s another article from Microsoft’s Office Developer Center : Convert VBA Code to Visual Basic .NET When Migrating to Visual Studio 2005 Tools for Office .

Why Migrate to Visual Studio 2005 Tools for Office :

• Improved development productivity
• Improved security model
• Easier deployment and versioning
• Added functionality from the Microsoft .NET Framework
• Improved code reuse
• Easier integration

Here’s where I think problems might occur for VBA immigrants :

• different data types (Long, Short, Integer & Co.)
• dates manipulation (.fromOAdate, .toOADate methods)
• no more Option Base (never used it)

What I like :

• Scope of variables : variables can be defined even within a For … Next loop instead of being defined for the whole procedure or function.
• Exception Handling with new statements like “Try … Catch ex As Exception … Finally… End Try” for error trapping.

It doesn’t look like the migration would be too painful. I suppose it’s just a matter of getting used to the object model, the user interface and the error trapping…

hmmm… rephrase : If you manage to get used to the object model, the user interface and the error trapping then the migration should not be painful.

rephrase : I am not a reference. so good luck.

VSTO has been there for a while. But like every other Exceler loyal to his VBA, I’ve signed the petition for classic VB , especially with the deadline on VB coming very soon (here’s a Google for more details).

Lately, my curiosity took the best of me and got me to initiate contact with the other side. so I found some interesting videos at Microsoft Visual Studio 2005 Tools for the Microsoft Office System : Video Tutorials … and my curiosity has grown even stronger.

Has any of you moved ? how was it ?

A great tip from the Spy Journal : Using the F9 key in the formula bar in Excel

If you have a long formula in the formula bar and you want a quick way to see the results of part of the formula then you can use the F9 key. WARNING! Be careful how you use this because this will actually change the formula if you save the changes.

Interesting article on MSDN : Creating XML Mappings in Excel 2003

Walk through common XML tasks in Microsoft Office Excel 2003 to learn more about the new XML functionality in Office 2003 Editions. Learn how to add custom schemas, work with XML maps in Excel, and create a series of mappings based on various schemas. (19 printed pages)

On the newsgroups, I saw a question about detecting duplicate entries in a column. Jan Karel suggested the pre-emptive approach with data validation which will not allow the user to enter a duplicate entry.

There are cases where duplicate entries have to be allowed but a visual indication indicates the duplicate values. Conditional Formatting comes in handy in those cases :

Suppose B1:B20 holds the data. the conditional formatting should be applied by selecting the range and applying the “Formula Is” is : =COUNTIF($B$2:$B$20,B2)>1.

Duplicate data can still be entered but is automatically indicated :

Even more impressed with Wordpress 1.5. It supports pages and is truly becoming a one-stop shop for blogging. (shop ? don’t think so… it’s still for free!)

So ladies and gentlement, my first page : software I use, inspired by Daily Dose of Excel’s About page.

Daily Dose of Excel » JKP Application Development Services Gets Syndicated

Jan Karel recently added RSS to his excellent Excel site, ADS. I’ve subscribed via Bloglines. Clearly RSS is a terrific technology for websites that are updated frequently, like Excel Overdose Daily Dose of Excel. But it may be even more useful for static websites that are updated infrequently. ADS is not a very good example of that because Jan Karel is updating like a bat out of hell, but there are plenty of Excel sites out there that only add pages once per month or once per year. I think RSS would be invaluable on those kinds of sites.

I use FeedDemon (Bloglines and NewsGator Online Services integrated)

Finally ! it was more stressful to plan than difficult to implement. Like all other to do’s , the tough part is finding the time to do the tasks than actually doing them. So today, I upgraded WordPress to version 1.5 – Strayhorn . It is very hard to believe that this beautiful and powerful engine is free !

The upgrade is amazingly smooth but it was the smaller details that took all the time and attention. For example, WordPress now supports themes, so out goes myuglycolors.css. BUT (and there’s always a BUT) deciding on a new theme is not so easy, so I hope you enjoy the out-of-the-box WordPress Classic Theme. wasn’t that hard, was it ?

There will be a few posts shortly with new string UDFs, mostly the result of the previous weeks’ silence. This was all part of the effort to create a toolkit to process client static data and automatically generate the Oracle insert/update scripts.

The Browse For Folder is particularly useful when you’re running a file analysis routine on a specified folder or trying to specify an output folder for a list of files (e.g. SWIFT files).

On a personal note, I will try to keep this blog from turning into a glorified “Favorites” folder. but there were so many times that I needed the “browse for folder” functionality and had to go search for it again and again.