How to Code 2

Jun 10, 2013 at 9:16 AM

Sample Cording below is made by Excel VBA

Function Test() As Double
Dim temp_Ans as Double
temp_Ans = Cells(1,1).value
Return temp_Ans

End Function

I want to know how to get "Cells(1,1).value".
Jun 11, 2013 at 1:56 AM
Abount this question,
I want to code in
Jun 11, 2013 at 11:11 AM
Edited Jun 11, 2013 at 11:11 AM

The code you have posted is not a working VBA function, and it's not clear what you want to do.

Maybe you can make a working VBA version, then I can suggest how to do it in VB.NET, or you can give details of what you are trying to do.

Jun 11, 2013 at 11:56 AM
Hi, Govert.
I'm sorry my sample is not good.
And I'm from Japan, so I'm not good at English well.

The thing I want to know is to get non-selected cell's data.
When one cell's data changes,I want to get another cell's data. (for example cells(1,1) data)

I want your help,Govert.
Jun 11, 2013 at 12:05 PM
Edited Jun 11, 2013 at 12:06 PM

You can use the Excel C API to do this, using the ExcelReference type. To be able to read values from elsewhere in the sheet, your function must be marked with an <ExcelFunction(IsMacroType:=True)> attribute.

So suppose you want to read cell B3 from within your function - it has row index 2 and column index 1 (the ExcelReference class uses 0-based indices). So you could try something like this:
<ExcelFunction(IsMacroType:=True)> _
Public Shared Function GetB3() As Object
    Dim cellRef As ExcelReference
    Dim content As Object

    cellRef = New ExcelReference(2, 1)
    content = cellRef.GetValue()

    Return content    
End Function
Another approach would use the Excel COM automation interface, but that's not a good idea from inside a worksheet function.

Jun 11, 2013 at 12:12 PM

Thank you for your quick help.
Your sample is so good.
I'll try.

Jun 12, 2013 at 6:18 AM

Everything is good.

Thanks a lot.