Better way to reference / use worksheet codenames?

aquixano · Aug 29, 2017

I recently switched to using worksheet code names vs. worksheet names in my code because of some advantages I thought would benefit me. One inherent problem I didn’t anticipate was the inability to assign/use code names with a variable (apparently because there isn’t a worksheet collection for codenames). The two code examples below do the same thing – loop through specific worksheets, but using worksheet names I could do it in two lines of code, but with code names it requires 31 lines in a Select Case. I understand one way to avoid this is by changing my macro security, but I’m not inclined to do that. I still lean toward using codenames, and would prefer not to use both for consistency, but in this specific example it seems sorta kludge. Is there a better way to do this or am I at the “take or leave it” point with using codenames? Thanks for reading.

Code:

  ‘Original code usine worksheet names
          davesh = Choose(j, "D.A.V.E.", "DAVE_Alt_Setups", "Earnings_Flags", "XLB", "XLE", "XLF", "XLI", "XLK", "XLP", "XLU", "XLV", "XLY", "XLRE", "NDX")
          Sheets(davesh).Select


  ‘Revised code using worksheet codenames
          Wks = Choose(j, "cnDAVE", "cnDaveAlt", "cnEarnFlags", "cnXLB", "cnXLE", "cnXLF", "cnXLI", "cnXLK", "cnXLP", "cnXLRE", "cnXLU", "cnXLV", "cnXLY", "cnNDX")
          Select Case Wks
              Case "cnDAVE"
                  cnDAVE.Select
              Case "cnDaveAlt"
                  cnDaveAlt.Select
              Case "cnEarnFlags"
                  cnEarnFlags.Select
              Case "cnXLB"
                  cnXLB.Select
              Case "cnXLE"
                  cnXLE.Select
              Case "cnXLF"
                  cnXLF.Select
              Case "cnXLI"
                  cnXLI.Select
              Case "cnXLK"
                  cnXLK.Select
              Case "cnXLP"
                  cnXLP.Select
              Case "cnXLRE"
                  cnXLRE.Select
              Case "cnXLU"
                  cnXLU.Select
              Case "cnXLV"
                  cnXLV.Select
              Case "cnXLY"
                  cnXLY.Select
              Case "cnNDX"
                  cnNDX.Select
          End Select

Norie · Aug 29, 2017

Perhaps.

Code:

Dim Wks As Worksheet

    Set Wks = Choose(j, cnDAVE, cnDaveAlt, cnEarnFlags, cnXLB, cnXLE, cnXLF, cnXLI, cnXLK, cnXLP, cnXLRE, cnXLU, cnXLV, cnXLY, cnNDX)

    Wks.Select

aquixano · Aug 29, 2017

Thanks Norie - much cleaner - I figured there had to be a way to do it!

Better way to reference / use worksheet codenames?

aquixano

New Member

Excel Facts

Norie

Well-known Member

aquixano

New Member

Similar threads

Forum statistics

Share this page

Better way to reference / use worksheet codenames?

aquixano

New Member

Excel Facts

Norie

Well-known Member

aquixano

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock