Breaking News: Grepper is joining You.com. Read the official announcement!

what are some basic framework of reusable functions for Excel (XLS) using VBA?

ShowGo Shane answered on December 18, 2022 Popularity 1/10 Helpfulness 1/10

answer what are some basic framework of reusable functions for Excel (XLS) using VBA?

what are some basic framework of reusable functions for Excel (XLS) using VBA?

Comment

Option Compare Database
Option Explicit

Private Const sModName = "mod_MSExcel" 'For Error Handling

Public oExcel           As Object    'Excel Application Object
Public oExcelWrkBk      As Object    'Excel Workbook Object
Public oExcelWrSht      As Object    'ExcelWorksheet Object
Public bExcelOpened     As Boolean    'Was Excel already open or not


Public Sub LaunchExcel(Optional bVisible As Boolean = True)
On Error Resume Next
    Set oExcel = GetObject(, "Excel.Application") 'Bind to existing instance of Excel
    
    If Err.Number <> 0 Then 'Could not get instance of Excel, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set oExcel = CreateObject("Excel.Application")
        bExcelOpened = False
    Else    'Excel was already running
        bExcelOpened = True
    End If

On Error GoTo Error_Handler
    oExcel.Visible = bVisible

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: LaunchExcel" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Sub AddExcelWrkBk(Optional ByVal sTmplt As String = "")
'Create a new workbook based on a template file
    On Error GoTo Error_Handler

    If sTmplt = "" Then
        Set oExcelWrkBk = oExcel.Workbooks.Add()
    Else
        'Technically should test for the existance of the file before trying to use it
        Set oExcelWrkBk = oExcel.Workbooks.Add(sTmplt)
    End If

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: AddExcelWrkBk" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Sub OpenExcelWrkBk(ByVal sWrkBk As String, Optional sPwd As Variant)
'Open an existing Excel Workbook
    On Error GoTo Error_Handler

    If IsMissing(sPwd) Then
        Set oExcelWrkBk = oExcel.Workbooks.Open(sWrkBk)
    Else
        Set oExcelWrkBk = oExcel.Workbooks.Open(sWrkBk, , , , sPwd)
    End If

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: OpenExcelWrkBk" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Sub CloseExcel(Optional bCleanupOnly As Boolean = True)
On Error GoTo Error_Handler
    
    If bCleanupOnly = False And bExcelOpened = False Then
        'oExcelWrSht.Close False
        'oExcel.ActiveWorkbook.Close False
        oExcel.Quit
    End If

Error_Handler_Exit:
    On Error Resume Next
    Set oExcelWrSht = Nothing
    Set oExcelWrkBk = Nothing
    Set oExcel = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: CloseExcel" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Popularity 1/10 Helpfulness 1/10 Language vb

Source: www.devhut.net

Tags: basic excel using vba

Link to this answer
Share Copy Link

Contributed on Dec 18 2022

ShowGo Shane

0 Answers Avg Quality 2/10

what are some basic framework of reusable functions for Excel (XLS) using VBA?

Contents

More Related Answers

what are some basic framework of reusable functions for Excel (XLS) using VBA?

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.