donderdag 28 februari 2008

Still awaiting my upgrade, but interesting freebee

I'm still waiting for my copy of VS 2008 Professional, but I've caught a very nice freebee from VistaDB. It's called VistaDB Express Edition and you can look at . It is a very interesting database engine because:

1. It's written in managed code
2. It's tiny (yes it's darn tiny)
3. It's powerful despite its tiny size
4. It's SQL language compatible with SQL Server Express
5. It get updated regularly
6. The databases created are also very tiny

There's one but: although its SQL Server compatibility, not everything works at this time like CURSOR and FETCH.

Despite of these small limitations this DB is far better than Jet/Access and even SQL Server CE!
You'll hear from me regarding this database!

You can download it at and give it a try

donderdag 21 februari 2008

Merging-cells nightmare and its solution

I've gone through a stupid nightmare merging (combining) a continuous series of cells into one cell.

Suppose there's an empty worksheet and the selected cell is A1

Do the following:

1. Type something into cell A1
2. Select the following cells A1, B1, C1 and D1
3. Locate the button Merge cells into one

Changes are great that absolutely nothing happens.

I've repeated this using the macro recorder in order to see what happens under water and I've learned my lesson:

The instruction 'Range("A1, B1, C1, D1").Select' gets generated when the CTRL key is pressed while selecting cells. The latests cell looks like to be in an edit state. This kind of selection fails the merge! You find a selection of loose cells separated with commas. Although consequent, Excel interprets this if it wasn't, since the merge function doesn't work.

The instruction 'Range("A1:D1").Select' gets generated when the SHIFT key is pressed whilst clicking the cell D1. The latest cell is like the first one normally selected. This kind of selection succeeds the merge! Watch the colon which means a consequent area of cells and the merge works like a charm!

Now I'd like to share you the ultimate solution for mistakes in merging cells. I've thought for a short time that there was a bug in Office 2007!! Happily this thought is false!
When selecting cells don't ever touch the CTRL key or nothing will be merged! The right steps for a successful merge are:

1. Select the first cell of the region to be merged into one cell
2. Whilst pressing the SHIFT key select the last cell of the region
3. Now there a valid consequent selection, locate the merge-cells-into-one-cell function on the ribbon and use it

zondag 17 februari 2008

The best way to install large software titles

I'd like to share you my experiences regarding installing large software packages like Office packages or development packages from Borland or Microsoft.

There are two situations possible:

1. You are running the operating system natively installed on a computer
2. You are running the operating system as an application under virtualization program like Vmware

Before installing a huge software package there are some preparations needed in order to gain better performance:

Ad 1. Disconnect the computer from internet and disable the antivirus package for this session only.
Ad 2. Disable internet and antivirus package for this session only.

Now it's time to install the package. I've had to install two big fat packages: Office 2007 Home and Student and Access 2007 upgrade. The performance of installing both packages was pretty good. Both large packages installed in a mere 30 minutes, because of the temporary absence of antivirus and disabled internet. After the installations are completed you cannot activate both packages yet, because in the first attempt there was no internet available for this time and you will surely fail the exam called "Product Activation".
Now installation is completed there are a few post-install actions required for activating the software if needed:

Ad 1. Connect the computer to internet and re-activate antivirus.
Ad 2. Enable internet and re-activate antivirus.

Now it's time to reboot the computer and wait for some updates to be applied over the newly installed software.

After this it's time to activate the software when needed. In my case with Office 2007 and Access 2007 it was necessary to activate and now I've passed that exam.

For large updates as known as service-packs it is advisable to treat them if they were huge installs. With internet and antivirus enabled the performance will be poor due to security checking. Temporary disabling internet and antivirus will also put security offside for a while, but this isn't harmful, since internet is also temporarily disconnected. I've had to wait a long time for running service pack 1 of Office 2007 because of the fact that both internet and antivirus were activated during update session. It's better to download an offline update version of the service-pack of Office 2007 or another application and manually apply it whilst internet and antivirus were disabled for that time.

maandag 11 februari 2008

Some important differences between VBA and a .NET language

I'd like to explain my objections against VBA and my advocacy for a .NET language by going through some points:

1. In VBA exception handling is not so clear. It's a pretty rough approach:

In this example there's no error handling. If this is a deployed piece of software, it will crash upon a division by zero. We can expect an upset customer, because the application has crashed!

