Help in declaring Variables 1

[uipuih]

I am trying get the sum of values in a column and then insert that value into a cell.
I tried the following but cannot get the MyValue to get the sum of the Range.

Dim MyRange As Range
Dim MyValue As Integer
Set MyRange = Range("E:E")
Set MyValue = Sum(MyRange)

'
End Sub

 

[SkipVought]

hi,

SUM is a spreadsheet function, not VBA...

Dim MyRange As Range
Dim MyValue As Long

'you set objects only
 Set MyRange = Range("E:E")

'other variable types just get assigned
 MyValue = Application.Sum(MyRange)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[uipuih]

Thank you Skip

 

[SkipVought]

BTW, the Integer data type holds from -32,768 to 32,767 and uses 2 bytes of memory. The Long data type holds from -2,147,483,648 to 2,147,483,647 and uses 4 bytes of memory.

So the use of any integer (small i) data type depends on the range of expected values.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[uipuih]

Thanks yet again.

Ian

 

[fumei]

Does the automatic conversion of Integer into Long happen in Excel as well as Word. I assume it does. In which case, it is better to simply drop using Integer, and use Long all the time.

Gerry

 

[PHV]

Does the automatic conversion of Integer into Long happen in Excel as well as Word
Well, I'd say it's a VBA feature, the Application doesn't matter.

 

[fumei]

Yeah, that is - as stated - what I assumed...but I have been caught with differences before. What do I know? I thought it was possible Excel (as it is so numbers based) may be enabled with some sort of an exception. After all, this is going on behind the scenses anyway.

Gerry

 

[xlbo]

I always use longs just in case...there's likely to be far bigger memory issues with the other objects I use anyway!!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DougAJ4]

Yes, stick to Longs with Excel VBA. Using Integers will just slow things down as they are converted to Longs anyway.

Doug Jenkins

http://newtonexcelbach.wordpress.com/