Tuesday, September 1, 2009

Little Nitpick on VBA - Continued

Here is a comment on was posted on my 'nitpick' blog post by 'Jared'; I thought it would be better as a separate post:

Speaking of lost productivity...one thing that has thrown me off in VBA (with Essbase or not) is the syntax for calling functions and procedures--when to use parentheses or not. For example, every Essbase VBA programmer has used EssVRetrieve, probably in the format:

dim sts as long

'Retrieve data from the current sheet
sts=EssVRetrieve(Null, Null, 1)

The function runs and its return value is assigned to the variable sts.

Now, in some cases, I want to call functions and I don't care about a return code. Or, I want to call a procedure (which you cannot assign to value, of course). But if you do this:


...or this, for example:


...you'll get a syntax error. And since function syntax in most documentation includes the parentheses, you may have a hard time figuring out the error. Even the auto-complete in the VBA editor seems to indicate that the parentheses are OK: type a left paren after the
function/sub, and it will list the arguments for you, as if everything is just fine.

Unfortunately, it isn't. :)
So the rule is this: only use parens with a function call if you are assigning a value to the function; and if it's a sub procedure, you never use parens. So you would have:

EssVRetrieve Null,Null,1 'Retrieve data, don't worry about return code


CheckCellValue Activesheet.Name, 5, FALSE 'Call "CheckCellValue" proc, passing parms

Hope this isn't too much of a derail, but it just came to mind...

Not too much of a derail at all Jared as I think it will be useful information for a number of blog readers.

No comments: