Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 6th, 2006, 10:31 AM
Friend of Wrox
Join Date: Jan 2005
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default string same name as Sub

Sub TP_UKtoPortugal_RT_mapColours()

'... variables for error handling
    Const sErrorSource As String = "TP_UKtoPortugal_RT_mapColours()"
Is there a way to have the string the same name as the Sub name - without hard-coding it?


Ayrshire Minis - a Mini E-Community
Old January 6th, 2006, 12:50 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts

Unfortunatly not.

The best workaround for this type of problem that I've seen is to run a routine to "prep" all subs prior to running anything. The code below is a basic example of how to achieve this.

NB you'll need to add a reference to VBIDE (Microsoft Visual Basic for Applications Extensibility) to get this to work.

Sub InsertProcName(strModuleName As String)

Dim VBCodeMod As VBIDE.CodeModule
Dim StartLine As Long
Dim InsertLine As Long
Dim Msg As String
Dim ProcName As String
Dim test As String

    ' Initialise the code module variable
    Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(strModuleName).CodeModule

    With VBCodeMod

        ' Start on the line after the declarations at the top - they're not relevant
        StartLine = .CountOfDeclarationLines + 1

        ' Loop through all of the lines on the code pane
        Do Until StartLine >= .CountOfLines

            ' Find the procedure's name
            ProcName = .ProcOfLine(StartLine, vbext_pk_Proc)

            ' Find the first line of the procedure's line number
            StartLine = .ProcBodyLine(ProcName, vbext_pk_Proc)

            ' Constant declaration line to insert
            Msg = "Const sErrorSource As String = """ & ProcName & """"

            ' Position to insert Constant declaration line
            InsertLine = StartLine + ProcHeaderLen(VBCodeMod, ProcName)

            ' Insert constant declaration line if not already there
            If .Lines(InsertLine, 1) <> Msg Then .InsertLines InsertLine, Msg

            ' Move down to the next procedure
            StartLine = StartLine + .ProcCountLines(ProcName, vbext_pk_Proc)


    End With

End Sub

Private Function ProcHeaderLen(CodeMod As VBIDE.CodeModule, ProcName As String) As Long

Dim Counter As Long
Dim LineNum As Long
Dim C As String

    ' Find the first header line
    LineNum = CodeMod.ProcBodyLine(ProcName, vbext_pk_Proc)

    ' Find the character on the RH end of this line
    C = Right(CodeMod.Lines(LineNum, 1), 1)

    ' Whilst the RH character is "_" the header is being continued so loop down until it is not
    Do While C = "_"
        Counter = Counter + 1
        C = Right(CodeMod.Lines(LineNum + Counter, 1), 1)

    ' Add one for the first line
    Counter = Counter + 1

    ' Output the result
    ProcHeaderLen = Counter

End Function

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to find a string in another string in vb6 satish_k VB How-To 3 March 30th, 2007 12:17 PM
Casting String array to string Samatha ASP.NET 1.0 and 1.1 Professional 1 December 5th, 2006 07:46 AM
syntax to find a string in a string cole SQL Server 2000 2 October 10th, 2005 06:06 PM
how to replace a string with another string/number crmpicco Javascript How-To 4 March 14th, 2005 12:59 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.