CODEWITHSUNDEEP
excelvba
HotFuzz
HotFuzz

Reputation: 39

How do you access a specific value for a Range object in VBA?

Alright,

So here is a snippet of my code I use for statistics analysis. I need my variable "Freq" to be defined as a range because I use different excel formulas to speed up the code such as AVERAGE(), STEYX(), DEVSQ(), FORECAST() etc. I'm using VBA to do so because I need to iterate quickly throught a lot of data and feel limited only using Excel formulas.

dim Freq as Range
dim sht as Worksheet
dim n as integer

n = sht.Cells(sht.Rows.Count, "D").End(xlUp).Row - 1
Set sht.ThisWorkbook.ActiveSheet
Set Freq = sht.Range("D" & 2 & ":D" & n + 1)

Later in my code, i want to verify if any value in the "Freq" variable is above or below a certain value. I know how to do this by checking each cell in the range, or assign an array for the range, but I feel like having a second variable for the same range of data is reductant and inefficient.

When I look at the "Freq" object in VBA, I see a Value2(x,1), x being any number between 1 and n, but I have not found a way to access this value yet. I tried:

tmp=Freq.Value(1)
tmp=Freq.Value(1,1)
tmp=Freq.Value2(1)
tmp=Freq.Value2(1,1)

but none of this works.

So, basically my question is: How do you access a specific value for a Range object in VBA?

Any help is greatly appreciated!

Upvotes: 0

Views: 1932

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Range.Value takes one optional argument, so Freq.Value(1,1) is just not correct syntax.

To omit optional argument, you do `Freq.Value()(1,1) per Rory.

Alternatively, cast your Freq.Value to a variant array:

Dim myArray as Variant
myArray = Freq.Value
MsgBox myArray(1,1)

The ExcelRangeValueType is given as below, and when omitted will use the default parameter value:

xlRangeValueDefault         10  Default. If the specified Range object is 
                                empty, returns the value Empty (use the 
                                IsEmpty function to test for this case). If 
                                the Range object contains more than one cell, 
                                returns an array of values (use the IsArray 
                                function to test for this case).
xlRangeValueMSPersistXML    12  Returns the recordset representation of the 
                                specified Range object in an XML format.
xlRangeValueXMLSpreadsheet  11  Returns the values, formatting, formulas, 
                                and names of the specified Range object in 
                                the XML Spreadsheet format.

Related: https://fastexcel.wordpress.com/2017/03/13/excel-range-valuevaluetype-what-is-this-parameter/

Upvotes: 1

Related Questions