New to VBA, struggling to write some simple codes

[AlexRM]

Hi all,
Trying to learn some VBA. One code I'm trying to write is a table lookup function. The table has row and heading titles. I want arguments of the function to be a row title, column title, and table range, and the returned value to be the contents of the cell corresponding to that row and column.

Here's what I've tried. It results in a #VALUE error:

Function Getdata(row_name As String, col_name As String, table_array As range) As Double


Dim row_index As Integer
Dim col_index As Integer


row_index = WorksheetFunction.Match(row_name, range(table_array).Rows(1), 0)
col_index = WorksheetFunction.Match(col_name, range(table_array).Columns(1), 0)


Getdata = WorksheetFunction.Index(table_array, row_index, col_index)


End Function

This is just using the in-built XL functions MATCH and INDEX, and incorporating them into a single function, GETDATA. When setting row_index and col_index, I try to lookup just the first row or column of the table using range(table_array).Rows(1), and when a I add a breakpoint on this line, mousing over this part displays the message "<Method 'Range' of object '_Global' failed>". I am very confused with ranges and how they work... they seem to be both an object and a property, and according to this message it's trying to be a method as well. Can anyone help me out?

[YasserKhalil]

Try this

Function Getdata(row_name As String, col_name As String, table_array As Range) As Variant
    Dim row_index As Integer
    Dim col_index As Integer
    
    row_index = WorksheetFunction.Match(row_name, table_array.Columns(1), 0)
    col_index = WorksheetFunction.Match(col_name, table_array.Rows(1), 0)
    
    Getdata = WorksheetFunction.Index(table_array, row_index, col_index)
End Function

You have used when referring to table_array and that is not correct, you have to use table_array directly
Another point you should refer to Column(1) when searching for rows and vice vers

[Bob Phillips]

You should add some error checking in there, in case the names don't get matched.

Here is another way.

Function Getdata(row_name As String, col_name As String, table_array As Range) As Variant
Dim rowRng As Range, colRng As Range
Dim rowIndex As Long, colIndex As Long

    On Error Resume Next
    rowIndex = Application.Match(row_name, table_array.Columns(1), 0)
    colIndex = Application.Match(col_name, table_array.Rows(1), 0)
    On Error GoTo 0
    If rowIndex = 0 Or colIndex = 0 Then
    
        Getdata = CVErr(xlErrRef)
    Else
    
        Set rowRng = table_array.Rows(rowIndex)
        Set colRng = table_array.Columns(colIndex)
        Getdata = table_array.Parent.Evaluate("=" & rowRng.Address & " " & colRng.Address)
    End If
End Function

[YasserKhalil]

wonderful Mr. xld
you are perfect as usual

[Bob Phillips]

wonderful Mr. xld
you are perfect as usual

I actually think the OP's approach, with your correction, is a better way, but I just added the alternative out of interest, and to show the OP how to handle invalid calls

[AlexRM]

Hey guys, thanks for your responses! The code is now working using both of your approaces
So I understand now how I misused the range object. There are a couple of things I am still not sure about:
- Why is "columns" used to search for rows? This seems very confusing.
- Why is the function defined as variant data type? I guess this would make the code more robust if the table might have string data, but would it still work as Double if the table only contains numbers?
- xld, why do you use "long" for row index and column index? Is the choice between long and integer significant here?

Cheers

[Blade Hunter]

"- xld, why do you use "long" for row index and column index? Is the choice between long and integer significant here?"

Bascially VBA doesn't use Integer but rather converts it to a long at run time..... but still places integer restrictions on it.

Syntactically Integer is correct, just not in VBA.

Here is a much better explanation than I could post

http://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long

[AlexRM]

Thanks Blade Hunter.

Another question has come up. The code works great when I input the range as eg. B2:D10. But I would like to be able to name a set of cells and refer to the range by that name. For example, I have a table of data called FluidsData, and I want to be able to simply type that rather than 'Fluids Data'!B2:D10, so something like Getdata("steam","density",FluidsData). It doesn't seem to work when I try this though. What changes would I need to make to my code to handle this?

EDIT: Nevermind, this actually works fine. I derped and typed the wrong table name (should have been solidsData rather than fluidsData).

[Bob Phillips]

"[COLOR=#333333]Bascially VBA doesn't use Integer but rather converts it to a long at run time..... but still places integer restrictions on it.

Syntactically Integer is correct, just not in VBA.

VBA does use integer, and it is correct syntax in VBA as it is a basic VB data type. It is just that since 32bit systems (and consequently 64bit) work in longs, so when the application passes an integer value to some o/s component it gets converted to long, the component does it stuff, then converts it back to integer to pass tit back. So using integer is not wrong, it is just less efficient (although it is highly unlikely it would be noticeable).