GetActiveSheet error

Add-in Express™ Support Service
That's what is more important than anything else

GetActiveSheet error
Get active sheet Error with Chart 
Michael Kaden


Guest


Dear Andrei,

On opening a workbook I am using

Public Function GetActiveSheet() As Excel.Worksheet
Return ExcelApp.ActiveSheet
End Function

so I can go back to the initial sheet after looping through sheets:

If the sheet having focus on opening however is a chart then we get a runtime error. I understand that ExcelApp.Activesheet will raise an error if there is no Sheet Active. How can this be avoided other than with exemption handling?

Thank you and kind regards

Michael
Posted 18 Apr, 2018 04:14:01 Top
Andrei Smolin


Add-in Express team


Posts: 18794
Joined: 2006-05-11
Hello Michael,

In that code you forcibly cast the result of ExcelApp.ActiveSheet to Excel.Worksheet. I suppose the issue occurs because that property returns Nothing (due to the cast) in that scenario and your code isn't prepared to getting Nothing.


Andrei Smolin
Add-in Express Team Leader
Posted 18 Apr, 2018 04:38:37 Top
Michael Kaden


Guest


Dear Andrei,

thank you for your answer. I think I cannot test if there is a sheet or a chart as:

If xlSheet IsNot Nothing then......

would raise an error and I cannot define xlSheet = Workbook.Sheet.Item(k)

as I do not know which sheet is a sheet and which a chart or would Workbook.Sheet.Item(1)always be a sheet? and if so, I would still raise an error if there are only Charts in the Workbook.

I now "solved" it with:

On Error Resume Next
xlSheet = GetActiveSheet()
xlChart = ExcelApp.ActiveChart
On Error GoTo Errorhandler

and at the end

On Error Resume Next
xlSheet.Activate()
Marshal.ReleaseComObject(xlSheet) : xlSheet = Nothing
xlChart.Activate()
Marshal.ReleaseComObject(xlChart) : xlChart = Nothing
On Error GoTo Errorhandler

I do not like the suppression of errors, perhaps you have a better idea? Or in other words, how to find out if an object is not there without raising an error.

BTW I still use

On Error Goto Errorhandler
.............
Exit sub
Errorhandler:
Action
End Sub

As I, after reading about it and experimenting, do not see what Try/Catch/Finally/EndTry can do better. In my sample projects I use msgbox as Errorhandler Action for simplicity in the "real" programs I go to a procedure where the client can decide if he wants to see errors, if he wants to suppress repeating errors (same errornu.) and if he wants to write all errors to a log file. So far I make my own log file, perhaps I should use Application.log to also log crashes etc.

Any comment to my error "philosophy" is very welcome.

Thank you & kind regards

Michael
Posted 18 Apr, 2018 12:00:47 Top
OliverM


Guest


Michael,

You can figure out a sheet type as described https://excel.tips.net/T002538_Detecting_Types_of_Sheets_in_VBA.html.

A more general approach is to query the COM interface for the type name using the below helper class.

using System;
using System.Runtime.InteropServices;

namespace XlAddIn.Log4VBA
{
    using ComTypes = System.Runtime.InteropServices.ComTypes;

    /// <summary>
    /// A COM helper class
    /// </summary>
    internal sealed class ComUtils
    {
        /// <summary>
        /// Returns a string value representing the type name of the specified
        /// COM <see langword="object"/>.
        /// </summary>
        /// <param name="comObj">A COM object the type name of which to return.
        /// </param>
        /// <returns>A string containing the type name.</returns>
        internal static string GetTypeName(object comObj)
        {
            if (comObj == null)
            {
                return string.Empty;
            }

            if (!Marshal.IsComObject(comObj))
            {
                // The specified object is not a COM object
                return string.Empty;
            }

            IDispatch dispatch = comObj as IDispatch;
            if (dispatch == null)
            {
                // The specified COM object doesn't support getting type information
                return string.Empty;
            }

            ComTypes.ITypeInfo typeInfo = null;
            try
            {
                try
                {
                    // obtain the ITypeInfo interface from the object
                    dispatch.GetTypeInfo(0, 0, out typeInfo);
                }
                catch
                {
                    // Cannot get the ITypeInfo interface for the specified COM object
                    return string.Empty;
                }

                string typeName;

                try
                {
                    // retrieves the documentation string for the specified type description 
                    string documentation;
                    string helpFile;
                    var helpContext = -1;
                    typeInfo.GetDocumentation(
                        -1,
                        out typeName,
                        out documentation,
                        out helpContext,
                        out helpFile);
                }
#pragma warning disable 168
                catch (System.Exception ex)
#pragma warning restore 168
                {
                    // Cannot extract ITypeInfo information
                    return string.Empty;
                }

                return typeName;
            }
            catch (System.Exception ex)
            {
                // Unexpected error, return
                // empty string
                return string.Empty;
            }
            finally
            {
                if (typeInfo != null)
                {
                    Marshal.ReleaseComObject(typeInfo);
                }
            }
        }
    }

    /// <summary>
    /// Exposes objects, methods and properties to programming tools and other
    /// applications that support Automation.
    /// </summary>
    [ComImport]
    [Guid("00020400-0000-0000-C000-000000000046")]
    [InterfaceType(ComInterfaceType.InterfaceIsIUnknown)]
    internal interface IDispatch
    {
        [PreserveSig]
        int GetTypeInfoCount(out int count);

