HI, i've made a small function that counts the seconds i hours and
minutes

if i try to convert hours larger then 9 i get an error that says my
variable isn't
declared right. but i can't see anything wrong

Help wanted


Function sec(Invarde As String) As Long
Dim mystring As String
Dim antal As Integer
Dim hour As Integer
Dim minute As Integer
mystring = Invarde
antal = Len(mystring)
hour = Left(mystring, antal - 3)
minute = Right(mystring, 2)
SEC = (HOUR * 3600) + (MINUTE * 60)
End Function


--
jocke
------------------------------------------------------------------------
jocke's Profile: http://www.excelforum.com/member.php...nfo&userid=766
View this thread: http://www.excelforum.com/showthread...hreadid=473737

do you know which variable is "declared wrong"?
(during debugging, you should be able to put the cursor on the different
variables and see what the variable value is.)
Is the len of the input string what you think it should be?

"jocke" wrote:


HI, i've made a small function that counts the seconds i hours and
minutes

if i try to convert hours larger then 9 i get an error that says my
variable isn't
declared right. but i can't see anything wrong

Help wanted


Function sec(Invarde As String) As Long
Dim mystring As String
Dim antal As Integer
Dim hour As Integer
Dim minute As Integer
mystring = Invarde
antal = Len(mystring)
hour = Left(mystring, antal - 3)
minute = Right(mystring, 2)
SEC = (HOUR * 3600) + (MINUTE * 60)
End Function


--
jocke
------------------------------------------------------------------------
jocke's Profile: http://www.excelforum.com/member.php...nfo&userid=766
View this thread: http://www.excelforum.com/showthread...hreadid=473737

The integer data type can contain positive integers up to 32767.

Since both HOUR and MINUTE are declared as Integer, and both 3600 and 60
are less than 32767, the internal multiplication will use the more
restrictive type (Integer). So if HOUR9, the multiplication creates an
overflow even though the result would eventually be cast as a Long
(since SEC is declared as long).

You don't really gain anything by typing as Integer - the storage
required is the same as for Longs, and, IIRC, math operations are no
less efficient, so declare HOUR and MINUTE as Longs.

Alternatively, you can coerce the internal result to a Long by appending
the Long type-declaration character (&) to your constant:

SEC = (HOUR * 3600&) + (MINUTE * 60)

In article ,
jocke wrote:

HI, i've made a small function that counts the seconds i hours and
minutes

if i try to convert hours larger then 9 i get an error that says my
variable isn't
declared right. but i can't see anything wrong

Help wanted


Function sec(Invarde As String) As Long
Dim mystring As String
Dim antal As Integer
Dim hour As Integer
Dim minute As Integer
mystring = Invarde
antal = Len(mystring)
hour = Left(mystring, antal - 3)
minute = Right(mystring, 2)
SEC = (HOUR * 3600) + (MINUTE * 60)
End Function

its the " sec" variable thats making all the trouble.


--
jocke
------------------------------------------------------------------------
jocke's Profile: http://www.excelforum.com/member.php...nfo&userid=766
View this thread: http://www.excelforum.com/showthread...hreadid=473737

thank's now it's working, but i don't get it.
if the variabel hour is declared integer, must the result of any
multiplying with "hour" be less than 32767 or else it will fail?

thanks a lot

/jocke


--
jocke
------------------------------------------------------------------------
jocke's Profile: http://www.excelforum.com/member.php...nfo&userid=766
View this thread: http://www.excelforum.com/showthread...hreadid=473737

your post shows sec as capitals "SEC"
try converting to lower case "sec" in your macro
If you have option explict active this could be your problem.

"jocke" wrote:


its the " sec" variable thats making all the trouble.


--
jocke
------------------------------------------------------------------------
jocke's Profile: http://www.excelforum.com/member.php...nfo&userid=766
View this thread: http://www.excelforum.com/showthread...hreadid=473737

VBA's calculation engine uses the most restrictive typing in its
calculations, so if you have multiply two integers, the result is an
integer. If the result is too large, you'll get an overflow error.

This applies to intermediate calculations as well. So the result of

(HOUR * 3600)

since HOUR is an Integer, and 3600 by default is an integer, will be an
integer. If HOUR 9, an overflow will occur.

If you use the Long type-declaration character (&):

(HOUR * 3600&)

VBA will reserve an internal Long variable for the result (since one of
the arguments is a Long), and your calculation will carry through.



In article ,
jocke wrote:

if the variabel hour is declared integer, must the result of any
multiplying with "hour" be less than 32767 or else it will fail?

In article ,
jocke wrote:

its the " sec" variable thats making all the trouble.

No, "sec" has nothing to do with it.

Try this in the Immediate Window:

? 10 * 3600

You'll get an overflow error. Next try

? 10& * 3600

See my other post for an explanation.