Can't Create New Sheet by "Running" Macro

[Cyberdude]

I wrote a function that will create a new worksheet and give it an optional name, which is returned as the value of the function. I have a short "calling" macro that invokes the sheet-creation macro for testing purposes.
When I invoke the calling macro while in the VBA editor, the new shet is created and renamed just as I want.
When I get out of the editor and try to Run the calling macro, then I get an error when I try to select the new worksheet because the function never creates the new sheet. This is the bare bones code I'm using:

Sub CreateWS_Caller()
MsgBox "New worksheet named '" & CreateWS("TestSht") & "' successfully created."
End Sub

Function CreateWS(Optional SheetNm) As String
Dim NewSheet As Object
If IsMissing(SheetNm) Then SheetNm = "NewSht"    'Use a default sheet name
On Error GoTo ErrMsg      '<-(Never gets triggered)
Set NewSheet = Worksheets.Add                           'Create a new empty worksheet
On Error GoTo 0
NewSheet.Name = SheetNm                                  'Change the name of the new sheet
Worksheets(SheetNm).Select     '<-(Error "Subscript out of range")
CreateWS = SheetNm                                           'Return sheet name  to caller
Exit Function
ErrMsg:
MsgBox "Error in Worksheets.Add"
End Function

So why does it run flawlessly when I execute within the VBE, but not when I use RUN when in Excel?

[Bob Phillips]

I wrote a function that will create a new worksheet and give it an optional name, which is returned as the value of the function. I have a short "calling" macro that invokes the sheet-creation macro for testing purposes.
When I invoke the calling macro while in the VBA editor, the new shet is created and renamed just as I want.
When I get out of the editor and try to Run the calling macro, then I get an error when I try to select the new worksheet because the function never creates the new sheet.

I do not see what you seem to see.

A new worksheet will always be created, it will just get the name Sheet4, 5, 6 or whatever.

In Excel, you do mean that you ran Create_Caller, not the CreateWS function don't you?

[MWE]

This seems to run fine the first time (Excel2000 and Win/XP). If CreateWS_Caller is run a second time, there will be an error when the proc attempts to rename that new sheet "TestSht" (that name already exists).

BTW, the line that tests for the optional arguement SheetNm is not really necessary if the arguement specification is changed to:

