Problem with varient array

I have a multi-dimension array with one string, one currency and three integers. When I access the currency value, it works fine as long as the value is less than 1000.00. Anything larger than that returns 1 when I access it. Assume the currency value is in column 1 so

var a As Currency = myArray(i,1) should return the proper value.

if I use

var a As String = myArray(i,1) I get the proper string value

but if I try

var a As Currency = myArray(i,1).CDbl it won’t compile.

The code below works but seems cumbersome. Is there a better way to handle this?

var a As String = myArray(i,1)
var b As Currency = a.CDbl

Some attention is needed here:

Var arr() As String
arr = Array("1000.23", "1,000.23")

Var c As Currency
c = arr(0).CDbl    // 100023 because my locale uses comma as decimal
break
c = arr(1).CDbl    // 1.0002 because my locale uses comma as decimal
break

Var arr() As String
arr = Array("1000.23", "1,000.23")

Var c As Currency
c = arr(0).Val    // 1000.23 because Val uses point as decimal always
break
c = arr(1).Val    // 1 because Val stops at any non-digit as comma
break

It would be helpful to see how it’s declared and how you are adding values.

So you know that Variants are Special, Magic and a royal pain in the ■■■■ all at the same time.

But I notice that you are not pulling currency values correctly:

var a As Currency = myArray(i,1).CDbl it won’t compile.

That doesn’t compile because Variant doesn’t have a CDbl method. It does however have a CurrencyValue method which “treats the value as a Currency”

My array is a property of the window and is declared with Name = myArray(-1,4) with type = Variant
It is populated from a database that as the sum of many values entered by the user. Apparently Xojo enters a thousands separator when adding up currency values as all the entries in my test case are < 1000. The data is stored in the array which is used for creating a report. I tried using

Format(CType(myArray(i,1), Currency), app.kdollarFormat)

but it returned 1.00 also. I think I will stick with the twp step process at the bottom of my first post. Not elegant, but it works.

Yeah, ctype doesn’t help you here because it’s already a Currency. The problem is that you’re just not being specific enough on the single line and the Variant is just guessing wrong.

The way I usually handle this is to use a class instead. In this case, a class that has a String property, a currency property and three integer properties. Then you store class instances in the array. This way you don’t have to do all that type casting in and out of a variant array.

It also makes your code a lot more readable.

Can you write a simple sample demonstrating this issue as I could not reproduce?

Test sample:

// Report:
// A currency value stored in an array of variants works fine
// as long as the value is less than 1000.00.
// Anything larger than that returns 1 when I access it 

Var c1, c2 As Currency
c1 = 1234.56 // testvalue

Var myArray(0,1) As Variant

myArray(0,1) = c1 // Store currency

c2 = myArray(0,1) // Get the currency again

MessageBox c2.ToString(New Locale("en-US"))

Greg’s method using a class looks like a better way to handle this, so I am converting to it.

Turns out the original problem was caused by treating the second item in my array as currency when I had formatted it for printing when the array was populated. I was then trying to format a string that was already formatted and did didn’t work.

2 Likes