Excel vba: object variable or with block variable not set error when returning Object from function

Question

I want to use a function that should return an Excel.Application Object:

 Dim ExcelApp As Object
 Set ExcelApp = getExcelApp()



Function getExcelApp() As Object
        Dim ExcelApp As Object
        Set ExcelApp = CreateObject("Excel.Application")
        ExcelApp.Visible = False
        ExcelApp.ScreenUpdating = False
        ExcelApp.DisplayAlerts = False
        ExcelApp.EnableEvents = False

        getExcelApp = ExcelApp
End Function

But I get a object variable or with block variable not set error. What is wrong and how to accomplish what I want?

Wolfie · Accepted Answer · 2017-08-23 10:14:03Z

3

The error is because you're missing the Set command (which is needed because you're assigning an object).

But you're creating one object, setting it up, then assigning it to a different object for returning. You could instead assign everything directly to the return variable.
A more concise function would be:

Function getExcelApp() As Object
    Set getExcelApp = CreateObject("Excel.Application")
    With getExcelApp
        .Visible = False
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
   End With
End Function

answered Aug 23, 2017 at 10:14

Wolfie

30.7k7 gold badges30 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nathan_Sav · Accepted Answer · 2017-08-23 14:30:07Z

3

Set for objects, so set getExcelApp = ExcelApp

Also, If you are calling this from Excel, then you'll already have the correct class library available, so I'd use

Function Get_Excel() As Excel.Application

Set Get_Excel = New Excel.Application
Get_Excel.Visible = True
'     etc, etc
End Function

edited Aug 23, 2017 at 14:30

answered Aug 23, 2017 at 10:09

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Comments

Vityata · Accepted Answer · 2017-08-23 10:09:40Z

2

Try like this:

Option Explicit

Public Sub TestMe()

    Dim ExcelApp As Object
    Set ExcelApp = getExcelApp()
    Debug.Print ExcelApp.Name

End Sub

Function getExcelApp() As Object

    Dim ExcelApp As Object
    Set ExcelApp = CreateObject("Excel.Application")
    ExcelApp.Visible = True
    ExcelApp.ScreenUpdating = False
    ExcelApp.DisplayAlerts = False
    ExcelApp.EnableEvents = False

    Set getExcelApp = ExcelApp

End Function

It would print the name of the new ExcelApp. In this case Microsoft Excel. I have also changed ExcelApp.Visible to True and the function, returning an object should be with a Set.

answered Aug 23, 2017 at 10:09

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Collectives™ on Stack Overflow

Excel vba: object variable or with block variable not set error when returning Object from function

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related