Function CreateWS( Optional SheetNm as String = "TestSht) as String

Can you be a little clearer about what is not working and what errors you are encountering?

[Richie(UK)]

Hi dude,

Your only problem would seem to be, as MWE indicated, if you use a name that has already been applied. Why not amend the error-handling to ignore any such errors and just return the default name in these cases (as xld says, Sheet n, n+1,n+2 etc etc)?

Why are you trying to Select the new sheet? Generally we should avoid trying to Select or Activate objects in order to work with them - its rarely necessary and just slows your code. The added sheet will become the ActiveSheet anyway.

How about:

Sub CreateWS_Caller()
    MsgBox "New worksheet named '" & CreateWS("An/illegal?Name") & "' successfully created."
End Sub
 
Function CreateWS(Optional SheetNm As String = "TestSht") As String
    Dim NewSheet As Worksheet
Set NewSheet = Worksheets.Add
    On Error Resume Next
    'in case the suggested name already exists or is illegal
    NewSheet.Name = SheetNm
    On Error GoTo 0
    CreateWS = NewSheet.Name
End Function

HTH

[Norie]

Why are you using a function for this?

[Bob Phillips]

Why are you using a function for this?

Why not?

[Norie]

Because aren't functions generally used to return values rather than create sheets.

[Bob Phillips]

Because aren't functions generally used to return values rather than create sheets.

Functions can be used to do anything a sub does, with the extra capability of returning a value. They don't have to return a value.

Perhaps you should be asking ... why use a sub?

[Norie]

Functions can be used to do anything a sub does, with the extra capability of returning a value. They don't have to return a value.

Are you sure?

Public Function test(rng As Range)
	rng.Offset(10, 1).Interior.Color = 3
End Function

[Bob Phillips]

Are you sure?

Public Function test(rng As Range)
    rng.Offset(10, 1).Interior.Color = 3
End Function

You are referring to worksheet functions, which in the case of a VBA function relates to where it is invoked from, not what it can do.

Type

test range("A1")

in the immediate window in the VB IDE and it works perfectly well.

Functions are a construct of Basic, not Excel. Excel has utilised them for as paricular purpose UDFS.

[Norie]

Functions are a construct of Basic, not Excel. Excel has utilised them for as paricular purpose UDFS.

What do you mean by Basic?

[johnske]

Are you sure?

Public Function test(rng As Range)
    rng.Offset(10, 1).Interior.Color = 3
End Function

Hi Norie, Functions are particularly useful when there is a lot of repetitious work to be done E.G.

Option Explicit

Sub try()
Range("A3:A7") = "X"
test Range("A3:A7")
Range("C3:C7") = "Y"
test Range("C3:C7")
Range("E3:E7") = "Z"
test Range("E3:E7")
End Sub
 
Function test(rng As Range)
rng.Offset(10, 1).Interior.ColorIndex = 3
rng.Offset(10, 1).Value = rng.Value
End Function

The alternative to this is something along these lines:

Sub try2()
Range("A3:A7") = "X"
Range("A3:A7").Offset(10, 1).Interior.ColorIndex = 3
Range("A3:A7").Offset(10, 1).Value = Range("A3:A7").Value
Range("C3:C7") = "Y"
Range("C3:C7").Offset(10, 1).Interior.ColorIndex = 3
Range("C3:C7").Offset(10, 1).Value = Range("C3:C7").Value
Range("E3:E7") = "Z"
Range("E3:E7").Offset(10, 1).Interior.ColorIndex = 3
Range("E3:E7").Offset(10, 1).Value = Range("E3:E7").Value
End Sub

Regards,
John

[Dave]

Functions are fun. I would also like to add the utility of passing paramaters and returning a boolean value is also kind of handy. You can use conditional statements to create an event without satisfying the conditions of the statement. Obscurely handy. In the following example, the conditional if statement is not satisfied (ie. no message), however the function does use the value passed to the function to insert into [sheet1!a1]. Actually, I think I very much like this. That's my 2 cents worth. HTH Dave

Private Sub Testf(x)
testnumber = 1
If test(testnumber) Then
MsgBox "This is a test"
End If
End Sub
Function test(Passnumber As Variant) As Boolean
test = False
[sheet1!a1] = Passnumber
End Function

[johnske]

What do you mean by Basic?

BASIC - (Beginner's All Purpose Symbolic Instruction Code)
By Mary Bellis


BASIC (standing for Beginner's All Purpose Symbolic Instruction Code) was written (invented) in 1963, at Dartmouth College, by mathematicians John George Kemeny and Tom Kurtzas as a teaching tool for undergraduates. BASIC has been one of the most commonly used computer programming languages, a simple computer language considered an easy step for students to learn before more powerful languages such as FORTRAN. BASIC's popularity was spread by both Paul Allen and William Gates, in 1975. Gates and Allen (both Microsoft founding fathers) wrote a version of BASIC for the Altair personal computer. It was the first product Microsoft sold. Later Gates and Microsoft wrote versions of BASIC for the Apple computer, and IBM's DOS which Gates provided came with its' version of BASIC

(http://inventors.about.com/library/i...rs/blbasic.htm)

HTH

[Bob Phillips]

What do you mean by Basic?

As in the B in VBA!

Functions are fun.

Not only fun, you could argue that all called procedures should be functions that set a return value that the caller checks before proceeding. Much better than global variables.

In the following example, the conditional if statement is not satisfied (ie. no message), however the function does use the value passed to the function to insert into [sheet1!a1].

Must admit to not really understanding the purpose of this demo, but I smiled when you said ... Actually, I think I very much like this...

[Bob Phillips]

BASIC - (Beginner's All Purpose Symbolic Instruction Code)

I think he knows that John, just unable to make the connection from what I said to same

[Bob Phillips]

Functions are fun. I would also like to add the utility of passing paramaters and returning a boolean value is also kind of handy. You can use conditional statements to create an event without satisfying the conditions of the statement. Obscurely handy. In the following example, the conditional if statement is not satisfied (ie. no message), however the function does use the value passed to the function to insert into [sheet1!a1]. Actually, I think I very much like this. That's my 2 cents worth.

Hi Dave,

This has intrigued me, mainly because I don't understand and I would like to.

In my view of the world, you use functions in one of two ways
1) to check the validity of a situation and return True or False so that the caller can then act accordingly, OR
2) to effect a certain operation, and return a return code so that the caller can then act accordingly.
In other words, they are diversions from the main logic flow, used to control the main application flow (similar to simple IF tests against builtin values, suich as
IF date = TODAY Then (pseudo code)).

Taking my view to the extreme, they are not part of the main app, but simply used to provide functionality not built-in, so should be in a separate module to the main app.

Your example seems to be neither of these, and I fail to understand its purpose as the MsgBox can never ever be displayed. But you clearly see value in the technique, so can you have another go at explaining it to me, maybe with a real world example?

[Cyberdude]

Hi, Gang. Sorry I wasn't able to insert some comments. When I tried yesterday, the input editor apparently wasn't working. The input area stayed brown, and never would turn to green (colors I see on my PC).
Lets start by saying that I reduced the code I'm using to bare bones, because that's the part that I have trouble with. Norie is concerned about why I would use a function. The purpose of the macro is to be a somewhat general utility to be used by other macros. It's a function because it returns the name of the sheet to the caller. The caller can supply a sheet name as the argument, but it's optional. If no arg is furnished, then the function supplies a default one.
One of the jobs the function does is insure that the name returned is unique. It searches for the name it might use, and if found, then a numeric suffix is appended, and another search is done to see the revised name is unique. This is a nice service for the caller, and guarantees that the returned name is indeed unique to that workbook.
Having this service in a function makes it unnecessary for me to repeat that code in each application.
xld was asking about my using the calling macro named "CreateWS":

In Excel, you do mean that you ran Create_Caller, not the CreateWS function don't you?

The answer is that for testing anything that has an argument I usually have a small routine to initiate it. I can't just press PF5, because the editor doesn't like that. I suppose I could use the RUN command, but then I can't add various argument values. So, yes, xld, I initiated the main function via the callinig routine while I'm in the editor. Perhaps there is a better way, but it suits me just fine. Once I've gotten the thing debugged pretty good, I switch to the Excel worksheet, where I have a button that will initiate the main macro that uses the function at some stage, then finishes up what it is doing.
My problem occurs when I switch from executing the function while in the editor to causing it to execute from the Excel worksheet by pushing a button that has the main macro assigned to it. As I tried to explain, the main macro can be any macro that invokes the ws creation function, including the "calling" macro I use while I'm in the editor.
To add my opinion to the Function vs. Sub argument, I almost always use a function when:
1) I want to get a value for something, and I think the function could be used elsewhere by other macros, or
2) The code in the function is sufficiently lengthy that I'd like to simplfy the main macro's code and make it easier to read.
I seldom write a macro to be executed from a worksheet formula because:
1) It tends to execute every time the sheet is recalculated, and it tends to slow thiings down if I use it in a lot of places, and
2) More often than not, I break the rule of not changing anything on the worksheet (even if I don't know how it happens) so it just gets an error, or it gets into an infinite loop somehow.

[Cyberdude]

Update: I took some of your suggestions and made some changes of my own, and ... Voila! ... it seems to be working now. Thank you all for your contributions.

[Dave]

Cyberdude I see that you have solved this problem so I hope it's okay if I respond to xld's post...

Hi Dave,

This has intrigued me, mainly because I don't understand and I would like to.

In my view of the world, you use functions in one of two ways
1) to check the validity of a situation and return True or False so that the caller can then act accordingly, OR
2) to effect a certain operation, and return a return code so that the caller can then act accordingly.
In other words, they are diversions from the main logic flow, used to control the main application flow (similar to simple IF tests against builtin values, suich as
IF date = TODAY Then (pseudo code)).

Taking my view to the extreme, they are not part of the main app, but simply used to provide functionality not built-in, so should be in a separate module to the main app.

Your example seems to be neither of these, and I fail to understand its purpose as the MsgBox can never ever be displayed. But you clearly see value in the technique, so can you have another go at explaining it to me, maybe with a real world example?

xld you have a very good understanding of functions and my late night example wasn't meant to suggest some mysterious utility of functions. The example poorly demonstrates how the use of a conditional statement in the main routine calling a function can make preparations required in order for the main routine to proceed while also reporting back to the main routine whether those changes were successful. Of course this could be coded into the main routine but the eloquence of the simple conditional statement in the main routine is what I find attractive especially if this part of the code is required more than once in the program. A useful real world example...

Function NoFileError(Flpath As String) As Boolean
'check if file exists. 
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.fileexists(Flpath) Then
NoFileError = False
Else
MsgBox "Error: This file does not exist: " & Flpath
NoFileError = True
End If
Set fs = Nothing
End Function

From the main routine...

If NoFileError(Filename) Then
' do what you want to manage the file error here
Else
' continue with the routine using the file without error
End If

This example is not very representative of my previous statements but is a real world example of a function's utility. Have a fun day! Dave