Solved: Simple Error Handling Help

[jtrowbridge]

I've got a chunk of code that counts the number of rows of continuous data in a spreadsheet which I later use as an upper bound in loops. The problem is that I'm defining my Counter varable as an integer which means that if there is no data on a tab the counter = 65536 which is above the upper bound for integers, aka error.

So, to make a long story short... Does anyone know how to handle errors like this:

--pseudo code--

Dim x as integer

If error then make x = 1 else leave x as it is

x = "String"

So end result is that x = 1


--real code (operating on completely blank worksheet)--

Sub problem()
Dim dealcount As Integer


Sheets("Sheet1").Select
Cells(1, 1).Select
Selection.End(xlDown).Select

dealcount = ActiveCell.Row 'error occurs here

End Sub


--want my code to do this--

Sub problem()
Dim dealcount As Integer


Sheets("Sheet1").Select
Cells(1, 1).Select
Selection.End(xlDown).Select

IF ERROR (dealcount = ActiveCell.Row) THEN
dealcount = 2
ELSE
dealcount = ActiveCell.Row
ENDIF


End Sub



I'm apparently not creative enough to utilize the On Error Resume/Goto functions for this.

thanks!

: pray2:

[rory]

Well, the obvious answer is to use a Long, not an Integer. :)
In general terms, you'd want:
On Error Goto Err_handler
dealcount = activecell.row

...

exit sub

err_handler:
dealcount = 2
resume next

End Sub

Obviously that's very rough - you should at least check what the error is before resetting the variable!

[malik641]

Well, the obvious answer is to use a Long, not an Integer. :)

jtrowbridge,

If you're curious to know why you can't use an integer, it's because they support numbers from -32,768 to 32,767. And the last row that you were trying to set the integer to was 65536.

It's too bad VBA doesn't utilize unsigned variables. Could have used an Unsigned Integer if using Excel 2003 or below (handling each row with a ?1, anyway).

[Reafidy]

As a rule of thumb always dimension rows as long. :)

[Bob Phillips]

As a rule of thumb, never use Integer

[Reafidy]

Thats a big call. :)


As a rule of thumb, never use Integer

[Bob Phillips]

But a correct one.

[Reafidy]

Id say "unless you have a damn good reason to use integer use long"

Because the VB engine converts integer to long anyway - and a ever so slight speed decrease is noted performing the conversion.

I just never say never! ;)

[mikerickson]

I heard a rumor that MicroSoft introduced a new "very long" data type to accomidate 2007's increased capabilities. Does anyone know anything more about that?

[malik641]

I heard a rumor that MicroSoft introduced a new "very long" data type to accomidate 2007's increased capabilities. Does anyone know anything more about that?Never heard of it. There's no need IMO. The Long data type is -2,147,483,648 to 2,147,483,647. And Excel 2007 has hardly 1M+ rows.

If you're really hurting for more space, there's always the big Decimal data type (+/-79,228,162,514,264,337,593,543,950,335 with no decimal point).


Anyway, why does VB 'convert' the data type Integer to long? And how, if you get an error if you exceed Integer's limits like jtrowbridge?

[Bob Phillips]

I heard a rumor that MicroSoft introduced a new "very long" data type to accomidate 2007's increased capabilities. Does anyone know anything more about that?

There is a new CountLarge property to cater for the number of cells on a worksheet, 16,000 x 1M+, which exceeds Long etc.

[Bob Phillips]

Never heard of it. There's no need IMO. The Long data type is -2,147,483,648 to 2,147,483,647. And Excel 2007 has hardly 1M+ rows.

If you're really hurting for more space, there's always the big Decimal data type (+/-79,228,162,514,264,337,593,543,950,335 with no decimal point).

It isn't a data type, it is a property as I mention to mikerickson.


Anyway, why does VB 'convert' the data type Integer to long? And how, if you get an error if you exceed Integer's limits like jtrowbridge?

Because we are not using 16-bit any longer, so it is inefficient within the core to cater for 16 and 32-bit, it is far more efficient to be standard, and convert the data before passing to the engine. This is standard API, deprecating handling, it is a nightmare trying to cater for every flavour, every variation out there.

