使用来自personal.xls的宏时的VBA逻辑 [英] VBA logic when using macros from personal.xls

问题描述

我运行一个电子表格报告，其中包含大约50列的数据，从1到5000行。我只对4列感兴趣，但它们从不在同一位置，因为这些报告对于每个客户端设置有所不同。然后我拿这4列并粘贴到一个新的工作簿中，我可以导入另一个程序。

我有三个宏创建，完成这个任务，如果从本地运行完成文件。当我将它们加载到personal.xls中以用于各种文件时，我有问题。具体的工作簿/工作表引用问题。

部分宏运行到我打算从中导出的表，而其他部分则作用于personal.xls文件本身。这使我感到困惑，因为我没有任何使用thisworkbook或activeworksheet等命令的行。

例如：
- 第一行被编码为重命名Sheet1。宏重命名为personal.xls中的Sheet1。

- 下一行是找到我感兴趣的列的位置的四个查找命令中的第一行，然后移动它们。这个宏完美地运行在我打算的表单上。

我认为我最好的课程是通过命名活动工作簿，然后将每个命令分配到工作簿级别来开始每个宏而不是从Worksheets，Range等开始。

任何人都可以帮助我了解VBA在从personal.xls执行宏时如何思考，以及如何最好地避免正在运行的宏

解决方案

有两种方法可以采用。我的代码中使用了一个或两个，这不是一个或其他情况。

声明变量

首先定义要在变量中处理的每个工作表。我通常呆在床单层面，但这只是个人选择。如果你宁愿在工作簿级别，那也没关系。一个过程可能如下所示：

  Dim shSource as Worksheet 
 Dim shDest as Worksheet 
 
设置shSource =工作簿（SomeBook）。工作表（1）
设置shDest = ActiveWorkbook.Worksheets（Summary）
  

然后每当我引用一个范围或单元格或其他任何表单时，我用该工作表对象变量来表示。即使我需要到工作簿，我从表格开始。例如，如果我需要从上面的例子中关闭Source工作簿，我将使用

  shSource.Parent.Close False 
  

我设置了我需要的表单变量，然后我所做的一切都是根据这些变量。 / p>

编辑

如果你打开或创建工作簿，那么变量是绝对的要走的路例如，如果你打开一个工作簿，你可以使用这两个例子之一

  Dim wb As Workbook 
设置wb = Workbooks.Open（C：\ ...）
 
 Dim ws As Worksheet 
设置ws = Workbooks.Open（C：\ ...）。工作表（1）
  

或创建新的，这两个示例之一：

  Dim wb As Workbook 
设置wb = Workbooks.Add 
 
 Dim ws as Worksheet 
设置ws =工作簿.Add.Worksheets（1）
  

使用块

当我只想到一些东西时，设置一堆变量似乎是浪费的，在这些情况下，我使用一个Block，所以我仍然可以完全合格的参考，但在我的代码中没有一堆杂乱。

 使用Workbook（MyBook）
 With 。$（$）
 .Range（A1）。value =stuff
结束
 
带.Worksheets（Second_Sheet）
.Range（G10）。Formula == A1
结束
结束
  

我可能更喜欢变量方法，但是我也使用它们。

编辑2：隐式引用

你应该永远明确地引用你的工作簿和工作表，但是如果没有的话，Excel还会表现出来。一行代码开始像 Range（A1）。Value = ... 被称为非限定引用。您正在引用一个范围，但是您不是在该表格中显示哪个工作表。Excel根据您的代码位置处理不合格的引用。

在工作表的类模块中（像您使用SheetChangeChange的表格事件），非限定引用是指该模块表示的工作表。如果您在Sheet1模块中的更改事件中工作，并且代码 x = Range（G1）。值则您所指的G1位于Sheet1上。在这种情况下，您应该使用 Me 关键字，而不是依靠Excel。

在任何其他模块像标准模块一样），非限定引用指的是ActiveSheet。相同的 x = Range（G1）。标准模块中的值代码指的是在任何表格都有焦点的G1。

Excel对不合格参考的处理非常可靠。您可以通过依靠Excel来解决合格的参考来轻松创建强大的代码。但你不应该。如果您符合每个参考资格，您的代码将更易于阅读并易于调试。我有资格参考。这不是我永远做的事情之一，除非我很懒 - 我真的在100％的时间里做。

