[Solved] Inadmissable value or data type overflow

[Robitron]

Okay, I've written a macro for calc that searches a list of items, finds the total number of each item then tallies the total value of each item based on quantity.

The macro works great until I come to one item that is valued at 10001. I have 8 of those items which equals 80008, however when the macro gets to that item, i always get the Inadmissable Value or Data Type Overflow.
All items up to that point calculate correctly and are of a lower total value so why doesn't 80008 work? Here is the code:

Code: Select all

	Dim Doc as Object
	Dim Cemetery as Object
	Dim Explorers as Object
	Dim WyattMarx as Object
	Dim Cell1 as Object
	Dim Cell2 as Object
	Dim Cell3 as Object
	Dim Cell4 as Object
	Dim Cell5 as Object
	Dim Array1()
	Dim Array2()
	Dim Array3()
	Dim Temp
	Dim Tick as Integer
	Dim Count as Integer
	Dim Count2 as Integer
	Dim Value as Integer
	Dim Quantity as Integer
	Dim Total as Integer
	Dim Columns as Integer
	Dim Rows as Integer
	Dim TotalColumns as Integer
	Dim TotalRows as Integer
	Dim TotalItems as Integer
	Dim Total as Integer
	Dim Sim as String
	Dim Item as String

'***************************************************
Sub Main
	Doc = ThisComponent
	Cemetery = Doc.Sheets.GetByName("Cemetery")
	Explorers = Doc.Sheets.GetByName("Explorers")
	WyattMarx = Doc.Sheets.GetByName("Marx, Wyatt")
	TotalRows = 0 : Count2 = 0 : Quantity = 0 : Temp = 3
CountItems:
	Cell1 = Cemetery.GetCellByPosition(0 , TotalRows + 3)
	If Cell1.String <> "" Then
		TotalRows = TotalRows + 1
		Goto CountItems
	End If
	TotalRows = TotalRows + 2
	Cell1 = Explorers.GetCellByPosition(0 , TotalItems + 5)
	If Cell1.String<> "" Then
		TotalItems = TotalItems + 1
		Goto CountItems
	End If
	ReDim Array1(TotalItems) : Count2 = 0
	For Count = 5 to TotalItems + 4
		Quantity = 0 : Temp = 0 : Total = 0
		Cell1 = Explorers.GetCellByPosition(0 , Count)
		For Rows = 2 to TotalRows + 2
			Cell2 = Cemetery.GetCellByPosition(1 , Rows)
			If Cell2.String = Cell1.String Then
				Temp = Temp + 1
				Cell4 = Cemetery.GetCellByPosition(2 , Rows)
				Total = Total + Cell4.Value
			End If
			If Cell2.String = Cell1.String Then
				Quantity = Quantity + 1
'				Cell2.String = ""
			End If
		Next Rows
		Cell3 = WyattMarx.GetCellByPosition(0 , Count - 3)
		Doc.CurrentController.Select(Cell3)
		Cell3.String = Cell1.String
		Cell3 = WyattMarx.GetCellByPosition(1 , Count - 3)
		Cell3.Value = Temp
		Cell3 = WyattMarx.GetCellByPosition(2 , Count - 3)
		Cell3.Value = Total
		Temp = 0
	Next Count
End Sub

NOTE: There is a separate macro to do some initial sorting so I'm not worried about that aspect. Also, please forgive my macro methodology as I am self-taught in basic and have no formal understanding of certain terms or procedures. Thank you.


Here is the list of items that it sorts and tallies:

