Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Type Mismatch between Worksheets in Excel

Posted on 2011-05-09
9
Medium Priority
?
495 Views
Last Modified: 2012-05-11
Hi,

I have a custom addin, written in VBA for EXCEL, that contains a custom worksheet with a additional macros, and properties. The worksheet is named 'ReportSheet' in the Addin I want a macro that will copy this worksheet to my activeworkbook and allow access to the custom macros... I'm using code like this below:

Dim myReport As PTSREPORTS.ReportSheet
myAddin.ReportSheet.Copy After:=ActiveWorkbook.Sheets(Sheets.Count)
Set myReport = ActiveSheet

This function breaks with Run time error:13 "type mismatch". Using watches in the VBA editor, I can see that the type of ActiveSheet is "Object/ReportSheet", whereas the type of myReport is just "ReportSheet". Is there a way to disable the runtime check and accept the set statement, or to cast the objects to each other?
0
Comment
Question by:jonathan_hills
9 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35721271
jonathan_hills,

Why not simply declare myReport as type Worksheet?  Based on the code sample above, when you are setting the object, you are referring to a worksheet in the currently active workbook and not in the add-in, so I do not see what you are gaining by using the stricter PTSREPORTS.ReportSheet declaration.

Patrick
0
 

Author Comment

by:jonathan_hills
ID: 35721440
When I declare myReport as type Worksheet, I can not run any of the custom macros attached to it. Attempting to run myReport.myCustomFunction returns a VB error "Method or Data Member not found"

I've attributed this to the fact that myReport is declared as a Worksheet Object, and that Excel isn't looking at the object to see what functions this object has that are different from the regular Worksheet object
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35721468
I wonder if it is being run on the add-in and the sheets count is being referenced to the add-in rather than the activeworkbook.  I'm not saying it is a factor in the concern, but it would be easier for any viewers if it was explicit.

Chris
Dim myReport As PTSREPORTS.ReportSheet
myAddin.ReportSheet.Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Set myReport = ActiveSheet

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:jonathan_hills
ID: 35721495
I changed the code to take the ActiveWorkbook into account when referencing the sheet count as suggested... The results are the same
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35721615
Declare it as an Object rather than Worksheet and you should be able to call the macros.
0
 

Author Comment

by:jonathan_hills
ID: 35721711
I have been declaring this as an object for a few and it allows me to set the myReport variable correctly, however, there seem to be other issues down the road, including stability issues causing Excel to crash. I was hoping for a different solution.

When using the 'Object' solution, sometimes the handle shows up as 'Variant/Object/Reportsheet'. and sometimes as 'Object/Reportsheet'. Does anyone understand the difference.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35721733
If you declare as Object, it should not be a Variant ever.
That should also not cause stability issues - if you are getting crashes, I would suspect that it is due to something else.
0
 

Author Comment

by:jonathan_hills
ID: 35721806
In another function, that Gets called from a CommandBarButton, I need to capture the report worksheet from the active selection. When I look at Selection.Worksheet... the type is defined as Variant/Object/Worksheet

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35721944
It should not be if you assign it to an Object variable.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question