I'm trying to read a variable from an excel sheet in the format
hh:mm:ss.000 and subtract it with a value in the same format.
This works fine if i do it in the worksheet,


A1 = 09:03:00.296
A2 = 09:03:00.203

A2 - A1 = 00:00:00.093


however if i do the same in VBA im getting a "type mismatch" error 13

Dim A1
Dim A2

A1 = Worksheets("Sheet 1").Range("A1")
A2 = Worksheets("Sheet 1").Range("A2")

Worksheets("Sheet 1").Range("A3") = A1 - A2

I appreciate any help on this.

Thomas Meixner

-----Original Message-----
I'm trying to read a variable from an excel sheet in the
format
hh:mm:ss.000 and subtract it with a value in the same
format.
This works fine if i do it in the worksheet,


A1 = 09:03:00.296
A2 = 09:03:00.203

A2 - A1 = 00:00:00.093


however if i do the same in VBA im getting a "type
mismatch" error 13

Dim A1
Dim A2

A1 = Worksheets("Sheet 1").Range("A1")
A2 = Worksheets("Sheet 1").Range("A2")

Worksheets("Sheet 1").Range("A3") = A1 - A2

I appreciate any help on this.

Thomas Meixner
.

You need to explicitly tell VBA what type of variable you
are working with - in this case, Date types - or else your
formula is ambiguous. So replace your Dim statements with:

Dim A1 as Date, A2 as Date

I think this should resolve the problem

"K Dales" wrote in message ...

.

You need to explicitly tell VBA what type of variable you
are working with - in this case, Date types - or else your
formula is ambiguous. So replace your Dim statements with:

Dim A1 as Date, A2 as Date

I think this should resolve the problem


Thx for the reply

As soon as i specify the wariable as Date, the program terminates as
soon as i try to read the cell. Variable type "Date" seems not
understand the format hh:mm:ss.000.

Is there maybe way to read the value into a decimal format, calculate
it and format it back to hh:mm:ss.000 ?

-----Original Message-----
"K Dales" wrote in
message ...

.

You need to explicitly tell VBA what type of variable
you
are working with - in this case, Date types - or else
your
formula is ambiguous. So replace your Dim statements
with:

Dim A1 as Date, A2 as Date

I think this should resolve the problem


Thx for the reply

As soon as i specify the wariable as Date, the program
terminates as
soon as i try to read the cell. Variable type "Date"
seems not
understand the format hh:mm:ss.000.

Is there maybe way to read the value into a decimal
format, calculate
it and format it back to hh:mm:ss.000 ?
.


It is the decimal points after the seconds. Excel cannot
understand them as a date format (and cannot handle it -
there would be an overflow in the date variable if it
tried to handle subdivisions of seconds that small). This
will make it tricky. I think you need to parse the format
yourself, as in the example below:

Dim A1 as Variant, A2 as Variant, ADiff as Variant
Dim T1 as Date, T2 as Date, TDiff as Variant
Dim S1 as Single, S2 as Single, SDiff as Variant

A1 = Worksheets("Sheet 1").Range("A1")
T1=TimeValue(Left(A1,6) & "00")
S1 = CSng(Right(A1,6))

A2 = Worksheets("Sheet 1").Range("A2")
T2=TimeValue(Left(A2,6) & "00")
S2 = CSng(Right(A2,6))

Now you can subtract the seconds part (S2 from S1) and the
hours/minutes part (T2 from T1) then reassemble the result
into a string - but you would also need to have
a "carrying" procedure in the case S2S1. More coding
than I can afford the time to do at the moment, but I hope
it gives you a clue.

Good luck!