You need to add the Microsoft MapPoint 11.0 Object Library as a reference in the VBA window.

There is info on how to do this at the top of this page:
Excel User-Defined Functions and MapPoint - MapPoint Articles - MP2K Magazine

I was running into the same issue yesterday, and that was all I had to do to fix it.

Good Luck!