使用工作表CodeName并避免.Select& .启用 [英] Using Worksheet CodeName and Avoiding .Select & .Activate

查看:108
本文介绍了使用工作表CodeName并避免.Select& .启用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在我的工作簿中,我经常需要使用它们的 CodeName 来激活一些工作表,然后在该工作表中搜索一些文本,并使用包含我要查找的文本的单元格.

In my workbook I frequently need to activate some sheets by using their CodeName then search for some texts within that sheet and use the row or column number of the cell that contains the text I'm looking for.

在那种情况下,我使用以下类型的代码:

In that situations, I am using below kind of codes:

Sheet16.Select '(Using codename)
Cells.Find(What:="FIRST TEXT I'M LOOKING FOR", After:= _
        ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

   FirstRow= ActiveCell.Row

    Cells.Find(What:="SECOND TEXT I'M LOOKING FOR", After:= _
        ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

SecondRow = ActiveCell.Row

Rows(FirstRow & ":" & SecondRow + 1).EntireRow.Hidden = False

一切工作正常,但是如今,我正在尝试改进代码,我想更快地运行代码.

Eveything works well, but nowadays I am trying to improve my codes and I'd like to run my codes faster.

现在,

Now,

(我正在寻找类似于ThisWorkbook.Worksheets("Sheet1")的答案-不是函数

Dim wb as Workbook, ws as Worksheet
set wb = ThisWorkbook
set ws = wb.CodeName(Sheet16) or wb.Sheet16 or sheet16
'then 
ws.Cells.Find(What ..........   rest of the code  ...... )

没有人为 CodeName 属性工作. 按代号完全引用工作表

none of them working for CodeName property. Fully reference a worksheet by codename or Referencing sheets in another workbook by codename using VBA didn't answer my question.

再次在该示例中,我首先在代码的第一部分中搜索特定的文本,即:="FILO TEXT I'M LOOKING FOR" ,然后我需要使用该单元格获取它的行号或使用偏移量任何内容,因此,我觉得自己不得不使用.激活因为,

Again in that example I firstly search for specific text, which is :="FIRST TEXT I'M LOOKING FOR" in the first part of my code, and then I need to use that cell to get it's row number or use offset or anything , and because of that I feel myself obligated to use .Activate because,

FirstRow =  Cells.Find(What:="FIRST TEXT I'M LOOKING FOR", After:= _
        ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row

种类的代码无法正常工作. 如何避免在Excel VBA宏中使用选择" ,在此答案中,有一些建议,但没有一个对我有帮助案子.我试图从该答案的所有者那里得到一个答案,以避免任何重复的问题,但他建议我提出一个新问题. (只要我的两个问题都属于我的示例代码,并且将它们连接起来,我就会在一个问题中将它们一起询问.)

kind of codes do not work as well. How to avoid using Select in Excel VBA macros here in that answer there are couple of suggestions but none of them helps me in this case. I tried to get an answer from the owner of this answer to avoid any duplicate question but he suggested me to ask a new question. (And as long as both of my questions belong to my example code and I'll connect them, I asked them together in one question.)

推荐答案

在将工作表变量设置为代号时,限制是您只能在ThisWorkbook中使用代号,即包含代码的工作簿.

While setting a sheet variable to the codename, the limitation is you can use the codename in ThisWorkbook only i.e. the workbook which contains the code.

考虑此代码...

Dim ws As Worksheet
Set ws = wsData 'where wsData is the CodeName of a sheet.

现在,您可以在代码中对ws工作表进行操作或执行操作,而无需激活或选择它. 实际上,对于CodeNames,不需要声明工作表变量,而无论当前处于活动状态的工作表,都可以使用其代号直接引用工作表.

Now in your code you can manipulate or perform actions on ws sheet without activating or selecting it. Actually in case of CodeNames, you don't need to declare a sheet variable, you can directly refer to the sheet by using it's codename irrespective of which sheet is currently active.

喜欢...

wsData.Cells.Clear
Set Rng = wsData.Range("A1").CurrentRegion

例如加上另一个示例代码

e.g. with your another example code

Dim ws As Worksheet
Set ws = wsData 'where wsData is the CodeName of a sheet.

FirstRow = ws.Cells.Find(What:="FIRST TEXT I'M LOOKING FOR", After:= _
        ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row

'Or just (without declaring the ws sheet variable where wsData is the sheet code name)
FirstRow = wsData.Cells.Find(What:="FIRST TEXT I'M LOOKING FOR", After:= _
        ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row

这篇关于使用工作表CodeName并避免.Select& .启用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