Sub MyRout()
Dim i as single, f as single, g as single
f = 1.0
g = 0,0
i = f / g

End Sub

In this function we ignore the error at all. We don't care whether or not there's an error. If the program doesn't crash, it might be ok. NOT! Ignoring error will definitely lead to hard to find bugs! This is absolutely a deathsin!

Sub MyRout()
Dim i as single, f as single, g as single
f = 1.0
g = 0,0
On Error Resume Next
i = f / g
On Error GoTo 0
End Sub

Now I present you the best approach of trapping an error using the rough way, but the code look ugly because of labels and GoTo statements.

Sub MyRout()
Dim i as single, f as single, g as single
On Error GoTo FAILED
f = 1.0
g = 0,0
i = f / g
Exit Sub
MsgBox "We tried to divide by zero!"
End Sub

Consider there was a database update using code instead of SQL statements using DAO and we ignored any runtime errors at all, the customer might assume the database got correctly updated, because of no error message, but under water some terrible things have happened! The database didn't get updated! This is why "On Error Resume Next" without checking is so dangerous!

Now look at the next .NET right way to trap for errors:

Sub MyRout()
Dim i as single, f as single, g as single
f = 1.0
g = 0,0
i = f / g
catch (ex1 as DivideByZeroException)
MessageBox.Show("You tried to divide by zero!")
catch (ex2 as Exception)
MessageBox.Show("Unhandled exception. This shouldn't happen...")
'Doing some cleanup code whether or not an exception occurred
end try
end sub

This looks pretty! If you omit to protect your code, your application will crash, but the error message is pretty polite. During debugging time Exception this new way are a real blessing to find and slay bugs.

2. Programming properties

In VBA there are 3 statements to define properties:

Property Let/Set/Get

I'll give you an example:

class MyClass
dim mQuantity as integer

Property Let Quantity(value as integer)
mQuantity = value
End Property

Property Get Quantity as integer
Quantity = mQuantity
End Property

end class

There are two Property statements and they have to be semantically equal!

Now the .NEaT manner:

Class MyClass
private mQuantity as Integer
property Quantity
return mQuantity
end get
set(value as integer)
mQuantity = value
end set
end property
end class
End Class

This looks very pretty and pleasant! A property has a reader and a writer!

Why do I want to get rid of Visual Basic for Applications?

Visual Basic for Application is the macro (hum...) language of many Microsoft Office programs. It's not truly a macro language, but it's a real program language, but somewhat old-fashioned.

In my humble opinion VBA has some serious drawbacks:

1. It's based on ActiveX/Com which means it can clobber up the registry
2. Using On Error GoTo for dealing with exceptions can be a pain in the neck and it's ugly
3. It's a huge package
4. Especially with old Office systems like Office 2000 the help system might get damaged
5. Programming properties using VBA is not very elegant
6. It's based on Variant datatyping, which can cause headaches during debugging
7. Sometimes the code-completion doesn't work well despite using type-libraries of the Office objects.
8. The code is stored into the template or the document and if this stream is damaged, strange error do happen like unexpected failures.
9. VBA is known to be sensitive for serious security problems due to 'macro-viruses'.

In my opinion .NET (managed code) has the following advantages:

1. VB.NET is a true object oriented language
2. Exception handling is clear and easy to read and modify
3. It's managed code and usage of the registry is limited (sometimes it isn't needed at all!)
4. It's type-safe
5. The .NET system is far more secure than ActiveX/Com
6. Unhandled exceptions now lead to meaningful exception messages instead of blunt COM error codes. Yes error messages containing only a number are very insulting (crude) to users!
7. The help system never failed me yet!
8. Programming properties is pleasant to do due to a very pleasant syntax of any language you want.
9. The custom code gets compiled in a DLL instead of a stream into the document. This also means a better loading time of the document or template.

The very beginning

I've now installed a copy of Office 2007 Home and Student and a copy of Access 2007 upgrade. I'd like to get rid of Visual Basic for Applications and replace it with managed code from .NET 3.5 using VS 2008 Professional Edition. I'd also like to blog about Remobjects Chrome an implementation of Object Pascal somewhat different from Delphi.

There are some considerations which must be respected:

Don't advocate illegal software like warez, serials or cracks!
Don't post commercials!
No spamming!

Comments containing such content will be moderated by me and afterall:

Be respectful to everybody and his/her opinion!