后续VBA继承通过构建，构造函数不工作？ [英] Follow-Up to VBA inheritance via construction, constructor not working?
问题描述
这是的追踪这个问题。
这是我的用例：我想逐个细胞地比较两个Excel文件，并突出显示不同的单元格。每个文件将有几张表，每张表将有几个列，每个列有一个标题和一些值（典型值）。以下是比较代码的草图活动图：
Here's my use case: I want to compare two Excel files cell-by-cell and highlight the cells that are different. Each file will have a few sheets and each sheet will have a few columns, each with a header and a few values (as typical). Here is my draft activity diagram for the comparison code:
这是我的草图类图：
我的目标是使VBA对于我经常遇到的类型（比较新旧版本的电子表格）来说不那么麻烦。也就是说，我希望它的工作更像Python ...特别是，我想编写如下代码：
My goal is to make VBA less cumbersome for the types of things I do often (such as comparing new and old versions of spreadsheets). That is, I want it to work more like Python... In particular, I want to write code like this:
For Each Sheet1 In File1
Name1 = Sheet1.Name
If File2.sheet_dict.Exists(Name1) Then
Sheet2 = File1.Sheets(File2.sheet_dict(Name1))
Sheet2.Checked = True
For Each Col1 In Sheet1.cols
hdr = Col1.Header
If Sheet2.header_dict.Exists(hdr) Then
Col2 = Sheet2.cols(Sheet2.header_dict(hdr))
Col2.Checked = True
For Each Val1 In Col1.Vals
Val2 = Col2.Vals(Val1.row_number)
Val2.Checked = True
If Not Val1.Match(Val2) Then
Val1.formatBad()
Val2.formatBad()
End If
Next Val1
For Each Val2 In Col2.Vals
If Not Val2.Checked Then
Val2.formatBad
End If
Next Val2
Else
Col1.formatBad()
End If
Next Col1
For Each Col2 In File2.cols
If Not Col2.Checked Then
Col2.formatBad
End If
Next Col2
Else
Sheet1.formatBad()
End If
Next Sheet1
For Each Sheet2 In File2
If Not Sheet2.Checked Then
Sheet2.formatBad()
End If
Next Sheet2
当然我会将所有数据加载到对象首先，但你得到的想法。尝试在VBA中做这样的事情是不是很疯狂？
Of course I'd have load all the data into the objects first, but you get the idea. Is it crazy to try to do something like this in VBA?
推荐答案
想使用面向对象的语言不是很疯狂
VBA的功能，但是您提供的用例不是
，远离已经提供的excel
的内置对象，因此不清楚您会有多少
从您将添加的复杂性中获得收益。有
a公平的权力在excel vba中被利用，但它是
最好地摆脱它的优势，只要你可以。
It's not crazy to want to use the object oriented language features of VBA but the use case you're giving isn't that far removed from the built-in objects that excel already provides and so it isn't clear how much you'll gain from the complexity you'll be adding to get it. There's a fair bit of power to be harnessed in excel vba but it's best to play off of it's strengths whenever you can.
你可以通过使用这篇文章结尾处的代码，很容易使不同的单元格更加
有效地
- 显然不是你所要的
，但是显然不需要
对于单列和行来说，使用OO。
You could just as easily colorize differing cells more efficiently by using the code at the end of this post - obviously not to do all that you intend but clearly something that doesn't require resorting to OO for single columns and rows.
Excel和vba与程序员的价值是非常不同的，这些程序员是从
中得到的，像python那样提供完整的
类继承。使用VBA，您只能拥有
接口继承，这将允许您重用代码。但是
如果你不小心你可以很容易地得到很多
的存根代码，你必须从类复制到类b $ b能够满足你想要的接口你的课程要实现。
Excel and vba are quite different than what programmers are used to coming from something like python that provide full class inheritance. With VBA you're stuck with only having interface inheritance which will allow you to reuse code. But if you're not careful you could very easily end up with a lot of stub code that you have to copy from class to class just to be able to satisfy the interface you want your classes to implement.
另外还有另外一件事，你必须把
的头包裹在传统的OO语言之上，那就是
数据你复制在你的内存中的对象，而不是仅仅离开
a工作表，并根据需要访问它们。有一个自然的
倾向想要将所有东西加载到一个对象中，并从那里处理
- 但是在这种环境中，你应该真正考虑两个
的冲动。
There's also another thing you definitely have to wrap your head around coming from a conventional OO language and that's how much data you replicate in your in-memory objects as opposed to just leaving on a worksheet and accessing them as required. There's a natural tendancy to want to load everything into an object and manipulate from there - but it's an urge you should really think twice about in this environment.
如果您有一个现有的服务器后端，将验证在工作表和数据库之间移动的
数据，那么至少
您有一种隔离正常的MVC问题的方法。实际上，您将
将excel用作一种网页，其中包含电子表格用户喜欢的附加功能
。如果你没有后端，那么你
真的必须非常小心地验证你的模型和
环境中的数据。您应该得到
用于保护工作表的想法，除了用户必须输入数据的单元格
（假设您正在编写代码
以使其他人不仅仅是您自己）。事实上，这是一个很好的
的想法，以使用不同的
颜色对输入单元格和计算单元格进行着色，以突出显示这种差异。后者应该被保护
，而前者在需要的时候可以触发将验证
输入并更新模型状态的事件（如果
你有一个，理想情况下可以使用后端工作）
If you have an existing server back-end that will validate the data that moves between your worksheets and a database then at least you have a way of segregating normal MVC concerns. In effect you'd be using excel as a sort of web page with the additional functionality that spreadsheet users love. If you don't have a back-end then you really have to be very careful about validating your models and data in this environment. You should get used to the idea of protecting the worksheet except for those cells that users will have to input data into (assuming you are writing code to benefit others than just yourself). In fact it's a good idea to color the input cells and the calculated cells with distinct colors to highlight this difference. The latter should be protected whereas the former where needed can trigger events that will validate input and update the model state (and ideally work with a back-end if you've got one).
保护单元格还允许您隐藏状态信息在
工作表中定义明确的部分，可用于引用
返回工作对象事实上，良好的用例是那些
将明确定义的单元格块作为特定
类实例的用户界面进行隔离。
Protecting cells also allows you to hide state information in a well-defined sections of the worksheet that can be used to reference back to working objects. In fact good use cases are those that segregate well defined cell blocks as an user interface to specific class instances.
尽可能使用范围在
相同的工作表和其他工作表上引用节。命名范围是你的朋友。数据
验证的列表对于连续的数据也是非常有帮助的，应该尽可能使用
，因为它们对它们的工作非常有效。对于大小限制的
非连续数据集，您可以使用ActiveX
组合框，如果将
事件处理函数传递给唯一的id，则可以引用内存对象实例
Where possible you should use ranges to reference sections on the same worksheet and on others. Named ranges are your friend here. Data validated lists are also very helpful for contiguous data and should be used whenever possible as they are very efficient at what they do. For non-contiguous data sets that are limited in size, you can use ActiveX combo-boxes which can reference in-memory object instances if their event handlers are passed an id unique to the latter.
当检查事件更改时，您应该注意Worksheet_Change
轮询，您将看到很多网络示例。如果您不小心，可以
嚼一点时间。
When checking for event changes, you should be careful of the Worksheet_Change polling that you'll see a lot of examples of on the web. This can chew up a fair bit of time if you aren't careful.
总结：使用您可以利用excel和$ b的任何能力$ b不要重新发明轮子。
To summarize: use the whatever power you can harness from excel and refrain from re-inventing wheels.
' Compares the sheet 1 of the workbook you're in
' with sheet1 of the workbook file in 'Filename'
' and colors the cells that differ between the two.
Sub compare_workbooks_sheet1()
Dim Filename As String
Filename = "C:\MyBook.xlsm"
Dim wrkbk1 As Workbook
Set wrkbk1 = Workbooks.Open(Filename:=Filename)
Dim sht1 As Worksheet ' worksheet you're in
Dim sht2 As Worksheet ' worksheet you've opened to compare
Set sht1 = wrkbk1.Worksheets("Sheet1")
Set sht2 = ThisWorkbook.Worksheets("Sheet1")
Dim row As Long, col As Long
With sht2
For row = 1 To sht1.UsedRange.Rows.Count
For col = 1 To sht1.UsedRange.Columns.Count
If sht1.Cells(row, col) <> sht2.Cells(row, col) Then
.Cells(row, col).Interior.ColorIndex = 5
End If
Next
Next
End With
wrkbk1.Close
End Sub
这篇关于后续VBA继承通过构建，构造函数不工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
