Best way to pass a named range to a macro?

[JenniferMurphy]

I am working on a macro that will analyze data in a table and some data outside the table or possibly in a second table. What's the best way to pass those ranges to the macro?

I prefer to call the macro using a button form control. I've done that several times. But as far as I know, there is no way to pass any parameters (arguments) that way.

Thanks for any help.

 

[RoryA]

You can pass arguments using a macro assigned to a button, but they should be data values, not objects. (It also won't work if your workbook is saved in xlsb format).

If it is always the same table(s), then I'd just refer to them directly in the code.

 

[dmt32]

Have a look at this article & see if any help: Assign macro with arguments to a Form Control button - Excel Off The Grid

Dave

 

[JenniferMurphy]

You can pass arguments using a macro assigned to a button, but they should be data values, not objects. (It also won't work if your workbook is saved in xlsb format).

At the moment, it's saved as xlsx. I've never used an xlsb format.

If it is always the same table(s), then I'd just refer to them directly in the code.

If I do that, then I cannot have another table in a different sheet in the same workbook, because Excel chose to make table named global with no option to make them local. I believe this is in violation of modern structured programming conventions. (sigh)

 

[JenniferMurphy]

Have a look at this article & see if any help: Assign macro with arguments to a Form Control button - Excel Off The Grid

Dave

Thanks, Dave. That looks like the solution. I'll give it a try. I have marked this as the thread solution, but will be back if it doesn't work!

 

[JenniferMurphy]

Have a look at this article & see if any help: Assign macro with arguments to a Form Control button - Excel Off The Grid

I'm getting an error. I suspected it might be because the macro is in an add-in module. Is that a no-no?

To test it, I saved the workbook as xlsm, added a code module, and moved the macro code to that module. Now the macro code works.

But I want it in the add-in so it can be called from more than 1 workbook. I did a little checking and found a webpage that said I needed to include the full path to the add-in module.

Here's the macro code:

Sub WtdRtg(pTblParms As String, pTblData As String)

MsgBox "Hi"

End Sub

The add-in is in this folder: C:\Users\username\AppData\Roaming\Microsoft\AddIns

So I moved the macro code back to the add-in module and changed the macro assign string in the Form Button control to this:

['C:\Users\username\AppData\Roaming\Microsoft\AddIns\My Add-Ins.xlam'!'WtdRtg "TblParms", "TblData"'

But when I click on the button, I get this error message:

I then checked the macro assign string and it had gone back to this: 'Weighted Ratings.xlsm'!'WtdRtg "TblParms", "TblData"', which is what it was when the macro was in the workbook module. I tested this a couple of times. Every time I pasted the longer text with the add-in path and clicked OK, and then reopened it, it had gone back.

Can you tell me what I am doing wrong now?

 

[dmt32]

I'm getting an error. I suspected it might be because the macro is in an add-in module. Is that a no-no?

I no longer say something cannot be done as there is always someone out there that will tell you otherwise

I have never used a forms button to call code in that manner & cannot immediately give you any informed guidance - maybe another can.
I am on granddad (school run) duties today but If find a moment I will have a play and if have any success, will come back.

Dave

 

[RoryA]

Using:

'C:\Users\username\AppData\Roaming\Microsoft\AddIns\My Add-Ins.xlam'!'WtdRtg("TblParms", "TblData")'

should work.

 

[JenniferMurphy]

Using:

'C:\Users\username\AppData\Roaming\Microsoft\AddIns\My Add-Ins.xlam'!'WtdRtg("TblParms", "TblData")'

should work.

I wondered about the lack of parens. But that's what the webpage Dave recommended said to do (no parens).

I just tried your way and got this error message:

 

[RoryA]

In which module in the addin is the WtdRtg macro?