possible to save macro with workbook as add-in?

[John Smith]

I finished writing a macro. It works well. When I save the macro
as an add-in and then open the add-in, it doesn't work. It
complains about codes like sheet1.Cells(1,2).select. Seems the
workbook was not saved along with the macro.

Is it possible to save the workbook with macro as an add-in?

 

[Jake Marx]

Hi John,

I finished writing a macro. It works well. When I save the macro
as an add-in and then open the add-in, it doesn't work. It
complains about codes like sheet1.Cells(1,2).select. Seems the
workbook was not saved along with the macro.

This is because an Excel add-in has no visible worksheets. So trying to
select one will always result in error. What are you trying to do? Select
something in the active workbook, or get the value from a cell on sheet1 of
the add-in itself? If you want to interact with the user's current active
workbook (which will be different than the add-in), you would use
ActiveSheet or ActiveWorkbook.Worksheets(1) or similar. To interact with
the add-in, you would do like you have done without using the Select method:

Dim sName As String

sName = Sheet1.Cells(1, 2).Value '/ instead of selecting and using
Selection.Value

Is it possible to save the workbook with macro as an add-in?

Yes, but as I said above, the worksheets will not be visible to the end
user. You can still interact with them all you want (if you don't activate
or select anything).

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[John Smith]

Thanks for the prompt reply. What I would like to do is have the
macro generate a spreadsheet and put formula on the sheet. I tried
something like application.worksheets.add() but that did not work.

 

[Jake Marx]

Hi John,

OK. Since your add-in is hidden, adding worksheets to it wouldn't do you
any good (that is, if you want your end user to see the new worksheet). So,
you probably want to create a new workbook and add formulas to the first
worksheet in that workbook, right? If so, here's how you could do it:

Sub demo()
Dim wb As Workbook

Set wb = Workbooks.Add

With wb.Worksheets(1)
.Cells(1, 1).Value = 2
.Cells(2, 1).Formula = "=A1*2"
End With

Set wb = Nothing
End Sub

This is just a simple example, but hopefully it gets you started in the
right direction.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]