Parameter transmission from Excel into Macro

Question

I am making a Macro in VBA (Excel 2016 32bit + Windows 10 Pro) that fires when Workbook.NewSheet event occurs. This Macro must rename Sheet.CodeName property of every new sheet.

The problem is that when I run my Macro in breaking mode, step by step, it works perfectly. But it can’t rename Sheet.CodeName property after VB Editor (VBE) is closed. Simply Sheet.CodeName property is empty, so Macro doesn’t know what sheet must have to renamed. If I open VBE and, doing nothing, close it again, Macro runs rightly. And next, this Macro works correctly with Workbook.Open and Workbook.SheetChange events. Only Workbook.NewSheet event gives a trouble.

Give me a suggest, please, if you know, how I can improve parameter transmit from Excel to my Macro behind closed VBE.

Hi!

Thank You for want to helping me! The code is very easy. The goal is - get properly value of sh_CodeName property in message window when VBE is closed.

Current code:

Private Sub Workbook_NewSheet(ByVal sh As Object)
NewSheet sh
End Sub

Option Explicit
Sub NewSheet(ByVal sh As Object)
Dim sh_CodeName As String, sh_Name As String, i As Integer

sh_CodeName = sh.CodeName: sh_Name = sh.Name

MsgBox "Code Name - " & sh_CodeName & vbCrLf & "Name - " & sh_Name, vbOKOnly, "MESSAGE FROM WorkBook.NewSheet"

End Sub

Help us to help you; **post your current code.** – Gary's Student Apr 25 '20 at 11:06 — Gary's Student, Apr 25 '20 at 11:06

Storax · Accepted Answer · 2020-04-25T14:43:04.157

It seems that sh.codename is not available after adding a new sheet as long as one has not accessed the VBA project. Maybe someone else hase more on this.

This code works for me but has the disadvantage that you need to trust access to the VBA project object model.

Sub NewSheet(ByVal Sh As Object)
    Dim sh_CodeName As String, sh_Name As String, i As Integer


    sh_CodeName = ActiveWorkbook.VBProject.VBComponents(Sh.Name).Properties("_Codename")
    sh_Name = Sh.Name

    MsgBox "Code Name - " & sh_CodeName & vbCrLf & "Name - " & sh_Name, vbOKOnly, "MESSAGE FROM WorkBook.NewSheet"

End Sub

Even this simple macro will not give a codename when run with closed VBE

Sub TestAddIt()

Dim sh As Worksheet

    Set sh = ActiveWorkbook.Worksheets.Add
    MsgBox sh.Name & " - " & sh.CodeName

End Sub

PS: Another workaround could be to open the VBE and close it again but you still need access to the VBA project

Sub TestAddIt()

Dim Sh As Worksheet

    Set Sh = ActiveWorkbook.Worksheets.Add

    With Application.VBE.MainWindow
        .WindowState = 1
        .Visible = True
        .Visible = False
    End With

    MsgBox Sh.Name & " - " & Sh.CodeName

End Sub

Update: This also worked for me.

Sub TestAddIt()

Dim Sh As Worksheet

    Set Sh = ActiveWorkbook.Worksheets.Add
    ' Recompile the project
    Application.VBE.CommandBars.FindControl(ID:=578).Execute

    MsgBox Sh.Name & " - " & Sh.CodeName

End Sub

Why I can't add the name of author of the answers? My posts published with out this string "Dear Storax!". My thanks to Storax! — Levi.S, Apr 25 '20 at 14:37
It is very interesting way. I shall try it out any later, must to do another job. Thank you very much! — Levi.S, Apr 25 '20 at 15:27

score 0 · Answer 2 · answered Apr 25 '20 at 14:29

You help me with this statement.

sh_CodeName = ActiveWorkbook.VBProject.VBComponents(Sh.Name).Properties("_Codename")

I modified it slightly (Sh.CodeName instead of Sh.Name). And added a delay.

Sub Check_NewSheet(ByVal sh As Object)
    Dim sh_CodeName As String, sh_Name As String, i As Integer

On Error Resume Next
    sh_CodeName = ThisWorkbook.VBProject.VBComponents(sh.CodeName).Properties("_Codename")
If Err.number > 0 Then
i = 0
Do While sh_CodeName = ""
    sh_CodeName = ThisWorkbook.VBProject.VBComponents(sh.CodeName).Properties("_Codename")
DoEvents
i = i + 1
Loop
End If
sh_Name = sh.Name

    MsgBox "Code Name - " & sh_CodeName & vbCrLf & "Name - " & sh_Name & vbCrLf & _
    "Attempts - " & i, vbOKOnly, "MESSAGE FROM WorkBook.NewSheet"

End Sub

It works pretty good now. It is quite well as an interim solution. But I still interested to find out an explanation what is wrong in the Excel to Macro communication. So, if you will find one, please, share it with me.

Possibly there is nothing wrong. Maybe that's just the way it is. As said it seems as long as you have not accessed the VBA project after you have added a sheet programmatically the codename is not available. Maybe a kind of re-complie is needed and that is done as soon as you access the VBA project. BUT I am guessing :-( — Storax, Apr 25 '20 at 14:37
Updated my above solution with another approach which might save you the loop. — Storax, Apr 25 '20 at 14:44

Parameter transmission from Excel into Macro

2 Answers2