Using Sheets.Activate method within a VBA function doesn't work. Why?

Question

The function Sheets.activate doesn't work within a VBA function call. What am I doing wrong?

Answer 1

I want to be able to create a help sheet using a function call, but Excel vba ignores the sheets.add and sheets.delete methods. Using as stand ins the .activate and .select methods also fails, yet they work ok for VBA subs. Here is my codes for both methods:

Public Function TestBuildHelpSheet(eInput As String) As Double
Stop
Dim ws As Variant
For Each ws In Sheets
If ws.Name = "Help Sheet" Then ws.Activate ' doesn't work
Next
End Function

Sub RemoveHelpSheet()
Stop
'
' This works fine, changing ws.activate to ws.delete also works;
' It deletes the help sheet. Also tried ws.Select, same result
'
Dim ws As Variant
For Each ws In Sheets
If ws.Name = "Help Sheet" Then ws.Activate ' works
Next ws
End Sub

The eInput is just something for the function to receive to get going, and the stops are to allow the switchover to F8 stepping.

Why does this work with Subs, and not with functions? I also tried having the function call the sub, but that didn't work either.

Answer 2

The function has to be called by somebody. My guess is that whoever is calling the function isn't right. Please post the code where you are actually calling the function that tries to activate the help sheet.

Answer 3

I'm not clear why you would want to implement this as a Function. Functions usually process some data and return a value. That's not the case with your example. Just use a Sub instead.

Answer 4

Thank you for answering. What I gave as a function is stripped down coding of a function that looks something like this inside the function:

TestBuildHelpSheet = 0 (Default return)
Select Case eInput
Case "Apple"
TestBuildHelpSheet = 1
Case "Pear"
TestBuildHelpSheet = 2
Case Else
If vbYes = MsgBox("May I replace the " & HelpSheetName & "?", vbYesNo) Then
ws.delete
endif
' goes on to insert an updated help sheet since the eInput string was invalid and
' the user won't have any idea what choices there may be unless I give them a table
' they can reference.
end select

I left out the message box since that is not causing the problem. I can't use a sub, a user won't have access when using the function in their spreadsheet. Obviously I do have to make sure duplicate help sheets don't occur and not wipe out any notes an end user may have put on previous editions of a help sheet, which is why there has to be some message boxes in the coding. Additionally, the help sheet can stay with the excel file and be updated by the user by either forcing an error or entering the string "help" when using the function (I don't show that code above).

My question is, why doesn't it select/activate the sheet so I can provide decent help to an end user?

Answer 5

There aren't enough years left in the universe to answer all the why questions about Microsoft Office. I would focus on solving the problem. Create a sub that activates the worksheet, call the sub from the function. https://www.exceldemy.com/excel-vba-call-sub/