Addin Function, reference active worksheet

Question

I have created an addin which has 1 ribbon control, which performs a SQL statement on an Access DB and pastes the result, one cell to the right. This works perfectly. It is called SubTest(c as iribboncontrol) I also have another sub, called GetData(arg1,arg2) which I intended to use from the worksheet, as =GetData("01/02/2016","Open") and then one to the right again, the customised SQL returned is presented. This all works ok, until the pasting ActiveCell.Offset(1, 1).CopyFromRecordset rstData works fine from the ribbon, but not from the function call, am I missing something simple.

Having get data, like so, still gives errors

public sub get_data()
     activecell.offset(1,1).value="TEST"
end sub

Many thanks

A function called from a cell as a worksheet function in itself cannot change the value of another cell or cells. — Scott Craner, Nov 29 '16 at 15:45
Cheers, I was hoping for something fancy to build ad-hoc queries :) — Nathan_Sav, Nov 29 '16 at 15:46

score 3 · Accepted Answer · edited May 23 '17 at 10:31

A function takes inputs and returns a value. It cannot have side-effects¹. Hence, you can't call a procedure that alters cell values inside a function that's called from a worksheet cell, as Scott Craner already mentioned.

There are a number of things that are wrong with this though:

Public Sub get_data()
     ActiveCell.Offset(1,1).Value="TEST"
End Sub

Code that relies on ActiveCell means you have other code that uses Select and Activate. This makes your code extremely frail and error/bug prone, avoid them (see how).

Avoid underscores in procedure names; use PascalCase instead. This isn't just "because it's convention" - VBA will refuse to compile more advanced code that has underscores in interface member names; so even if you don't ever use interfaces and the Implements keyword, make it a habit to avoid underscores in member names. In VBA the underscore has a special meaning, such as you can see in event handler procedures: ObjectName_MemberName, e.g. Button1_Click.

Name things for what they do. "get data" would be the name of a function, that "gets" a value [and returns it]. A Sub that "gets" something makes no sense. Your procedure doesn't "get" anything, it "sets" a value if anything.

¹ Function procedures called from VBA code can definitely have side-effects. The fact is, in an ideal world they shouldn't; use procedures for side-effecting code instead. Excel worksheet functions are pure functions, and user-defined functions are required to be more or less pure functions as well, else they simply can't be used in a worksheet. Take some input, compute a value, return that value: that's all a function does.

Addin Function, reference active worksheet

1 Answers1