ExcelBanter - Data Types and inaccuracies with Trig functions in VBA

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Data Types and inaccuracies with Trig functions in VBA (https://www.excelbanter.com/excel-programming/291260-data-types-inaccuracies-trig-functions-vba.html)

Data Types and inaccuracies with Trig functions in VBA

I am developing an application to calculate navigation data from Sun & Star
sights (interesting for sailors, boring for everyone else).

The problem is that I appear to be getting small inaccuracies when
calculating angles. I have used Double as the data type. Looking through
the help files I note that there should be a 'Decimal' data type that uses
many more digits so potentially improving accuracy. However, a line such
as:

Dim dLat as Decimal

results in a message: 'Compile error: Expected: New or type name'

I understand that this is saying it doesn't recognise the Decimal data type.
Do I have to add a library to use this? if so which one?

Any ideas gratefully received.

Peter Morris

Data Types and inaccuracies with Trig functions in VBA

Peter,

From a Myrna Larson post:
'-----------------------------------
The decimal data type is a subtype of a variant. You must Dim the variable as a
variant, then put the data into it with the CDec function. Since Decimals can
have up to 28 significant figures, and doubles are limited to 15 or 16, how do
you assign a literal value to one? From a string representation of the number,
i.e.
Dim Dec As Variant
Dec = CDec("1234567890123456789012345678")
Debug.Print Dec, TypeName(Dec)
'------------------------------------
From a Chip Pearson post:
To display these values on a worksheet, you'll have to change them to
strings to prevent Excel from rounding them ...
Range("C2").Value = " ' " & CStr(Dec)
'-----------------------------------

Regards,
Jim Cone
San Francisco, CA

"Peter M" wrote in message
...
I am developing an application to calculate navigation data from Sun & Star
sights (interesting for sailors, boring for everyone else).
The problem is that I appear to be getting small inaccuracies when
calculating angles. I have used Double as the data type. Looking through
the help files I note that there should be a 'Decimal' data type that uses
many more digits so potentially improving accuracy. However, a line such
as:
Dim dLat as Decimal
results in a message: 'Compile error: Expected: New or type name'
I understand that this is saying it doesn't recognise the Decimal data type.
Do I have to add a library to use this? if so which one?
Any ideas gratefully received.
Peter Morris

Data Types and inaccuracies with Trig functions in VBA

Jim,

Does this mean that I could get greater accuracy simply by declaring
variables as Variant rather than Double?

ie use: Dim x, y, z
rather than Dim x as double, y as double, z as double.

Peter Morris

"Jim Cone" wrote in message
...
Peter,

From a Myrna Larson post:
'-----------------------------------
The decimal data type is a subtype of a variant. You must Dim the variable
as a
variant, then put the data into it with the CDec function. Since Decimals
can
have up to 28 significant figures, and doubles are limited to 15 or 16,
how do
you assign a literal value to one? From a string representation of the
number,
i.e.
Dim Dec As Variant
Dec = CDec("1234567890123456789012345678")
Debug.Print Dec, TypeName(Dec)
'------------------------------------
From a Chip Pearson post:
To display these values on a worksheet, you'll have to change them to
strings to prevent Excel from rounding them ...
Range("C2").Value = " ' " & CStr(Dec)
'-----------------------------------

Regards,
Jim Cone
San Francisco, CA

"Peter M" wrote in message
...
I am developing an application to calculate navigation data from Sun &
Star
sights (interesting for sailors, boring for everyone else).
The problem is that I appear to be getting small inaccuracies when
calculating angles. I have used Double as the data type. Looking
through
the help files I note that there should be a 'Decimal' data type that
uses
many more digits so potentially improving accuracy. However, a line
such
as:
Dim dLat as Decimal
results in a message: 'Compile error: Expected: New or type name'
I understand that this is saying it doesn't recognise the Decimal data
type.
Do I have to add a library to use this? if so which one?
Any ideas gratefully received.
Peter Morris

Data Types and inaccuracies with Trig functions in VBA

No. And you really can't get more accuracy unless you write your own
functions. If you passed a Variant of type Decimal to a built in function,
it would be converted to double or whatever the function normally works in.

I know someone has written an addin that does basic math with numbers of
creater precision than double, but I don't recall the name of it or the
author. You could try a google search on Excel, Addin, Precision or
something like that. Anyway, as I recall, this person was adding standard
functions to the addin to do more complex math at higher pecision. Note
that Excel works at the IEEE standard for double precision which is about 15
digits.

Also, Chip Pearson has a page on working with Latitudes and Longitudes-
maybe this has some bearing on your problem.
http://www.cpearson.com/excel/latlong.htm

Anyway, just declaring a variable as Variant will not make it a decimal
value if you assign it a number. It will retain whatever precision it had.
You would have to assign it to the variant with the cdec conversion function

Dim vVariant as Variant
vVariant = cdec(123.456567)

--
Regards,
Tom Ogilvy

"Peter M" wrote in message
...
Jim,

Does this mean that I could get greater accuracy simply by declaring
variables as Variant rather than Double?

ie use: Dim x, y, z
rather than Dim x as double, y as double, z as double.

Peter Morris

"Jim Cone" wrote in message
...
Peter,

From a Myrna Larson post:
'-----------------------------------
The decimal data type is a subtype of a variant. You must Dim the
variable
as a
variant, then put the data into it with the CDec function. Since
Decimals
can
have up to 28 significant figures, and doubles are limited to 15 or 16,
how do
you assign a literal value to one? From a string representation of the
number,
i.e.
Dim Dec As Variant
Dec = CDec("1234567890123456789012345678")
Debug.Print Dec, TypeName(Dec)
'------------------------------------
From a Chip Pearson post:
To display these values on a worksheet, you'll have to change them to
strings to prevent Excel from rounding them ...
Range("C2").Value = " ' " & CStr(Dec)
'-----------------------------------

Regards,
Jim Cone
San Francisco, CA

"Peter M" wrote in message
...
I am developing an application to calculate navigation data from Sun &
Star
sights (interesting for sailors, boring for everyone else).
The problem is that I appear to be getting small inaccuracies when
calculating angles. I have used Double as the data type. Looking
through
the help files I note that there should be a 'Decimal' data type that
uses
many more digits so potentially improving accuracy. However, a line
such
as:
Dim dLat as Decimal
results in a message: 'Compile error: Expected: New or type name'
I understand that this is saying it doesn't recognise the Decimal data
type.
Do I have to add a library to use this? if so which one?
Any ideas gratefully received.
Peter Morris

Data Types and inaccuracies with Trig functions in VBA