Name Excel sheet with specific date format

Question

Name Excel sheet with specific date format

Navigation

#1 by (1 votes)
#2 by (-2 votes)

1

I would like to name the sheets of Excel with a specific format "name of the day corresponding to the date" and "date entered by the user" , it works for me well the code except for the part of the name of the day, always shows me "Sat" , this is the code, thanks in advance.

Public Sub nameSheet()
  Dim strDate As String
Line1:
  strDate = InputBox("Insert date in format mm/dd/yy", "Text", Format(Now(), "mm/dd/yy"))
  If IsDate(strDate) Then
    strDate = Format(CDate(strDate), "mm.dd.yy")
  Else
    MsgBox "Invalid format"
    GoTo Line1
  End If
  ActiveSheet.Name = WeekdayName(Weekday(strDate), True, firstdayofweek) + " " + strDate
End Sub

vba excel

asked by oski90 05.03.2017 в 10:55

source

2 answers

Exception in thread "Loading Sources" when starting Android SDK Manager Sort data in a laravel view

score 1 · Answer 1

Functions related to dates are determined by the locale.

The problem is when using the Format function, using the English (United States) regional setting:

Public Sub nameSheet()
  Dim strDate As String
Line1:
  strDate = InputBox("Insert date in format mm/dd/yy", "Text", Format(Now(), "mm/dd/yy"))
  Debug.Print strDate

  If Not IsDate(strDate) Then
    MsgBox "Invalid format"
    GoTo Line1
  End If

  Debug.Print Format(strDate, "dddd mm.dd.yy")
End Sub

Result:

03/05/17
Sunday 03.05.17

score -2 · Answer 2

Have you declared the variable firstdayofweek that you are passing as a parameter? Have you assigned him value?

This parameter determines the first day of the week (in some countries the week starts on Sunday instead of Monday).

Make sure that this parameter contains a correct value (see the documentation of the function WeekdayName to see what the parameters are like). In the documentation it is clear that firstdayofweek is optional, so it is not necessary to pass the third parameter, like this:

ActiveSheet.Name = WeekdayName(Weekday(strDate), True) + " " + strDate

Using it like this, firstdayofweek = 0 by default. Then take the first day of the week from the system configuration.

If you want to specify the first day of the week, the third parameter must have the numerical value of the desired day of the week (Sunday-> 1, Monday-> 2, etc.). So:

ActiveSheet.Name = WeekdayName(Weekday(strDate), True, 2) + " " + strDate

Of course, you can pass a variable with the numerical value instead of putting it in a fixed gear.

It may always return "Sat" because, if that parameter is null (that is, variable undeclared or with value null ), some function used internally in WeekdayName could be returning -1 (a Sometimes the functions do that to warn that something has gone wrong). If that were the case, maybe the function is not capturing that error and going ahead with its internal operations (which, if Sunday == 0, could be interpreted as Saturday).