[Table=]Collector Item Value
Paul Marx Mysterious Mr. Gnome 1000
Cheryl Marx Emerald 22
Cheryl Marx Murder in Pleasant Ville 65
Paul Marx Mysterious Mr. Gnome 1000
Cheryl Marx Unknown Special Seed 10001
Marie Marx Tiny Space Rock 26
Cheryl Marx Unknown Special Seed 10001
Wyatt Marx Yellow Sapphire 41
Marie Marx Unknown Special Seed 10001
Paul Marx The Crumplebottom Legacy: A Tale 480
Cheryl Marx The Crumplebottom Legacy: A Tale 480
Paul Marx Unknown Rare Seed 22
Wyatt Marx Mysterious Mr. Gnome 1000
Wyatt Marx Diamond 153
Cheryl Marx Unknown Uncommon Seed 5
Cheryl Marx Ruby 31
Paul Marx Ruby 30
Wyatt Marx Tiny Space Rock 28
Wyatt Marx Lettuce Seed 2
Marie Marx Silver 31
Cheryl Marx Unknown Rare Seed 48
Paul Marx Unknown Special Seed 10001
Paul Marx Ruby 29
Cheryl Marx Grape Seed 16
Marie Marx Unknown Uncommon Seed 22
Marie Marx Palladium 470
Wyatt Marx Plutonium 1399
Wyatt Marx Llama Rights 610
Cheryl Marx Unknown Rare Seed 8
Cheryl Marx Unknown Special Seed 10001
Paul Marx Llama Rights 610
Wyatt Marx Game of Thorns 405
Jeremiah Marx Grape Seed 2
Cheryl Marx Unknown Special Seed 10001
Marie Marx Yellow Sapphire 46
Jeremiah Marx Unknown Special Seed 10001
Wyatt Marx Grape Seed 8
Paul Marx Palladium 381
Cheryl Marx The Crumplebottom Legacy: A Tale 480
Wyatt Marx Mysterious Mr. Gnome 1000
Wyatt Marx Unknown Uncommon Seed 17
Paul Marx Tomato Seed 12
Cheryl Marx Gigantic Space Rock 12384
Cheryl Marx The Crumplebottom Legacy: A Tale 480
Paul Marx Unknown Uncommon Seed 7
Cheryl Marx Tanzanite 74
Wyatt Marx Mysterious Mr. Gnome 1000
Paul Marx Diamond 161
Cheryl Marx Llama Rights 610
Wyatt Marx Gold 82
Cheryl Marx Game of Thorns 405
Wyatt Marx Unknown Rare Seed 10
Cheryl Marx Ruby 32
Paul Marx Yellow Sapphire 48
Paul Marx Luminorious Gem 280
Wyatt Marx Blue Topaz 13
Marie Marx Tomato Seed 7
Cheryl Marx Rainbow Gem 546
Cheryl Marx Unknown Rare Seed 30
Paul Marx Mysterious Mr. Gnome 1000
Wyatt Marx Tiny Space Rock 20
Marie Marx Lettuce Seed 2
Wyatt Marx Mysterious Mr. Gnome 1000
Paul Marx Tanzanite 77
Cheryl Marx Game of Thorns 405
Paul Marx Mysterious Mr. Gnome 1000
Wyatt Marx Game of Thorns 405
Cheryl Marx Llama Rights 610
Paul Marx Unknown Uncommon Seed 6
Cheryl Marx The Crumplebottom Legacy: A Tale 480
Wyatt Marx Rainbow Gem 558
Jeremiah Marx Gold 70
Jeremiah Marx Unknown Rare Seed 12
Cheryl Marx Gigantic Space Rock 2190
Paul Marx Grape Seed 3
Marie Marx Unknown Rare Seed 16
Paul Marx Emerald 25
Cheryl Marx Pink Diamond 6935
Wyatt Marx Unknown Rare Seed 22
Wyatt Marx Mysterious Mr. Gnome 1000
Paul Marx Unknown Rare Seed 13
Paul Marx Unknown Rare Seed 16
Cheryl Marx Unknown Special Seed 10001
Wyatt Marx Apple Seed 10
Jeremiah Marx Tiny Space Rock 28
Jeremiah Marx Unknown Uncommon Seed 7
Cheryl Marx Unknown Uncommon Seed 4
Marie Marx Mysterious Mr. Gnome 1000
Cheryl Marx Lettuce Seed 6
Wyatt Marx Llama Rights 610
Cheryl Marx Unknown Rare Seed 16
Wyatt Marx Iron 17
Wyatt Marx Llama Rights 610
Wyatt Marx Emerald 24
Marie Marx The Crumplebottom Legacy: A Tale 480
Marie Marx Diamond 128
Cheryl Marx Mysterious Mr. Gnome 1000
Cheryl Marx Unknown Uncommon Seed 13
Wyatt Marx The Crumplebottom Legacy: A Tale 480
Paul Marx Tanzanite 82
Paul Marx Tanzanite 79
Wyatt Marx Tanzanite 72
Wyatt Marx Silver 30
[/Table]

[B Marcelly]

Hi,

I have 8 of those items which equals 80008, however when the macro gets to that item, i always get the Inadmissable Value or Data Type Overflow.

Don't use variables of type Integer. Use instead type Long.

Integer Variables
Integer variables range from -32768 to 32767.
Long Integer Variables
Long integer variables range from -2147483648 to 2147483647.

Code: Select all

Dim Tick as Long, Count as Long,  Count2 as Long
Dim Value as Long, Quantity as Long
'  etc...

[Robitron]

Instead of using type long, I just changed it to Dim Total. That worked and that way the macro decides what type to make it. Thanks for the help!

[B Marcelly]

That's not a good solution.
You are supposing Basic is smarter than you.
I hope for you that it's not the case.

[Robitron]

Well, I was thinking about recycling. For instances, I have a generic Dim Array1() that is not labeled so that I can use it in one sub to track explorers while in another to track item values for example.

However, with Total, Value and so on, they would only ever be numeric so therefore could be set permanently as Long.