I recieved this email from my pal Simon:

“One of the problems in many of the spreadsheets and potential spreadsheets I come across is the need to automatically adjust to changing areas of external data. It seems at first glance that, to refer to a single column in a block of external data, the intersection operator would be useful e.g. =SUMIF(A:A NorthwindInv,A2,C:C NorthwindInv) where ‘NorthwindInv’ is the external data range name. I haven’t seen this much used in practice or indeed written about. Is there some good reason why it’s a bad idea?”

Hmm, the thing is, that style of referring to a ‘Range within a Range’ is new to me. Anyone have any thoughts to share?

I’ve been interviewing recently and have been subjecting the candidates to a rather nice VBA question. I thought the regulars here might get a kick out of thinking up the ‘perfect’ answer for one or more of the scenarios. It involves a Sub Main calling a Function which takes a single parameter:

Scenario a): the parameter is a Long integer and is passed using the ByRef keyword. The Function changes the parameter variable’s value. What is the purpose of the ByRef keyword in this context?

Scenario b): the parameter is a String and is passed using the ByRef keyword. The Function makes no change to the parameter variable’s value. What is the purpose of the ByRef keyword in this context?

Scenario c): the parameter is an ADO Recordset object and is passed using the ByVal keyword. The Function makes changes to the Recordset’s Filter and Sort properties. What is the purpose of the ByVal keyword in this context?

What do you think?