Option Explicit On

'cf http://msdn.microsoft.com/en-us/library/2k3te2cs.aspx
'Regular Expressions for Find and Replace
'{:a+}\.address
'range_address(\1)

Imports ExcelDna.Integration           ' IExcelAddin, ExcelDnaUtil, xlCall
Imports ExcelDna.Integration.CustomUI   ' needed for IRibbonControl
Imports NetOffice.ExcelApi              ' Application, Workbook, Worksheet etc
'Imports NetOffice.ExcelApi.Enums		' for XL* constants
Imports System.Runtime.InteropServices  ' needed for <ComVisible(True)> 
Imports System.IO                       ' Stream I/O
Imports LateBindingApi.Core				' for Netoffice Factory.Initialize() 
Imports NetOffice.ExcelApi.Modules

'http://msdn.microsoft.com/en-us/library/gg264614.aspx
'Compiler Constants - VBA Office 2010
' Win16, Win32, Win64, VBA6, VBA7, Mac
'In VB.NET,
'Conditional compiler constants are always private to the file in which they appear.
' There is no way to declare public compiler constants via the #Const directive
'Three conditional compilation constants are provided: Config, Debug, and Trace.
#Const Win32 = True	' for VBA code migrated from dual Mac/Win add-in

'Create an ExcelAddIn-derived class with AutoOpen and AutoClose, 
'and add a module called AddInMain to hold the Application object reference:

' This class is implemented only to allow us to initialize NetOffice
' We hook up a public field in the Module AddInMain 
'    that will be usable anywhere in the project.
Public Class thisAddIn
    Implements IExcelAddIn

    Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen
        ' must initialise here because XlCall cannot be used from Ribbon context, only in a macro context
        'FullName = System.IO.Path.GetFileName(XlCall.Excel(XlCall.xlGetName))
        'Path = System.IO.Path.GetDirectoryName(XlCall.Excel(XlCall.xlGetName))

        Factory.Initialize()    ' in LateBindingApi.Core.
        ' Set a public field in a module, so that Application will be available everywhere.
        Application = New Application(Nothing, ExcelDnaUtil.Application)

		End Sub

    Public Sub AutoClose() Implements IExcelAddIn.AutoClose ' Must be declared even if not used

    End Sub
End Class


' This module contains <global> shortcuts to the Application members.
Public Module GlobalHelper
    Property Application As NetOffice.ExcelApi.Application ' linked to ExcelDnaUtil.Application in AutoOpen
    Property ThisWorkbook As Workbook

    Function Array(ByVal ParamArray items() As Object) As Array
        Array = items
    End Function

    Public Function IsEmpty(ByVal p1 As Object) As Boolean
        Return IsNothing(p1)
    End Function

    Public Function IsNull(ByVal p1 As Object) As Boolean
        Return IsDBNull(p1)
    End Function

    Public Function IsObject(ByVal p1 As Object) As Boolean
        Return TypeOf (p1) Is Object
    End Function

    Public Function AppendToFile(ByVal FileName As String, ByVal txt As String) As String
        Dim ErrInfo As String = vbNullString
        SaveTextToFile(txt, FileName, ErrInfo, True)
        Return ErrInfo
    End Function

    Public Function GetFileContents(ByVal FullPath As String, _
            Optional ByRef ErrInfo As String = "") As String

        Dim strContents As String = vbNullString
        Dim objReader As StreamReader
        Try
            objReader = New StreamReader(FullPath)
            strContents = objReader.ReadToEnd()
            objReader.Close()
        Catch Ex As Exception
            ErrInfo = Ex.Message
        End Try
        Return strContents
    End Function

    Public Function SaveTextToFile(ByVal strData As String, _
            ByVal FullPath As String, _
            Optional ByVal ErrInfo As String = "", _
            Optional ByVal append As Boolean = False) As Boolean

        Dim bAns As Boolean = False
        Dim objReader As StreamWriter
        Try
            objReader = New StreamWriter(FullPath, append)
            objReader.Write(strData)
            objReader.Close()
            bAns = True
        Catch Ex As Exception
            ErrInfo = Ex.Message

        End Try
        Return bAns
    End Function

    Function Round(ByVal arg1 As Double, ByVal arg2 As Double) As Double
        Return Application.WorksheetFunction.Round(arg1, arg2)
    End Function

    ' Used to get around NetOffice errors about "not a member of 'LateBindingApi.Core.COMObject'
    ' but 1.5.0 now allows .Parent etc

    ' wrapper to help get .Name etc
    Function ParentName(ByVal obj As Object) As String
        On Error Resume Next
        ParentName = vbNullString
        ParentName = obj.Parent.Name       ' Return
    End Function
    Function ObjectCount(ByVal obj As Object) As Long
        On Error Resume Next
        ObjectCount = 0
        ObjectCount = obj.count   ' Return
    End Function

    ' Get obj.collection(item) by name or Index posn to get around
    'Error	62	Class 'LateBindingApi.Core.COMObject' cannot be indexed because it has no default property.	
    Function ObjectItem(ByVal oOwner As Object, ByVal Item As Object) As Object
        ObjectItem = oOwner(Item)
    End Function

    Function Workbooks_Open(ByVal filename As String, ByVal updateLinks As Boolean, ByVal read_Only As Boolean) As Workbook
        Return Workbooks.Open(filename:=filename, updateLinks:=updateLinks, readOnly:=read_Only, _
                              format:=5, password:=Nothing, writeResPassword:=Nothing, _
                              ignoreReadOnlyRecommended:=Nothing, origin:=2, addToMru:=False, _
                              converter:=Nothing, corruptLoad:=Nothing, delimiter:=Nothing, _
                              editable:=False, local:=Nothing, notify:=Nothing)
        'http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.open(v=office.11).aspx
        'format:=5 any value from 1-6 will do if it's not a text file
        'origin:=Enums.XlPlatform.xlWindows=2
    End Function

    ReadOnly Property ActiveCell As Range
        Get
            Return Application.ActiveCell
        End Get
    End Property

    ReadOnly Property ActiveSheet As Worksheet
        Get
            Return Application.ActiveSheet
        End Get
    End Property

    ReadOnly Property ActiveChart As Chart
        Get
            Return Application.ActiveChart
        End Get
    End Property

    ReadOnly Property ActiveWindow As Window
        Get
            Return Application.ActiveWindow
        End Get
    End Property

    ReadOnly Property ActiveWorkbook As Workbook
        Get
            Return Application.ActiveWorkbook
        End Get
    End Property

    ReadOnly Property Workbooks As Workbooks
        Get
            Return Application.Workbooks
        End Get
    End Property


    ReadOnly Property Selection As Object
        Get
            Return Application.Selection
        End Get
    End Property

    Public Const vbRed As Long = 255

End Module

'The ExcelRibbon-derived class must also be marked as ComVisible(True),
' or in the project properties, advanced options, the ComVisible option must be checked.
' (Note that this is not the ‘Register for COM Interop’ option, which must never be used with Excel-DNA)

<ComVisible(True)> _
Public Class Ribbon
    Inherits ExcelRibbon
    'ExcelDna provides a feature to use onAction="RunTagMacro" which will run a VBA sub named in tag="MyVBAMacro"
    ' Only methods in this class are visible to onAction :

    Sub RunIDMacro(ByVal ctl As IRibbonControl)
        Application.Run(ctl.Id)
    End Sub

    Sub RunIDMacroWithTag(ByVal ctl As IRibbonControl)
        Application.Run(ctl.Id, ctl.Tag)
    End Sub

End Class