In a general module of Workbook1 I am calling a Sub procedure in
WorkbookAddIn. I want the WorkbookAddIn Sub to add a worksheet to
Workbook1. Can that be done?

Thanks,
Alan Beban

Workbooks("Workbook1_Name").Worksheets.Add

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Alan Beban" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> In a general module of Workbook1 I am calling a Sub procedure in
> WorkbookAddIn. I want the WorkbookAddIn Sub to add a worksheet to
> Workbook1. Can that be done?
>
> Thanks,
> Alan Beban

This worked for me:

In the addin:
Function AddSheet(ByVal wkbToAddTo As Workbook) As Worksheet
Set AddSheet = wkbToAddTo.Worksheets.Add
End Function

In the caller:
Sub Test()
Dim wks As Worksheet
Set wks = Application.Run("Addin.xla!AddSheet", ThisWorkbook)
MsgBox "Sheet added is " & wks.Name & " to " & wks.Parent.Name
End Sub


--
Tim Zych
SF, CA


"Alan Beban" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> In a general module of Workbook1 I am calling a Sub procedure in
> WorkbookAddIn. I want the WorkbookAddIn Sub to add a worksheet to
> Workbook1. Can that be done?
>
> Thanks,
> Alan Beban

Bob Phillips wrote:
> Workbooks("Workbook1_Name").Worksheets.Add
>
Thanks, Bob. But the Add-In Sub doesn't know the name of the workbook of
the calling Sub.

Alan

Tim Zych wrote:
> This worked for me:
>
> In the addin:
> Function AddSheet(ByVal wkbToAddTo As Workbook) As Worksheet
> Set AddSheet = wkbToAddTo.Worksheets.Add
> End Function
>
> In the caller:
> Sub Test()
> Dim wks As Worksheet
> Set wks = Application.Run("Addin.xla!AddSheet", ThisWorkbook)
> MsgBox "Sheet added is " & wks.Name & " to " & wks.Parent.Name
> End Sub
>
>
Thanks, Tim. But like Bob Phillips' suggestion, this requires the
calling procedure to supply the code for adding the worksheet, and the
user won't know that this is required when calling the Add-In.

Since I posted I played with it some more, and the solution makes me
feel somewhat silly for posting. In the AddiIn Sub, simply

Worksheets.Add with no qualifiers seems to work fine to add the
worksheet in the calling Sub's workbook. I had coded
ActiveWorkbook.Worksheets.Add and it was adding the worksheet to the
Add-In workbook.

Thanks for responding.

Alan Beban

In the Active Code (WB1) you can refer to WB1 as Thisworkbook.

So your addin procedure should refer (in some way) to the phrase:

Thisworkbook.Worksheets.Add

HTH

"Alan Beban" wrote:

> Bob Phillips wrote:
> > Workbooks("Workbook1_Name").Worksheets.Add
> >
> Thanks, Bob. But the Add-In Sub doesn't know the name of the workbook of
> the calling Sub.
>
> Alan
>

I don't understand..why not just encapsulate the complexity. ThisWorkbook is
always the caller, and that macro can be inserted into the wrapper that
accesses the addin, so there's nothing special for the caller / user to do
then.

> Worksheets.Add with no qualifiers seems to work fine to add the worksheet
> in the calling Sub's workbook. I had coded
> ActiveWorkbook.Worksheets.Add and it was adding the worksheet to the
> Add-In workbook.

Now I'm confused. Maybe at this point I have no clue about what you are
trying to do. When I do either Worksheets.Add or
ActiveWorkbook.Worksheets.Add, it always adds it to the active workbook.
What if the caller is hidden or inactive? My approach is more robust and
exact. I'm very nitpicky about unqualified references.


--
Tim Zych
SF, CA

"Alan Beban" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Tim Zych wrote:
>> This worked for me:
>>
>> In the addin:
>> Function AddSheet(ByVal wkbToAddTo As Workbook) As Worksheet
>> Set AddSheet = wkbToAddTo.Worksheets.Add
>> End Function
>>
>> In the caller:
>> Sub Test()
>> Dim wks As Worksheet
>> Set wks = Application.Run("Addin.xla!AddSheet", ThisWorkbook)
>> MsgBox "Sheet added is " & wks.Name & " to " & wks.Parent.Name
>> End Sub
>>
>>
> Thanks, Tim. But like Bob Phillips' suggestion, this requires the calling
> procedure to supply the code for adding the worksheet, and the user won't
> know that this is required when calling the Add-In.
>
> Since I posted I played with it some more, and the solution makes me feel
> somewhat silly for posting. In the AddiIn Sub, simply
>
> Worksheets.Add with no qualifiers seems to work fine to add the worksheet
> in the calling Sub's workbook. I had coded
> ActiveWorkbook.Worksheets.Add and it was adding the worksheet to the
> Add-In workbook.
>
> Thanks for responding.
>
> Alan Beban

