Sunday, October 14, 2012

Web App Restarting Unexpectedly


What happened?


This was an ASP.NET application. It used forms authentication. The user logs in, navigates to a number of pages here and there and suddenly upon hitting few particular pages, the web app would restart. I knew this because 1) the user lost session 2) logs that are typically collected at Application_Start.

This was rather a puzzling and mysterious behavior of an app that's worked with no such issue for many months.

The Cause:


There was a piece of code in those few pages that wrote to a config file (a file whose extension is .config but not web.config). This was IIS6. This change to this config file triggered the AppPool to recycle and thus the application to restart.

The fix is obviously simple: don't write to the config file.

Saturday, April 21, 2012

Another log4net HowTo


Intro and Configuration
Log4net is a sophisticated logging library for .NET applications (web or otherwise). It’s not however cumbersome to use, neither it’s intrusive I believe. I will try in this post to illustrate what I think is good configuration for this library. A configuration that lets a .NET application uses but not have a hard dependency on it.
You start by downloading the library here http://logging.apache.org/log4net/. There you’ll also find good examples and documentation. Add a reference to your .NET app.
Now there are a number of ways you can configure the library, what to log (date & time stamp, thread #, custom message, exceptions etc) and where to log (a database, a file or the standard output {console}). A good way to configure the library is tell your application and the assembly level where the configuration file for log4net (typically an XML file) lives. You do so by adding the below line to your AssemblyInfo.vb or (AsssemblyInfo.cs) class
<Assembly: log4net.Config.XmlConfigurator(ConfigFile:="MyAppLog4net.config", Watch:=True)>

The configFile can a full-path to your log4net configuration file. The watch flag if set true tells log4net to reload the configuration at runtime if the configuration file was changed.
Now technically you can start logging. Given the simple config file below
<?xml version="1.0" encoding="utf-8" ?>
<log4net>
  <appender name="RollingFile" type="log4net.Appender.RollingFileAppender">
    <file value="myLog.log" />
    <appendToFile value="true" />
    <maximumFileSize value="100KB" />
    <maxSizeRollBackups value="2" />

    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern value="%date %level %thread %logger - %message%newline" />
    </layout>
  </appender>

  <root>
    <!--<level value="DEBUG" />-->
    <appender-ref ref="RollingFile" />
  </root>
</log4net>
With this log file we can log a visit to web page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    log4net.LogManager.GetLogger(Me.GetType().Name).Info(Me.Request.Path + " visited.")
End Sub
A sample output: 2012-04-21 09:21:33,970 INFO 21 default_aspx - /Default.aspx visited.
A better approach (create your own wrapper):
Instead of invoking log4net directly from your business logic layer, data layer, etc. a better approach is to create wrapper logging layer. The benefit is that you decouple your application from a specific logging implementation.
One way to do this is to create an interface that defines your logging operations. A simple interface can look like the below.
Public Interface ICustomLog
    Sub Debug(ByVal source As Type, ByVal message As String, ByVal exception As Exception, ByVal customObject As CustomLogProperties)
    Sub Info(ByVal source As Type, ByVal message As String, ByVal exception As Exception, ByVal customObject As CustomLogProperties)
    Sub Warn(ByVal source As Type, ByVal message As String, ByVal exception As Exception, ByVal customObject As CustomLogProperties)
    Sub [Error](ByVal source As Type, ByVal message As String, ByVal exception As Exception, ByVal customObject As CustomLogProperties)
    Sub Fatal(ByVal source As Type, ByVal message As String, ByVal exception As Exception, ByVal customObject As CustomLogProperties)
End Interface

Any type of logging implementation will then have to adhere to the defined interface. The parameters passed to various logging methods will apparent later, hopefully.
Here is an implementation that will rely fundamentally on log4net. But before showing the concrete implementation, let’s talk about custom properties that you can tell log4net to log for you.
Logging Custom Properties using log4net [.Core.LoggingEvent]
By default log4net will log a number of properties (thread number, date of event, custom message etc.). If you wish to add properties that are specific to your application, log4net will let you do that through a number of ways. One of the ways that you can load a log4net logger object with custom properties is through using the LoggingEvent class. The sample code will illustrate how. One of the common issues one runs to when using custom properties is that even it appears the code is using them correctly, they don’t show in the log file/ log table etc. This is almost always due to the fact that configuration file is not written correctly to tell log4net how to render or from where to get the custom properties; so special care needs to be given to how the configuration is written.
For example, let’s assume that one of the requirements of your is logging three custom properties: the SessionID {asp.net SessionID}, the raw request’s URL, and an application name. Putting these three variables in a custom object is a good idea (CustomLogProperties in sample). Every time we need to log an event with then create an instance of CustomLogProperites and pass to our logger who takes care of creating a LoggingEvent populated with the custom properties.
Private Sub SetCustomLogProperties(ByVal loggingEvent As log4net.Core.LoggingEvent, ByVal customLogObject As CustomLogProperties)
        If customLogObject Is Nothing OrElse loggingEvent Is Nothing Then
            Return
        End If
        loggingEvent.Properties(CustomFileLogger.ApplicationNameKey) = customLogObject.ApplicationName
        loggingEvent.Properties(CustomFileLogger.RequestKey) = customLogObject.RequestPath
        loggingEvent.Properties(CustomFileLogger.ASPSesssionIDKey) = customLogObject.SessionID
    End Sub
    Private Function GetLoggingEvent(ByVal source As Type, ByVal logger As log4net.ILog, ByVal message As String, ByVal exception As Exception _
                                     , ByVal level As log4net.Core.Level, ByVal customLogObject As Object) As log4net.Core.LoggingEvent
        If source Is Nothing OrElse logger Is Nothing Then
            Return Nothing
        End If
        Dim loggingEvent As New log4net.Core.LoggingEvent(source, logger.Logger.Repository, logger.Logger.Name, level, message, exception)
        SetCustomLogProperties(loggingEvent, customLogObject)
        Return loggingEvent
    End Function
    Public Sub Debug(ByVal source As Type, ByVal message As String, ByVal exception As Exception, _
                     ByVal customLogObject As CustomLogProperties) Implements ICustomLog.Debug
        Dim logger As log4net.ILog = GetLogger(source)
        Dim loggingEvent = GetLoggingEvent(source, logger, message, exception, log4net.Core.Level.Debug, customLogObject)
        If loggingEvent IsNot Nothing Then
            logger.Debug(message)
        End If
    End Sub
Here is the relevant portion of the log configuration file which will instruct the log4net library on how to write the custom properties
<layout type="log4net.Layout.PatternLayout">
      <conversionPattern value="%date %level %thread %logger - %message %exception [%property{ApplicationName}]
                         [%property{RequestPath}] [%property{SessionID}] %newline" />
    </layout>
Notice the use of %property{property_name} syntax.
Code Listings
Configuration 

    
 <?xml version="1.0" encoding="utf-8" ?>
<log4net>
  <appender name="RollingFile" type="log4net.Appender.RollingFileAppender">
    <file value="myLog.log" />
    <appendToFile value="true" />
    <maximumFileSize value="100KB" />
    <maxSizeRollBackups value="2" />

    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern value="%date %level %thread %logger - %message %exception [%property{ApplicationName}]
                         [%property{RequestPath}] [%property{SessionID}] %newline" />
    </layout>
  </appender>

  <root>
    <!--<level value="DEBUG" />-->
    <appender-ref ref="RollingFile" />
  </root>
</log4net> 



Sample Code

 

Partial Public Class _Default
    Inherits System.Web.UI.Page
    Private logger As ICustomLog = CustomFileLogger.GetInstance()
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'log4net.LogManager.GetLogger(Me.GetType().Name).Info(Me.Request.Path + " visited.")
        logger.Info(Me.GetType(), "Page visitied", Nothing, New CustomLogProperties() With {.ApplicationName = "MyApp", .RequestPath = Request.RawUrl, .SessionID = Me.Session.SessionID})
        CauseAndLogException()
    End Sub
    Private Sub CauseAndLogException()
        Dim str = "4/31/2012"
        Try
            DateTime.Parse(str)
        Catch ex As Exception
            logger.Error(Me.GetType(), "An error occurred in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex, New CustomLogProperties With _
                         {.ApplicationName = "MyApp", .RequestPath = Request.RawUrl, .SessionID = Me.Session.SessionID})
        End Try
    End Sub
End Class

Public Interface ICustomLog
    Sub Debug(ByVal source As Type, ByVal message As String, ByVal exception As Exception, ByVal customObject As CustomLogProperties)
    Sub Info(ByVal source As Type, ByVal message As String, ByVal exception As Exception, ByVal customObject As CustomLogProperties)
    Sub Warn(ByVal source As Type, ByVal message As String, ByVal exception As Exception, ByVal customObject As CustomLogProperties)
    Sub [Error](ByVal source As Type, ByVal message As String, ByVal exception As Exception, ByVal customObject As CustomLogProperties)
    Sub Fatal(ByVal source As Type, ByVal message As String, ByVal exception As Exception, ByVal customObject As CustomLogProperties)
End Interface
Public Class CustomFileLogger
    Implements ICustomLog
    Private Shared _loggers As Dictionary(Of Type, log4net.ILog) = New Dictionary(Of Type, log4net.ILog)
    Private Shared _lock = New Object()
    Private Shared _instance As ICustomLog = Nothing
    Private Sub New()
    End Sub
    Public Shared Function GetInstance() As ICustomLog
        If _instance Is Nothing Then
            _instance = New CustomFileLogger()
        End If
        Return _instance
    End Function
    Private Function GetLogger(ByVal source As Type) As log4net.ILog
        SyncLock _lock
            If CustomFileLogger._loggers.ContainsKey(source) Then
                Return CustomFileLogger._loggers(source)
            Else
                Dim logger As log4net.ILog = log4net.LogManager.GetLogger(source)
                CustomFileLogger._loggers.Add(source, logger)
                Return logger
            End If
        End SyncLock
    End Function
    Private Sub SetCustomLogProperties(ByVal loggingEvent As log4net.Core.LoggingEvent, ByVal customLogObject As CustomLogProperties)
        If customLogObject Is Nothing OrElse loggingEvent Is Nothing Then
            Return
        End If
        loggingEvent.Properties(CustomFileLogger.ApplicationNameKey) = customLogObject.ApplicationName
        loggingEvent.Properties(CustomFileLogger.RequestKey) = customLogObject.RequestPath
        loggingEvent.Properties(CustomFileLogger.ASPSesssionIDKey) = customLogObject.SessionID
    End Sub
    Private Function GetLoggingEvent(ByVal source As Type, ByVal logger As log4net.ILog, ByVal message As String, ByVal exception As Exception _
                                     , ByVal level As log4net.Core.Level, ByVal customLogObject As Object) As log4net.Core.LoggingEvent
        If source Is Nothing OrElse logger Is Nothing Then
            Return Nothing
        End If
        Dim loggingEvent As New log4net.Core.LoggingEvent(source, logger.Logger.Repository, logger.Logger.Name, level, message, exception)
        SetCustomLogProperties(loggingEvent, customLogObject)
        Return loggingEvent
    End Function
    Public Sub Debug(ByVal source As Type, ByVal message As String, ByVal exception As Exception, _
                     ByVal customLogObject As CustomLogProperties) Implements ICustomLog.Debug
        Dim logger As log4net.ILog = GetLogger(source)
        Dim loggingEvent = GetLoggingEvent(source, logger, message, exception, log4net.Core.Level.Debug, customLogObject)
        If loggingEvent IsNot Nothing Then
            logger.Debug(message)
        End If
    End Sub
    Public Sub Info(ByVal source As Type, ByVal message As String, ByVal exception As Exception, ByVal customLogObject As CustomLogProperties) Implements ICustomLog.Info
        Dim logger As log4net.ILog = GetLogger(source)
        Dim loggingEvent = GetLoggingEvent(source, logger, message, exception, log4net.Core.Level.Info, customLogObject)
        If loggingEvent IsNot Nothing Then
            logger.Logger.Log(loggingEvent)
        End If
    End Sub
    Public Sub Warn(ByVal source As Type, ByVal message As String, ByVal exception As Exception, ByVal customLogObject As CustomLogProperties) Implements ICustomLog.Warn
        Dim logger As log4net.ILog = GetLogger(source)
        Dim loggingEvent = GetLoggingEvent(source, logger, message, exception, log4net.Core.Level.Warn, customLogObject)
        If loggingEvent IsNot Nothing Then
            logger.Logger.Log(loggingEvent)
        End If
    End Sub
    Public Sub [Error](ByVal source As Type, ByVal message As String, ByVal exception As Exception, ByVal customLogObject As CustomLogProperties) Implements ICustomLog.Error
        Dim logger As log4net.ILog = GetLogger(source)
        Dim loggingEvent = GetLoggingEvent(source, logger, message, exception, log4net.Core.Level.Error, customLogObject)
        If loggingEvent IsNot Nothing Then
            logger.Logger.Log(loggingEvent)
        End If
    End Sub
    Public Sub Fatal(ByVal source As Type, ByVal message As String, ByVal exception As Exception, ByVal customLogObject As CustomLogProperties) Implements ICustomLog.Fatal
        Dim logger As log4net.ILog = GetLogger(source)
        Dim loggingEvent = GetLoggingEvent(source, logger, message, exception, log4net.Core.Level.Fatal, customLogObject)
        If loggingEvent IsNot Nothing Then
            logger.Logger.Log(loggingEvent)
        End If
    End Sub
    Private Shared Sub FlushLog()
        For Each log In _loggers
            Dim rep As log4net.Repository.ILoggerRepository
            rep = log.Value
            For Each appender As log4net.Appender.BufferingAppenderSkeleton In rep.GetAppenders()
                appender.Flush()
            Next
        Next
    End Sub
    Private Shared ReadOnly ApplicationNameKey As String = "ApplicationName"
    Private Shared ReadOnly RequestKey As String = "RequestPath"
    Private Shared ReadOnly ASPSesssionIDKey As String = "SessionID"
End Class
Public Class CustomLogProperties
    Public ApplicationName As String
    Public RequestPath As String
    Public SessionID As String
End Class

Useful Links:
http://logging.apache.org/log4net/
http://haacked.com/archive/2006/09/27/Log4Net_Troubleshooting.aspx

Monday, January 2, 2012

Finland Education System

Here http://www.theatlantic.com/national/archive/2011/12/what-americans-keep-ignoring-about-finlands-school-success/250564/#.Tv4NA-e7HkY.mailto

The article discusses that the main reason that led the Finnland education system reform was the inequality which was present before at some point of time before the reform itself took place. Therefore, the reform sought to achieve equality across the education system (pre-K to University). There are no private schools. Every student has access to free public education + what comes with it (health care etc.)

Since the 1980s, the main driver of Finnish education policy has been the idea that every child should have exactly the same opportunity to learn, regardless of family background, income, or geographic location. Education has been seen first and foremost not as a way to produce star performers, but as an instrument to even out social inequality.
The article highlights that co-operation is the norm the Finnish education system and that "nothing makes Finn more uncomfortable" than the idea of competition in the context of education.

The article also mentions the high expectations and equally high rewards of educators in Finland:

For Sahlberg what matters is that in Finland all teachers and administrators are given prestige, decent pay, and a lot of responsibility. A master's degree is required to enter the profession, and teacher training programs are among the most selective professional schools in the country. If a teacher is bad, it is the principal's responsibility to notice and deal with it.
Overall, a very interesting read.

Thursday, December 15, 2011

Using Entity Framework-based Repository Pattern in MVC Causes Errors

Using Entity Framework 4.0 in an MVC 3.0 application using the repository pattern where the data model is created in a separate project can result in compilation errors generated when the view are requested. Basically the views fail to compile properly if the view's model is an IEnumerable type of an entity object.

The compiler error message is not very helpful (though it gives hints)

Compiler Error Message: BC30456: 'Title' is not a member of 'ASP.views_

This indicates that the View object failed to compile all together and this only happens when the Entity Framework generated objects are involved in the model of the View.


Line 1:  <%@ Page Title="" Language="VB" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage(Of IEnumerable (Of WebApp.Domain.MyModelObject))" %>


One way to force the compiler to give a better message is to have the view accept a generic object and then later in the view code cast the object to what it really is (in this case an IEnumerable (Of WebApp.Domain.MyModelObject)

So the new Page directive looks like

<%@ Page Title="" Language="VB" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage(Of Object)" %>

And now instead of using Model right away, we have to cast it to its actual type

<% Dim modelData = CType(Me.Model , IEnumerable(Of WebApp.Domain.MyModelObject)) %>


Doing the above changes causes the compiler to give a better message because it's now able to compile the view but at run-time it finds that there are missing assemblies (the message below explains it)

Compiler Error Message: BC30007: Reference required to assembly 'System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' containing the base class 'System.Data.Objects.DataClasses.EntityObject'. Add one to your project.

And the guilty line that caused the error as you might have expected is

<% Dim modelData = CType(Me.Model , IEnumerable(Of WebApp.Domain.MyModelObject ))  %>

And now it's obvious that we need to add the references to our web.config (system.web -> compilation -> assemblies)
<add assembly ="System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>

There are other reasons why this error might come up which may not have to do with Entity Framework
look here for more http://stackoverflow.com/questions/1545538/asp-net-error-bc30456-title-is-not-a-member-of-asp-views-controllername-view



Wednesday, August 3, 2011

Sequential Numbering of Group of Events with T-SQL

An example to illustrate the title is winning and losing streaks in a football game. Suppose we have the below set of data about a number of a series games.


mnummatch_dateresult
107/01/2011L[oss]
2 07/02/2011L
3 07/03/2011 W[in]
4 07/04/2011W
507/05/2011 L
607/06/2011W
707/07/2011 W

The goal is to sequentially number each losing or winning streak. So for the above example, the number would look like the below:
mnummatch_dateresult streak
107/01/2011L -1
2 07/02/2011L -2
3 07/03/2011 W 1
4 07/04/2011W 2
507/05/2011 L -1
607/06/2011W 1
707/07/2011 W 2
We chose to number the two events in two different directions (negative for losses and positive for wins) . Below is an approach I took a while ago (and needed recently) using T-SQL to solve this problem:

First let's create a temporary table (a table variable) that adds the streak column. To do this we basically self-join the table so that each game is compared to the following game. The streak is then 1 if two consecutive games have different results (W/L or L/W). If, however, two consecutive games have the same result we use row_number() ranking function to denote the streak number. We do this operation once for the losses and once for the wins as the code illustrates below

declare @temp_results table(row_num int,match_date datetime,result char(1),streak int);
insert into @temp_results (row_num,match_date,result,streak)
select row_number() over (order by match_date) as row_num, match_date,result,streak
from(
select m1.match_date,m1.result
,(case when m1.result = m2.result then row_number() over(order by m1.match_date) else 1 end)
as streak
from @matches m1 left join @matches m2
on m1.mnum = m2.mnum+1
where m1.result= 'W'
UNION ALL
select m1.match_date,m1.result
,(case when m1.result = m2.result then -1 * (row_number() over(order by m1.match_date)) else -1 end )
as streak
from @matches m1 left join @matches m2
on m1.mnum = m2.mnum+1
where m1.result= 'L'
) as chld
The above code is not enough because row_number will not generate sequential streaks. In fact the data so far looks like the table below

mnummatch_dateresult streak
107/01/2011L -1
2 07/02/2011L -2
3 07/03/2011 W 1
4 07/04/2011W 2
507/05/2011 L -1
607/06/2011W 1
707/07/2011 W 4

The second step is to get rid of the gaps between sequence numbers. To do this we update the table (the table variable) so that for each streak if it's not 1 or -1 we find the last game (max) that has the same result as the current place but it took place before the current game and then subtract that game's row_number for current row_number. Code explains it better:
-- we need this update as a fix to get rid of the gaps between successive wins or successive losses
update t1
set t1.streak =
(case when t1.result = 'W' then 1+t1.row_num - (select max(row_num)
from @temp_results t
where t.streak = 1
and t.row_num < t1.row_num ) else -1 * (1+t1.row_num - (select max(row_num) from @temp_results t where t.streak = -1 and t.row_num < t1.row_num )) end) from @temp_results t1 --left join @temp_results t2 on t1.row_num = t2.row_num where t1.streak not in (1,-1)

Thursday, July 7, 2011

Report Rendering Fails in Local Mode

A desktop program developed using .NET 3.5 framework and was using RDLC report in local mode and a ReportViewer control  was throwing what might seem a weird exception the exception looked something like the below

The definition of the report 'Main Report' is invalid. 
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: 
An unexpected error occurred in Report Processing. 
System.UnauthorizedAccessException: Access to the path 'C:\Documents and Settings\\Local Settings\Temp\expression_host_05cd5e1af4624646bc2dd846b60460f0.dll' is denied.

What is this expression_host_....dll file and Why is The Report Rendering Engine Trying to Access It?

When you write an expression in your report definition (any expression even something that looks like =Fields!FirstName.Value + " " + Fields!LastName.Value) or when you use Custom Code, the report rendering engine does two things; it seems to me. One the rendering engine extracts all custom expressions and custom code and, based on these custom expressions and code, it dynamically creates VB.NET classes which are then compiled on the fly to a .NET assembly. These files and the resulting assembly are placed in the %TEMP% directory; they are deleted once they are no longer needed by the current instance of the running report. The second thing the rendering engine does is referencing the dynamically created assembly (which is named expression_host_.dll) when rending your report and making the necessary calls to this assembly as dictated by your report definition. 

How is The Above Explanation Related to The Error In Question?


Some anti-malware/anti-virus software do not like programs creating dynamic DLLs, it seems. In my case I verified that the dynamically created assembly was indeed blocked by the anti-malware program so that the ReportViewer and the hosting application could not use it and the even rendering engine program could not delete the expression_host dll file; which it normally does once the report is done rendering.

As soon as stopped the anti-malware program or just told to trust certain pattern of files, the report rendered perfectly without a problem.

Finally, it's worth mentioning that this problem was sporadic and had no predictable pattern. That is, one out 20 trials  the report would render fine but most of the time it fails. Something which leads to believe that there was a "race" between the report rendering engine and its application and the anti-malware program. Sometimes the anti-malware program would miss the host_expression dll and the report would render fine but most of the time the dll files is blocked.

The Bottom Line:


If you're running a program that use a RLDC report in local mode and the report uses expressions and custom code, rendering the report could fail due to an anti-virus/ anti-malware program blocking the custom assembly created to host your expressions and/or custom code.


Tuesday, May 31, 2011

Renaming Excel Sheets Exported from Reporting Services 2005 via an Excel Add-In


Intro

When a Reporting Services 2005 report with multiple pages is exported to excel to creates an excel document with multiple sheets where the first sheet is an index sheet or document map and the remaining sheets are report pages. Reporting Services 2005 gives these sheets generic names Sheetxy. Unlike the situation with Reporting Services 2008 [R2] there's no straight forward way of telling Reporting Services 2005 how name the report pages. One way to rename this sheets in excel to match the entries in the document map (which are more meaningful than sheetXY) is through an excel add-in. Below is an Excel add-in written in VB to rename sheets based on the document map. The code is tested on Windows XP/7 with Office 2003 Professional/Basic. If you decide to use the code below, make sure it has no unwanted side-effects in your environment and make sure you test it.

The basic idea of the code is:


  • Each document map entry points to a named range (which is stored in the built-in array Names
  • Using the range name we can find out the sheet that's being pointed to by an entry in the document map
  • Once the sheet is found, its name is changed to that of the hyperlink's DisplayText property of the pointing document map entry.

Steps to Create Excel Add-In using VBA

  1. Open a new excel workbook and go to the Visual Basic Editor
  2. In the left pane, right-click on VBAProjects (Book1) and Insert -> module. This module will have the code that will rename the sheets. The code in this module will be called when the custom menu item is clicked.
  3. This code does the renaming (you can paste in the newly created module
    Private Const INVALID_CHARS As String = "[]*/\?:"
    Private Const SHEET_NAME_MAX_LENGTH As Integer = 31
    Private Const DOCMAP_SHEET_NAME As String = "document map"
    Private Const DOCMAP_SHEET_NEW_NAME As String = "__INDEX__"
    Public Sub RunRenameSheetsCode()
        If Not Application.ActiveWorkbook Is Nothing Then
            Call RenameSheets(Application.ActiveWorkbook)
        End If
    End Sub
    Public Sub RenameSheets(ByRef wb As Workbook)
        On Error GoTo Error
        Dim sh As Worksheet
        Dim row As Integer
        Dim cell As Range
        Dim newName As String
        row = 1
        If Not IsFirstSheetDocMap(wb) Then
            Exit Sub
        End If
        Do While True
            Set cell = wb.Sheets(1).Range("$A$" & CStr(row))
            If Len(cell.Value) <= 0 Then
                Exit Do
            End If
            If cell.Hyperlinks.Count > 0 Then
                Set sh = GetSheetByRangeName(wb, cell.Hyperlinks(1).SubAddress)
                If Not sh Is Nothing Then
                    newName = CleanSheetName(cell.Value)
                    sh.name = newName
                End If
            End If
            row = row + 1
        Loop
        wb.Worksheets(1).name = DOCMAP_SHEET_NEW_NAME
        Exit Sub
    Error:
         MsgBox ("An error occurred while trying to rename sheets")
    End Sub
    Public Function CleanSheetName(ByVal name As String)
        Dim newName As String
        newName = name
        For i = 1 To Len(INVALID_CHARS)
           newName = Replace(newName, Mid(INVALID_CHARS, i, 1), "")
        Next
        newName = Left(newName, SHEET_NAME_MAX_LENGTH)
        CleanSheetName = newName
    End Function
    Public Function GetSheetByRangeName(ByRef wb As Workbook, rangeName As String) As Worksheet
        Dim i As Integer
        Dim sheetName As String
        Dim charIndex As Integer
        For i = 1 To wb.Names.Count
        If wb.Names(i).name = rangeName Then
            sheetName = wb.Names(i).RefersTo
            charIndex = InStr(1, sheetName, "!")
            If charIndex > 0 Then
                sheetName = Left(sheetName, charIndex - 1)
                sheetName = Right(sheetName, Len(sheetName) - 1)
                Exit For
            End If
        End If
        Next
        charIndex = InStr(1, sheetName, "'")
        If charIndex > 0 Then
            sheetName = Right(Left(sheetName, Len(sheetName) - 1), Len(sheetName) - 2)
        End If
        If Len(sheetName) > 0 Then
            Set GetSheetByRangeName = wb.Sheets(sheetName)
        End If
    End Function
    Public Function IsFirstSheetDocMap(ByRef wb As Workbook) As Boolean
        If wb.Worksheets.Count > 0 Then
            If LCase(wb.Worksheets(1).name) = DOCMAP_SHEET_NAME Then
                IsFirstSheetDocMap = True
                Exit Function
            End If
        End If
        IsFirstSheetDocMap = False
    End Function
    
    
      
  4. Go back to the left pane and expand VBAProjects and also expand Microsoft Excel Objects and double click the ThisWorkBook node . The ThisWorkBook will host the code responsible for creating the custom menu and hooking the OnAction event to the code responsible for renaming the sheets.
    Private Const MENU_CAPTION As String = "&Custom Menu"
    Private Const MENU_ITEM_CAPTION As String = "&Rename Sheets"
    Private Sub AddMenus()
    'ResetMenuBar
     On Error Resume Next
     Me.Application.CommandBars("Worksheet Menu Bar").Controls(MENU_CAPTION).Delete
     Dim mainMenu As CommandBar
     Dim customMenu As CommandBarControl
     Dim customMenuItem As CommandBarButton
     Dim helpMenuIndex As Integer
     Set mainMenu = Me.Application.CommandBars("Worksheet Menu Bar")
     helpMenuIndex = mainMenu.Controls("Help").Index
     Set customMenu = mainMenu.Controls.Add(Type:=msoControlPopup, Before:=helpMenuIndex)
     customMenu.Caption = MENU_CAPTION
     Set customMenuItem = customMenu.Controls.Add(Type:=msoControlButton)
     customMenuItem.Caption = MENU_ITEM_CAPTION
     customMenuItem.OnAction = "RunRenameSheetsCode"
    End Sub
    Private Sub ResetMenuBar()
        Application.CommandBars("Worksheet Menu Bar").Reset
    End Sub
    Private Sub Workbook_Open()
        Call AddMenus
    End Sub
    
  5. Save the current workbook as an excel add-in. From File menu select save as and in the save-as dialog in the "Save as type" list find and select Microsoft Office Excel Add-in (*.xla).
    By default excel saves new addins in the add-ins folder designated for the current user which is %USERPROFILE%\Application Data\Microsoft\AddIns. You can also place the add-in in C:\Program Files\Microsoft Office\OFFICE11\XLSTART causes excel to load your add-in when excel is started regardless of the user who started excel.
  6. Every time you start excel now you'll see your custom menu. If you the first sheet is name "document map" and the user clicks on the menu item which we created to rename sheets, the add-in will attempt renaming sheets based on what's in the document map.