Issue 10780

In a Basic Macro the type "Currency" is not supported in basic operations, such as addition, multiplication, etc.  Additionally in standard spreadsheet functions the Currency type is not recognized correctly.

A straight forward example shows one case I can definitely reproduce (run this an the "Object variable not set error" appears on "a = a * ..." :

Sub Main
	Dim a as double
	a = 15
	a = a * SampleCurrency()
End Sub

Function SampleCurrency( ) As Currency
	SampleCurrency = 47.50
End Function

However, it appears that Currency doesn't involve itself with standard conversions and I had a lot of difficult getting it to work (eventually I gave up and switch my macros to Double and everything worked fine).  That is, in some situations multiplying a value by a Currency will equal 0, other times it will fail, other times it will work.

Here is one more case I found with the SUM function which indicates a problem.  Using these two macro functions:

Function CurrencyArray( ) As Variant
	Dim result( 2, 0 ) As Currency
	result( 0, 0 ) = 5.0
	result( 1, 0 ) = 7.5
	result( 2, 0 ) = 20
	CurrencyArray = result()
End Function

Function DoubleArray( ) As Variant
	Dim result( 2, 0 ) As Double
	result( 0, 0 ) = 5.0
	result( 1, 0 ) = 7.5
	result( 2, 0 ) = 20
	DoubleArray = result()
End Function

And then the following two formulas in the spreadsheet and their resulting displayed amount:
=SUM( CurrencyArray() )   // = 0  (Incorrect)
=SUM( DoubleArray() )     // = 32.5 (Correct)

In the end I decided to use Double instead, but it has precision problems not being decimal (for our company and our profits this doesn't appear to cause a big impact at the moment though :)

BTW, I almost went insane trying to figure out how to pass ranges of data back and forth between Macros and the Spreadsheet, the syntax is extremely picky and has about 100 pitfalls (all of which I fell in at some point).  So if perhaps I did something obviously wrong I'm not surprised...