Alan,

Can you use

Activeworkbook.Worksheets.Add

If not, somewhere along the line you will have to capture the workbook and
save it in a variable.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Alan Beban" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Bob Phillips wrote:
>> Workbooks("Workbook1_Name").Worksheets.Add
>>
> Thanks, Bob. But the Add-In Sub doesn't know the name of the workbook of
> the calling Sub.
>
> Alan

Bob Phillips wrote:
> Alan,
>
> Can you use
>
> Activeworkbook.Worksheets.Add
>
> If not, somewhere along the line you will have to capture the workbook and
> save it in a variable.
>
That's what I started with, and it added the worksheet to the Add-In
workbook rather than the workbook of the calling procedure. I have since
posted saying that if I use

Worksheets.Add, without any qualifier

in the Add-In Sub, it adds the worksheet to the workbook of the calling
Sub, which solves my problem. But I still need to get my head around Tim
Zych's most recent post in this thread.

Thanks for responding,
Alan

Tim Zych wrote:
> I don't understand..why not just encapsulate the complexity. ThisWorkbook is
> always the caller, and that macro can be inserted into the wrapper that
> accesses the addin, so there's nothing special for the caller / user to do
> then.
>
>
>>Worksheets.Add with no qualifiers seems to work fine to add the worksheet
>>in the calling Sub's workbook. I had coded
>>ActiveWorkbook.Worksheets.Add and it was adding the worksheet to the
>>Add-In workbook.
>
>
> Now I'm confused. Maybe at this point I have no clue about what you are
> trying to do. When I do either Worksheets.Add or
> ActiveWorkbook.Worksheets.Add, it always adds it to the active workbook. . . .

???What is the active workbook in your statement above? The workbook of
the calling Sub or of the Add-In Sub?

Conceptualize a 3D array as a rectangular solid resting on the xy-plane
(analogous to the rows columns plane of a worksheet), with the third
dimension projecting toward the viewer)

I have a Sub procedure in an Add-In workbook named "ArrayFunctions". The
sub is Sub Save3DInWorksheet(inputArray, Optional ByVal Orientation As
String = "XY). (To ease this presentation, I will ignore the fact that
the orientation of the output might by "XZ" or "YZ"; i.e., planes
orthogonal to the xy-plane). Assuming, for illustration, an array that
has been declared with

ReDim arr(1 to 2, 1 to 3, 1 to 4) and loaded,

the Add-In Sub deposits the 24 elements of arr onto a worksheet in the
following form, with the index numbers of arr representing the elements:

1,1,1 1,2,1 1,3,1
2,1,1 2,2,1 2,3,1

1,1,2 1,2,2 1,3,2
2,1,2 2,2,2 2,3,2

1,1,3 1,2,3 1,3,3
2,1,3 2,2,3 2,3,3

1,1,4 1,2,4 1,3,4
2,1,4 2,2,4 2,3,4

The calling Sub, in a workbook named "test23D", will be, as an illustration

Sub test1()
Dim w
ReDim w(1 To 2, 1 To 3, 1 To 4)
For i = 1 To 2: For j = 1 To 3: For k = 1 To 4
w(i, j, k) = i + 2 * j + 3 * k
Next: Next: Next
Save3DInWorksheet w
End Sub

In the Add-In Sub is included the following snippet to provide the
destination for the output; i.e., the elements of the 3D array:

ReDim sName(1 To 3)
sName(1) = "XY"
sName(2) = "XZ"
sName(3) = "YZ"
On Error Resume Next
For q = 1 To 3
Set wSheet = ActiveWorkbook.Sheets(sName(q))
If Not Err = 0 Then
Worksheets.Add
ActiveSheet.Name = sName(q)
Err = 0
End If
Next

That snippet checks the Add-In Sub and, if the worksheets don't exist in
it, adds them to the Add-In Sub. I'm using xl2002. The result is the
same if I substitute ThisWorkbook for ActiveWorkbook.

But if I omit ActiveWorkbook in the above snippet, it checks for the
existence of the sheets in the workbook of the calling Sub, i.e.,
test23D, and if they don't exist there, it adds them to test23D, which
is the desired result.

Thanks again for spending time on this,
Alan Beban