        [PreserveSig]
        int GetTypeInfo(
            [MarshalAs(UnmanagedType.U4)] int iTInfo,
            [MarshalAs(UnmanagedType.U4)] int lcid,
            out ComTypes.ITypeInfo typeInfo);

        [PreserveSig]
        int GetIDsOfNames(
            ref Guid riid,
            [MarshalAs(UnmanagedType.LPArray, ArraySubType = UnmanagedType.LPWStr)] string[] rgsNames,
            int cNames,
            int lcid,
            [MarshalAs(UnmanagedType.LPArray)] int[] rgDispId);

        [PreserveSig]
        int Invoke(
            int dispIdMember,
            ref Guid riid,
            uint lcid,
            ushort wFlags,
            ref ComTypes.DISPPARAMS pDispParams,
            out object pVarResult,
            ref ComTypes.EXCEPINFO pExcepInfo,
            IntPtr[] pArgErr);
    }

    //// end code
}

Posted 19 Apr, 2018 02:05:04 Top
Andrei Smolin


Add-in Express team


Posts: 18794
Joined: 2006-05-11
Hello Michael and Oliver,

Michael Kaden writes:
I cannot define xlSheet = Workbook.Sheet.Item(k)


You can. Simply declare xlSheet as Object.

OliverM writes:
You can figure out a sheet type as described here.


A great suggestion!

OliverM writes:
A more general approach is to query the COM interface for the type name using the below helper class.


Consider calling Microsoft.VisualBasic.Information.TypeName(comObj) instead of using that helper class. For the sake of completeness, I have to admit that Microsoft.VisualBasic.Information.TypeName() returns "System.__ComObject" on OneNote objects; probably, this relates to the nature of OneNote add-ins.

Michael, I suggest that you check if using Microsoft.VisualBasic.Information.TypeName() works for you. Note the oddity of the sheet.Type value described on that page. Make sure this doesn't apply to using the ...TypeName() approach.

And guys, FYI, when you run into an interesting piece of code in C# or VB.NET, you can use a free code converter available online; see e.g. http://converter.telerik.com/.

Michael Kaden writes:
Any comment to my error "philosophy" is very welcome.


You should gradually switch to using Try/Catch[/Finally]; see https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/try-catch-finally-statement.

Michael Kaden writes:
I do not like the suppression of errors, perhaps you have a better idea? Or in other words, how to find out if an object is not there without raising an error.


Here's a raw sketch:

dim sheet as Object = ExcelApp.ActiveSheet
if isWorksheet(sheet) = true then
    dim wks as Excel.Worksheet = sheet as Excel.Worksheet
    wks.Activate()
end if


or


dim sheet as Object = ExcelApp.ActiveSheet
dim wks as Excel.Worksheet = GetWorksheet(sheet)
dim chartSheet as ... = GetChart(sheet)
if (wks isNot Nothing) then wks.Activate()
if (chartSheet isNot Nothing) then wks.Activate()


Still, I would use a Try/catch to handle an exception that may occur when you activate a sheet. Make sure that you don't just skip exceptions: create a log record to know what occurs; the log record should include the result of calling anException.ToString().


Andrei Smolin
Add-in Express Team Leader
Posted 19 Apr, 2018 03:13:12 Top
Michael Kaden


Guest


Thank you Andrei, thank you Oliver,

I favoured Andrei's suggestion as it is the shortest code "the safest code is code never written", however

I used

dim wks as Excel.Worksheet = GetWorksheet(sheet) -
Code Error GetWorksheet is not declared

If I use

Dim sheetsheet As Excel.Worksheet = ExcelApp.GetWorksheet(sheet) -
Run time error Error 438 "Object Doesn't Support This Property or Method"

So what would be the proper code?

Thank you and kind regards

Michael
Posted 19 Apr, 2018 05:28:39 Top
Andrei Smolin


Add-in Express team


Posts: 18794
Joined: 2006-05-11
Michael,

You are meant to write that method yourself.


Andrei Smolin
Add-in Express Team Leader
Posted 19 Apr, 2018 05:42:58 Top
Michael Kaden


Guest


Dear Andrei,

I actually went down the wrong path, looking for the worksheet type. I battled a lot with xlSheetType Enumeration as in my Excel, the chart sheet returns a value of -4169 and not -4109 , could not find out why.

However,what I wanted to achieve from the beginning was to return focus to the sheet which was active before I loop through all sheets. Your pointer to take activesheet as an object actually lead me to the easiest solution:

Before looping through the worksheets:

Dim sheet As Object = ExcelApp.ActiveSheet

and after the loops through all worksheets are completed:

xlBook.Sheets.Item(sheet.name).Activate

This does the trick regardless what type the initial worksheet is.

Thank you & kind regards

Michael
Posted 20 Apr, 2018 04:19:19 Top
Andrei Smolin


Add-in Express team


Posts: 18794
Joined: 2006-05-11
Great!


Andrei Smolin
Add-in Express Team Leader
Posted 20 Apr, 2018 04:59:17 Top