I have some complicated excel formulas (nested conditionals, etc.) that I'm hoping to optimize w/VBA. I use tables because the data sets are dynamic. My goal is to be able to replace some complicated functions with macros that pull in table data into an array, execute the worksheet functions, and spit the results back out into the corresponding cell. My specific problem is that I'm curious if there's a way within VBA to refer to a column within the same record/row on the same table, in the same way that Access' version of SQL and basic Excel functions permit.

In excel, one simple uses brackets and the @ sign, like this example of an excel formula in a column titled "Duplicate":

=IF(AND(COUNTIFS([NAME],[@[NAME]],[LOCATION],[@[LOCATION]])>1,NOT([@[IGNORE_RECORD]]="NO")),"DUPLICATE","OK")

Here, the table knows would know that for, say, a row representing a person named John Smith, it needs to look at the cell for "Ignore_Record," "Name," and "Location," in the same row as John Smith's name and populate the result in the column "Duplicate."

My questions for you all are

(1) whether there are any resources online or general tips about working with VBA arrays, listobjects, etc. to accomplish this same thing--ie, for relative references that allows the use of named ranges (I've found some solutions online using offsets, but these seem cumbersome and not scalable for future use, although I'd love any feedback if this impression is misguided)

(2) if you think this is better achieved as function, rather than a macro incorporating worksheet functions?

(Very green at VBA but decent at SQL and very proficient in non-VBA excel). Thank you!