Name a Range and Use Range Value to ReName Worksheet

[Shiseiji]

Two problems. 1) I can't reference the cells like I'd prefer. 2) I can't get the worksheet renamed.
I import a text file with the file create date preceded with a space as the last value. So the last entry in column A is the date the text file. I'm trying to concatenate the text "User Accounts" and the value in the last row of column A. I "thought" I could use the same expression that creates the last row reference, but that generates an error.

And I can't get the worksheet renamed.

TIA to anyone with the time to look this over and educate me.

Ron

Sub m_DateUserText()
    Application.DisplayAlerts = False
    Application.DisplayStatusBar = False
    Application.ScreenUpdating = False
    '
    ws_2Users.Activate
    '
    Dim LastRow                 As Long
    Dim LastCol                 As Integer
    Dim OneName                 As Name
    Dim ThisWsName              As Range
    Dim ThisWsDate              As Range
    Dim ThisWs                  As Worksheet
    Dim ThisWb                  As Workbook
    '
    Set ThisWb = ActiveWorkBook
    Set ThisWs = ActiveSheet
    '
    For Each OneName In ThisWb.Names
        On Error Resume Next
        If OneName.RefersToRange.Parent.Name = "ws_2Users" Then OneName.Delete
        On Error GoTo 0
    Next OneName
    '
    With ThisWs
        LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
        LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
        Cells(LastRow, 1).Name = "ThisWsDate"
        Cells(LastRow, 1).Offset(1).Name = "ThisWsName"
    With ThisWs
    ThisWsName.FormulaR1C1 = "= ""User Accounts "" & Range(ThisWsDate).Value "
    .Name = ThisWsName
    End With
    'Range(ThisWsDate).EntireRow.Delete 'not used, retained for reference
    'Range(ThisWsName).EntireRow.Delete 'not used, retained for reference
    'Set ThisWsDate = Nothing 'not used, retained for reference
    'Set ThisWsName = Nothing 'not used, retained for reference
    Cells(1, 1).Select
    End With 'ThisWs
    '
End Sub

 

[StephenCrump]

You declare ThisWsName, but don't set its value, so it will be empty and will produce error values when you refer to it later:

Dim ThisWsName As Range
'...
With ThisWs
    '...
    ThisWsName.FormulaR1C1 = "= ""User Accounts "" & Range(ThisWsDate).Value "
    .Name = ThisWsName

One possible fix:

    Range("ThisWsName").Formula = "=""User Accounts "" & TEXT(ThisWsDate,""d mmm yyyy"")"
    'OR
    Range("ThisWsName").Value = "User Accounts " & Format(Range("ThisWsDate").Value, "d mmm yyyy")
    
    .Name = Range("ThisWsName").Value

By the way, you need to be careful with your references to worksheets. Normally, I'd expect to see a .Cells as indicated below, otherwise the reference would be to Cells in the ActiveSheet. In this case, it won't cause a problem, because you have set ThisWs = ActiveSheet, but you won't always get away with it.

With ThisWs
LastRow = .Cells(Cells.Rows.Count, "A").End(xlUp).Row

 

[Shiseiji]

1st, thank you Stephen! When I don't get an quick response I wonder how odd my request is or if it is really stumping the experts.

Please excuse the delayed response while I was trying a few variations. I did learn a lot ! The final lesson as I was typing up this response.

2nd. I try hard to use the lessons others have already paid for! I have a general understanding of using the "." but in all the years of using this code snippet and seeing it used by others, I don't recall seeing ".Cells" but I certainly understand your explanation and caution. I'll start doing a find and replace in all my projects. Trying your suggestions and finding some answers to other questions that came up took some time but I didn't want to come back with questions not having tried to figure out some of it on my own.

Bottom line I couldn't get away from a final error or figure out why I was getting it:
ThisWsName = .Cells(LastRow, 1).Offset(1)
'Microsoft Visual Basic
'Run-time error '1004':
' Application-defined or object-defined error

Arggg. as I was typing this I think I figured it out. I just got the flash of that you were clearly saying! I needed to put the value in the cell first and then I can name the cell as the range "ThisWsName". Duh And it appears that if I was to use "Name" we can get away with null cells.

declare ThisWsName, but don't set its value

What I also learned along the way was why some objects are OK with "=" and why some need "Set." For anyone who stumbles across this posting and is curious:
--------------------
Dim LastRow As Long
Dim LastCol As Integer
Dim ThisWsName As Range

"LastRow = " will work.
"ThisWsName =" won't work, "Set" is required.
Objects in VBA have "default" properties. The default property for a Range is the Value property.
'LastRow is a long integer (1,048,576 rows in Excel 2016). This is a number that can be used for a variable involving greater numbers than integers.
' To declare a variable that would hold such a large number, use the Long data type.
' LastCol is an Integer (16,384 columns in Excel 2016)
'----------------

Because I write the SQL that produces the text file, not a "csv" because I have to deal with commas in the last name field, but rather "^" separated values, I can make the last line anything. So I added the type account to the last value. Just now I realized what you were saying and how to make it work. Before I figured it out I ended up with:

Sub m_DateAdminText()
    Application.DisplayAlerts = False
    Application.DisplayStatusBar = False
    Application.ScreenUpdating = False
    '
    Sheet1.Activate
    '
    Dim LastRow               As Long
    Dim LastCol                As Integer
    Dim ThisWsName       As Range
    Dim ThisWs                As Worksheet
    Dim ThisWb               As Workbook
    '
    Set ThisWb = ActiveWorkbook
    Set ThisWs = Sheet1
    '
    With ThisWb
    With ThisWs
        LastRow = .Cells(Cells.Rows.Count, "A").End(xlUp).Row
        LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
'----------------------------------------------------------------
        Set ThisWsName = .Cells(LastRow, "A") ' A value
        ThisWs.Name = ThisWsName
'----------------------------------------------------------------
    Set ThisWsName = Nothing
    Cells(1, 1).Select
    End With 'ThisWs
    End With 'ThisWb
    '
End Sub

 

[Joe4]

Note that the "Set" keyword is used when creating new "objects", like ranges, worksheets, workbooks, etc.
It is not used when setting the values of numeric, string, or boolean variables.
See here for more details: Excel VBA Programming - using Set

 

[Shiseiji]

Again, thank you!

 

[Shiseiji]

Sorry, many interruptions later:

Again, thank you!!! Thus

Set ThisWsName = .Cells(LastRow, "A") ' A value

is wrong. ThisWsName is a range (error would be "object"), but it worked this time only because there was a value where I created the range.

So with your assistance and some, really a lot LOL, getting my head around this, I ended up with the following code using a data set without "User Accounts" in the last line.
And a final question, on this thread anyway. After a value is entered using offset, is there an advantage to making the offset cell a named range with "Set", i.e.

ThisWsDate.Offset(1, 0).Value = "User Accounts"
Set ThisWsType = ThisWsDate.Offset(1, 0)
With ThisWs
         .Name = ThisWsType & ThisWsDate
End With

?

Sub m_DateUser()
    ' sheet properties name is "DateUser" for reading ease
    '1-9-20
    '
    Application.DisplayAlerts = False
    Application.DisplayStatusBar = False
    Application.ScreenUpdating = False
    '
    ws_2Users.Activate
    '
    Dim LastRow                 As Long
    Dim LastCol                 As Integer
    Dim ThisWsDate          As Range
    Dim ThisWsType          As Range
    Dim ThisWs                  As Worksheet
    Dim ThisWb                  As Workbook
    '
    Set ThisWs = ws_2Users
    '
    With ThisWs
        LastRow = .Cells(Cells.Rows.Count, "A").End(xlUp).Row
        LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
'---------------------------------------
    Set ThisWsDate = .Cells(LastRow, "A") ' range has a value so valid to use as an object
    ThisWsDate.Offset(1, 0).Value = "User Accounts"
    With ThisWs
         .Name = ThisWsDate.Offset(1, 0) & ThisWsDate
    End With
'---------OR--------------------------------
'Set ThisWsDate = .Cells(LastRow, "A") ' range has a value so valid to use as an object
'ThisWsDate.Offset(1, 0).Value = "User Accounts"
'Set ThisWsType = ThisWsDate.Offset(1, 0)
'With ThisWs
'         .Name = ThisWsType & ThisWsDate
'End With
'-----------------------------------------------
    Cells(1, 1).Select
End With 'ThisWs
End Sub

?

 

[StephenCrump]

Well done for persisting. I found VBA confusing at first - my first exposure to OOP - but it does get easier ...

Set ThisWsName = .Cells(LastRow, "A") ' A value

is wrong. ThisWsName is a range (error would be "object"), but it worked this time only because there was a value where I created the range.

No, it's not wrong. The following code will always "work", regardless of whether there is a value in the relevant cell:

Dim ThisWs As Worksheet
    
Set ThisWs = ws_2Users
    
With ThisWs
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set ThisWsDate = .Cells(LastRow, "A")
End With

If LastRow is 20, say, then ThisWsDate is the range object ws_2Users.Range("A20"), and you can access all this object's properties and methods, including .Value (which may be blank), .Formula, .Font.Name etc

After a value is entered using offset, is there an advantage to making the offset cell a named range with "Set"?

Set ThisWsType = ThisWsDate.Offset(1, 0)

Here, you're creating a VBA variable called ThisWsType (as opposed to an Excel named range). There's no harm in doing so, but also no real advantage here. But if you were going to keep referring to this range in subsequent code, sure - your code will be more elegant and meaningful referring to ThisWsType rather than ThisWsDate.Offset(1)

One final comment. You're quite right about default properties, and hence, for example coding:

ThisWs.Name = ThisWsType & ThisWsDate

My programming preference (usually!) is not to rely on default properties, but rather:

ThisWs.Name = ThisWsType.Value & ThisWsDate.Value

I think it's good discipline as you need to think and specify clearly, rather than fuzzily, what you're doing.

 

[Shiseiji]

Well done for persisting. I found VBA confusing at first - my first exposure to OOP - but it does get easier ...

I'm a hack, but thanks. I started doing VERY basic SQL, uh, ~ 20 years ago, to prove, based on the trend, that something was never going to happen. Done some GIS, original Keyhole then ESRI. So I do what I can but I'll never be a programmer. I think my 1st search here was on how to format a military date time group. All y 100+ postings are "Gee, I'm stumped, help please!" I'm still struggling with getting error handling working! But I have learned to break my ?30? odd modules into small bites so I can easily find what's broke this time.

No, it's not wrong. The following code will always "work", regardless of whether there is a value in the relevant cell:

Then I have more work to do. I was getting object errors and thought I had figured out why, though why a named range would work and not a temporary variable wouldn't was puzzling.

There's no harm in doing so, but also no real advantage here.

LOL, good to know I got this one right.

ThisWs.Name = ThisWsType.Value & ThisWsDate.Value

Makes sense to me, and for the little extra typing it takes, why not.

Again, thanks for all the feed back, it's appreciated. I can't pay it back here, but I teach volunteer bicycle maintenance and 3 hrs a week helping fix their bikes, so I do what i can. If you have any bike questions, let me know!

R

 

[StephenCrump]

If you have any bike questions, let me know!

Ha! Don't get me started. You're talking to a MAMIL!

 

[Shiseiji]

? Sadly, at least for now, my riding days are over. But I can still turn wrenches.