0

I'm having a brief foray in to VBA to automate some Excel workflow. I've got a function that creates a worksheet and returns it. I call it as follows:

Dim ws As Worksheet
Dim wb As Workbook

Set wb = Application.ActiveWorkbook
Set ws = makeNewWorksheet(wb)

The function is:

Function makeNewWorksheet(wb) As Worksheet
    Dim wsName As String
    Dim ws As Worksheet
    Dim newWs As Worksheet

    wsName = "Bounced " & Format(Now, "dd-mm-yyyy")
    For Each ws In wb.Worksheets
        If ws.Name = wsName Then
            Set newWs = ws
            Exit For
        End If
    Next
    If newWs Is Nothing Then
        Set ws = wb.Sheets.Add
        With ws
            .Name = wsName
            .Move after:=Sheets(Sheets.Count)
        End With
    End If
    makeNewWorksheet = ws
End Function

When I try to assign the return value of the function on the last line (makeNewWorksheet = ws), I get an error:

Object variable or With block variable not set

None of the online docs I can find seem to apply in this situation. Everything is defined and the ws variable has a value.

Edit: Re. the accepted answer below. Here's an explanation of the set keyword.

1 Answer 1

3

This:

makeNewWorksheet = ws

should be:

set makeNewWorksheet = ws
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, it's been a long time since I've used any BASIC variant. I just looked up the set keyword and linked to a relevant question above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.