I run a spreadsheet report that holds about 50 columns of data for anywhere from 1 to 5000 rows. I'm only interested in 4 columns, but they are never in the same location as these reports are set-up a bit differently for each client. I then take those 4 columns and paste into a new workbook that I can import into another program.

I have three macros created that accomplish this task flawlessy if ran from the local file. When I load them into the personal.xls for use on various files I have issues. Specifically workbook/worksheet referencing issues.

Parts of the macro run to the sheet I intend from them to result on, while other parts act on the personal.xls file itself. This confuses me because I don't have any lines that use commands such as 'thisworkbook' or 'activeworksheet'.

For example: - The first line is coded to rename Sheet1. The macro renames Sheet1 in personal.xls.
- The next line is the first of four Find commands that locate where the columns i'm interested are located and then move them. This macro runs perfectly on the sheet I intend.

I think my best course is to begin each macro by naming the active workbook and then breaking out each command to the workbook level instead of starting with Worksheets, Range, etc.

Can anyone help me understand what VBA is thinking when performing macros from personal.xls and how to best avoid the macros being run on that sheet itself?

解决方案

There are two approaches you can take. I use one or both in my code - it's not a one or the other situations.

Declare Variables

Start by defining each sheet that you want to work on in a variable. I generally stay at the sheet level, but that's just a personal choice. If you'd rather be at the workbook level, that's OK too. A procedure might looks like:

Dim shSource as Worksheet
Dim shDest as Worksheet

Set shSource = Workbooks("SomeBook").Worksheets(1)
Set shDest = ActiveWorkbook.Worksheets("Summary")

then whenever I reference a Range or Cells or anything else on a sheet, I preface it with that sheet object variable. Even if I need to get to the workbook, I start with the sheet. If I needed to, for instance, close the Source workbook from the above example, I would use

shSource.Parent.Close False

I set up the sheet variables I need and then everything I do is in terms of those variables.

Edit

If you're opening or creating workbooks, then variables is definitely the way to go. For example, if you're opening a workbook, you could use one of these two examples

Dim wb As Workbook
Set wb = Workbooks.Open(C:\...)

Dim ws As Worksheet
Set ws = Workbooks.Open("C:\...).Worksheets(1)

or creating new, one of these two examples:

Dim wb As Workbook
Set wb = Workbooks.Add

Dim ws as Worksheet
Set ws = Workbooks.Add.Worksheets(1)

With Blocks

When I'm only trying to get at something one time, it seems like a waste to set up a bunch of variables. In those cases, I use a With Block so I can still have fully qualified references, but without a bunch of clutter in my code.

With Workbook("MyBook")
    With .Worksheets("First_Sheet")
        .Range("A1").Value = "stuff"
    End With

    With .Worksheets("Second_Sheet")
        .Range("G10").Formula = "=A1"
    End With
End With

I probably prefer the variable method, but I use them both.

Edit 2: Implicit Referencing

You should always explicitly reference your workbooks and worksheets, but it's still instructional to know how Excel will behave if you don't. A line of code that starts like Range("A1").Value = ... is called an unqualified reference. You're referencing a range, but you're not saying which sheet its on or which workbook that sheet is in. Excel handles unqualified references differently depending on where your code is.

In a Sheet's Class Module (like where you use sheet events like SelectionChange), unqualified references refer to the sheet represented by that module. If you're in the Sheet1 module working in the Change event and you code x = Range("G1").Value then the G1 you are referring to is on Sheet1. In this case, you should be using the Me keyword rather than relying on Excel.

In any other module (like a Standard Module), unqualified references refer to the ActiveSheet. The same x = Range("G1").Value code in a Standard Module refers to G1 on whichever sheet has the focus.

Excel's treatment of unqualified references is very reliable. You could easily create robust code by relying on Excel to resolve the qualified references. But you shouldn't. Your code will be more readable and easier to debug if you qualify every reference. I qualify every reference. And that's not one of those things I "always" do except when I'm lazy - I really do it 100% of the time.

这篇关于使用来自personal.xls的宏时的VBA逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！