[malik641]

It isn't a data type, it is a property as I mention to mikerickson.
Sorry, I don't see you mentioning that to mikerickson in this thread :dunno

I don't understand how it's a property, the help documentation considers it a data type (handled by Variant as a subtype).


Decimal Data Type

Decimal variables are stored as 96-bit (12-byte) signed integers scaled by a variable power of 10. The power of 10 scaling factor specifies the number of digits to the right of the decimal point, and ranges from 0 to 28. With a scale of 0 (no decimal places), the largest possible value is +/-79,228,162,514,264,337,593,543,950,335. With a 28 decimal places, the largest value is +/-7.9228162514264337593543950335 and the smallest, non-zero value is +/-0.0000000000000000000000000001.

Note At this time the Decimal data type can only be used within a Variant, that is, you cannot declare a variable to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function.


Because we are not using 16-bit any longer, so it is inefficient within the core to cater for 16 and 32-bit, it is far more efficient to be standard, and convert the data before passing to the engine. This is standard API, deprecating handling, it is a nightmare trying to cater for every flavour, every variation out there. So what about the Boolean data type? And the Byte data type? Are they also converted because we're using a 32-bit system? Or should we just use a Long for those because the system would handle it better?

That would be inefficient to me to have 4 bytes of storage for a Boolean or Byte type. I know that most VBA'ers don't care too much about storage size and how much memory is allocated, but I thought that those data types are there for those purposes.

I'm not saying this to argue with you, I know you know a lot. I'm just curious to know these things, too.

[Bob Phillips]

Sorry, I don't see you mentioning that to mikerickson in this thread :dunno

I don't understand how it's a property, the help documentation considers it a data type (handled by Variant as a subtype).

It was after your post Joseph, I was just referring back to it.

mikerickson said that he had heard of a new very long data type. I said that they had introduced a new poroperty that could return a cells count as Count overflows in 2007.


So what about the Boolean data type? And the Byte data type? Are they also converted because we're using a 32-bit system? Or should we just use a Long for those because the system would handle it better?

That would be inefficient to me to have 4 bytes of storage for a Boolean or Byte type. I know that most VBA'ers don't care too much about storage size and how much memory is allocated, but I thought that those data types are there for those purposes

Hey, no-one ever accussed MS of being consistent.

I haven't looked into it, but I very much doubt that Boolean is any different in 16-bit to 32-bit. TRUE is all bits set, FALSE is no bits set, so it would be -1 or 0 regardless.

But more importantly, you cannot use a Long where a Boolean is expected, it will fail at runtime if you try, you get a ByRef argument trype mismatch. You can change your code to use LOngs instead of Booleans, but I would venture that is probably more inefficient, because TRUE/FALSE is easily tested.

That actually raises a good point about Integere. I said never use them earlier, which of course is wrong because if an API uses an integer argument, you have to pass it as an integer as well.,

[malik641]

It was after your post Joseph, I was just referring back to it.

mikerickson said that he had heard of a new very long data type. I said that they had introduced a new poroperty that could return a cells count as Count overflows in 2007. I see. I thought you meant that "Decimal Data Type" was not a type, but a property.


Hey, no-one ever accussed MS of being consistent. You ain't kiddin'.


I haven't looked into it, but I very much doubt that Boolean is any different in 16-bit to 32-bit. TRUE is all bits set, FALSE is no bits set, so it would be -1 or 0 regardless. Right. But what I wonder is since a Boolean variables stores 2 Bytes to memory, that's how many bytes should be allocated to memory, right? Not 8 bytes just because we work on a 32-bit system. So what I think should be stored is simply "0000 0000" for False and "1111 1111" for true. Not "0000 0000 0000 0000 0000 0000 0000 0000" for False and "1111 1111 1111 1111 1111 1111 1111 1111" for True...at least, that's what I think. I need to research this further.


That actually raises a good point about Integere. I said never use them earlier, which of course is wrong because if an API uses an integer argument, you have to pass it as an integer as well., Well, as Reafidy said, never say never! :)

[jtrowbridge]

Thanks for the info guys!

:beerchug: