Strange probem with capitalization of object
in [Programming]
|
From: kevlarmcc on 1 Apr 2010 12:28 I thought I was choosing to use the object itself rather than a variable for it. I get the same results with using a variable such as "ws". Thanks for the help, as I am new to this. What's second? "Dave Peterson" wrote: > First, using a variable named Worksheet is a bad idea. Excel has an object that > uses that name (it's a worksheet <vbg>.) > > Instead > > Option Explicit > Sub RunAll() > dim wks as worksheet > for each wks in worksheets > case "Report 1" > ...... > > > > kevlarmcc wrote: > > > > I have two copies of code that I swear are indentical except for the > > captialization of an object, which seems to be automatic. Somehow one copy of > > the code doesn't auto cap the object and it is that copy that works. Code is > > below: > > > > Sub RunAll() > > > > For Each Worksheet In Worksheets > > Select Case Worksheet.Name > > Case "Report 1" > > Case Else > > Worksheet.Activate > > If ActiveSheet.Name = "BGE" Then _ > > ActiveSheet.Name = "Sheet999" > > End Select > > Next Worksheet > > End Sub > > > > When Name is capped, it doesn't work. With lowercase name it works. I can't > > seem to replicate the non-capped object because when writing new code it > > won't let me not capitalize it. I am not sure what I did to get the object > > not capitalized in the first place. Anyone understand this? > > -- > > Dave Peterson > . >
From: Rick Rothstein on 1 Apr 2010 12:33 I **think** that you have declared "name" (with the small "n") as a variable somewhere where the current module can see it. I can duplicate your problem easily enough as follows... Dim name As String Sub RunAll() For Each Worksheet In Worksheets Select Case Worksheet.name Case "Report1" Case Else Worksheet.Activate If ActiveSheet.name = "BGE" _ Then ActiveSheet.name = "Sheet999" End If End Select Next Worksheet End Sub The "n" in the property name for your Worksheet variable in the Select Case statement will always be lower case matching the case used in the Dim statement. As Dave pointed out in his response, it is a bad idea to use data type names, property names, or built-in function names as names for your own variables. -- Rick (MVP - Excel) "kevlarmcc" <kevlarmcc(a)discussions.microsoft.com> wrote in message news:B8E2ADCD-38A8-4A5B-BA80-6ACB97D5EDE5(a)microsoft.com... > I am not sure I am explaining it correctly. It's not the value for Name it > is > the actual object Name. > > Here is the code that works: > > Sub RunAll() > For Each Worksheet In Worksheets > Select Case Worksheet.name 'This just skips the first sheet and isn't > crucial > Case "Report1" > Case Else > > 'Here is the issue; note name instead of Name > Worksheet.Activate > If ActiveSheet.name = "BGE" _ > Then ActiveSheet.name = "Sheet999" > End Select > Next Worksheet > End Sub > > Forgive me if the misunderstanding is mine; I am new to Excel code! > > > "Mike H" wrote: > >> Hi, >> >> Text comparisons using = in VB are case sensitive. If you want to ignore >> case try this simpler version of your code >> >> Sub Rename_Shts() >> Dim ws As Worksheet >> For Each ws In ThisWorkbook.Worksheets >> If UCase(ws.Name) = "BGE" Then >> ws.Name = "Sheet999" >> End If >> Next >> End Sub >> >> -- >> Mike >> >> When competing hypotheses are otherwise equal, adopt the hypothesis that >> introduces the fewest assumptions while still sufficiently answering the >> question. >> >> >> "kevlarmcc" wrote: >> >> > I have two copies of code that I swear are indentical except for the >> > captialization of an object, which seems to be automatic. Somehow one >> > copy of >> > the code doesn't auto cap the object and it is that copy that works. >> > Code is >> > below: >> > >> > Sub RunAll() >> > >> > For Each Worksheet In Worksheets >> > Select Case Worksheet.Name >> > Case "Report 1" >> > Case Else >> > Worksheet.Activate >> > If ActiveSheet.Name = "BGE" Then _ >> > ActiveSheet.Name = "Sheet999" >> > End Select >> > Next Worksheet >> > End Sub >> > >> > When Name is capped, it doesn't work. With lowercase name it works. I >> > can't >> > seem to replicate the non-capped object because when writing new code >> > it >> > won't let me not capitalize it. I am not sure what I did to get the >> > object >> > not capitalized in the first place. Anyone understand this?
From: Mike H on 1 Apr 2010 12:38 Hi, You confused me by referring to 'name' as an Object, it isn't. Activesheet is the Object and Name is a property of that object. But, I'm still no closer to understanding the issue. If the syntax you have used is correct; and it is in the sample code you posted, then 'name' will auto capitalise to Name. The code you posted in you first post and the one in the second both work for me. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "kevlarmcc" wrote: > I am not sure I am explaining it correctly. It's not the value for Name it is > the actual object Name. > > Here is the code that works: > > Sub RunAll() > For Each Worksheet In Worksheets > Select Case Worksheet.name 'This just skips the first sheet and isn't > crucial > Case "Report1" > Case Else > > 'Here is the issue; note name instead of Name > Worksheet.Activate > If ActiveSheet.name = "BGE" _ > Then ActiveSheet.name = "Sheet999" > End Select > Next Worksheet > End Sub > > Forgive me if the misunderstanding is mine; I am new to Excel code! > > > "Mike H" wrote: > > > Hi, > > > > Text comparisons using = in VB are case sensitive. If you want to ignore > > case try this simpler version of your code > > > > Sub Rename_Shts() > > Dim ws As Worksheet > > For Each ws In ThisWorkbook.Worksheets > > If UCase(ws.Name) = "BGE" Then > > ws.Name = "Sheet999" > > End If > > Next > > End Sub > > > > -- > > Mike > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > introduces the fewest assumptions while still sufficiently answering the > > question. > > > > > > "kevlarmcc" wrote: > > > > > I have two copies of code that I swear are indentical except for the > > > captialization of an object, which seems to be automatic. Somehow one copy of > > > the code doesn't auto cap the object and it is that copy that works. Code is > > > below: > > > > > > Sub RunAll() > > > > > > For Each Worksheet In Worksheets > > > Select Case Worksheet.Name > > > Case "Report 1" > > > Case Else > > > Worksheet.Activate > > > If ActiveSheet.Name = "BGE" Then _ > > > ActiveSheet.Name = "Sheet999" > > > End Select > > > Next Worksheet > > > End Sub > > > > > > When Name is capped, it doesn't work. With lowercase name it works. I can't > > > seem to replicate the non-capped object because when writing new code it > > > won't let me not capitalize it. I am not sure what I did to get the object > > > not capitalized in the first place. Anyone understand this?
From: kevlarmcc on 1 Apr 2010 12:53 Sorry for the confusion. I am still getting the terminology down. And the code works for me when I create a new workbook and test it, just not in the book I want it too, so I've narrowed it down to some difference in the workbook I am trying to run it in. But still no idea what the difference might be. "Mike H" wrote: > Hi, > > You confused me by referring to 'name' as an Object, it isn't. Activesheet > is the Object and Name is a property of that object. > > But, I'm still no closer to understanding the issue. If the syntax you have > used is correct; and it is in the sample code you posted, then 'name' will > auto capitalise to Name. > > The code you posted in you first post and the one in the second both work > for me. > > > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "kevlarmcc" wrote: > > > I am not sure I am explaining it correctly. It's not the value for Name it is > > the actual object Name. > > > > Here is the code that works: > > > > Sub RunAll() > > For Each Worksheet In Worksheets > > Select Case Worksheet.name 'This just skips the first sheet and isn't > > crucial > > Case "Report1" > > Case Else > > > > 'Here is the issue; note name instead of Name > > Worksheet.Activate > > If ActiveSheet.name = "BGE" _ > > Then ActiveSheet.name = "Sheet999" > > End Select > > Next Worksheet > > End Sub > > > > Forgive me if the misunderstanding is mine; I am new to Excel code! > > > > > > "Mike H" wrote: > > > > > Hi, > > > > > > Text comparisons using = in VB are case sensitive. If you want to ignore > > > case try this simpler version of your code > > > > > > Sub Rename_Shts() > > > Dim ws As Worksheet > > > For Each ws In ThisWorkbook.Worksheets > > > If UCase(ws.Name) = "BGE" Then > > > ws.Name = "Sheet999" > > > End If > > > Next > > > End Sub > > > > > > -- > > > Mike > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > introduces the fewest assumptions while still sufficiently answering the > > > question. > > > > > > > > > "kevlarmcc" wrote: > > > > > > > I have two copies of code that I swear are indentical except for the > > > > captialization of an object, which seems to be automatic. Somehow one copy of > > > > the code doesn't auto cap the object and it is that copy that works. Code is > > > > below: > > > > > > > > Sub RunAll() > > > > > > > > For Each Worksheet In Worksheets > > > > Select Case Worksheet.Name > > > > Case "Report 1" > > > > Case Else > > > > Worksheet.Activate > > > > If ActiveSheet.Name = "BGE" Then _ > > > > ActiveSheet.Name = "Sheet999" > > > > End Select > > > > Next Worksheet > > > > End Sub > > > > > > > > When Name is capped, it doesn't work. With lowercase name it works. I can't > > > > seem to replicate the non-capped object because when writing new code it > > > > won't let me not capitalize it. I am not sure what I did to get the object > > > > not capitalized in the first place. Anyone understand this?
From: Dave Peterson on 1 Apr 2010 13:11 Second was the stuff after "instead". I'd look for a variable named "Name" or a module named "Name" or a procedure/function named "Name". That's another string of letters that you shouldn't use for any of those things. kevlarmcc wrote: > > I thought I was choosing to use the object itself rather than a variable for > it. I get the same results with using a variable such as "ws". Thanks for the > help, as I am new to this. What's second? > > "Dave Peterson" wrote: > > > First, using a variable named Worksheet is a bad idea. Excel has an object that > > uses that name (it's a worksheet <vbg>.) > > > > Instead > > > > Option Explicit > > Sub RunAll() > > dim wks as worksheet > > for each wks in worksheets > > case "Report 1" > > ...... > > > > > > > > kevlarmcc wrote: > > > > > > I have two copies of code that I swear are indentical except for the > > > captialization of an object, which seems to be automatic. Somehow one copy of > > > the code doesn't auto cap the object and it is that copy that works. Code is > > > below: > > > > > > Sub RunAll() > > > > > > For Each Worksheet In Worksheets > > > Select Case Worksheet.Name > > > Case "Report 1" > > > Case Else > > > Worksheet.Activate > > > If ActiveSheet.Name = "BGE" Then _ > > > ActiveSheet.Name = "Sheet999" > > > End Select > > > Next Worksheet > > > End Sub > > > > > > When Name is capped, it doesn't work. With lowercase name it works. I can't > > > seem to replicate the non-capped object because when writing new code it > > > won't let me not capitalize it. I am not sure what I did to get the object > > > not capitalized in the first place. Anyone understand this? > > > > -- > > > > Dave Peterson > > . > > -- Dave Peterson
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: duplicating data Next: Search Directories/SubDirectories and Display in ListBox or Ms |