How to get COLUMNS() function to work in VBA with Evaluate()

[TomCon]

On my worksheet the following two formulas work and correctly return 5 as the result.
=COLUMN(data_rng[[#Headers],[Net]])
=COLUMN($E$1)

However, trying to use those formulas in Evaluate, they do not work.
Dim c As Variant
c = Evaluate("=COLUMN(data_rng[[#Headers],[Net]])")
c = Evaluate("=column($E$1)")

I have tried Dim c as integer as well.

With c as Variant, the line c=Evaluate... executes, does not give an error, but after the line, c does not have the value 5.
If i type ?c in the Immediate window, after the above line executers, it gives the "Runtime Error 13, Type mismatch".

With c Dim as Integer, the error occurs on the Evaluate line and it does not execute.

How do i get this to work, use the COLUMN() worksheet function within VBA to get the result?

Thanks!

 

[jasonb75]

Even though there is only a single value, the column function will return it as an array which vba doesn't like.
I know of 2 methods that will work, there may be others.
Using vba native functions instead of evaluating a formula.

Range("data_rng[[#Headers],[Net]]").Column

Using INDEX to nullify the array by extracting the first (only) item in the array.

Evaluate("INDEX(COLUMN(data_rng[[#Headers],[Net]]),1)")

edit:- 3 ways, this one came to mind after posting, this is simply the native vba equivalent to INDEX.

Evaluate("COLUMN(data_rng[[#Headers],[Net]])")(1)

 

[TomCon]

Even though there is only a single value, the column function will return it as an array which vba doesn't like.
I know of 2 methods that will work, there may be others.
Using vba native functions instead of evaluating a formula.

Range("data_rng[[#Headers],[Net]]").Column

Using INDEX to nullify the array by extracting the first (only) item in the array.

Evaluate("INDEX(COLUMN(data_rng[[#Headers],[Net]]),1)")

edit:- 3 ways, this one came to mind after posting, this is simply the native vba equivalent to INDEX.

Evaluate("COLUMN(data_rng[[#Headers],[Net]])")(1)

Heh, totally appreciate it. These "obscure things"...hard for me to discover, so appreciate this "intricate insight"!
Thanks!