通过构造跟进VBA继承，构造函数不工作？-Follow-UptoVBAinheritanceviaconstruction,constructornotworking?

1  

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.

想要使用VBA的面向对象语言功能并不疯狂,但是你提供的用例与excel已经提供的内置对象相差不远,因此不清楚你将使用多少从你将要添加的复杂性中获益。在excel vba中可以利用相当大的力量,但最好尽可能地发挥它的优势。

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.

通过使用本文末尾的代码,您可以更轻松地更好地着色不同的单元格 - 显然不是要完成您想要的所有操作,但显然不需要针对单个列和行使用OO。

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.

Excel和vba与程序员习惯于提供完全类继承的python之类的东西完全不同。使用VBA,您只能使用接口继承,这将允许您重用代码。但是如果你不小心你可能很容易得到很多存根代码,你必须从一个类复制到另一个类,以便能够满足你希望你的类实现的接口。

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.

还有一件事你必须要从传统的OO语言中解决这个问题,那就是你在内存中的对象中复制了多少数据,而不是只是留在工作表上并根据需要访问它们。想要将所有东西加载到对象中并从那里进行操作是一种自然的趋势 - 但是在这种环境中你应该真正考虑过三次。

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).

如果您有一个现有的服务器后端,它将验证在工作表和数据库之间移动的数据,那么至少您可以分离正常的MVC问题。实际上,您将使用excel作为一种具有电子表格用户喜爱的附加功能的网页。如果您没有后端,那么您必须非常小心地在此环境中验证模型和数据。您应该习惯保护工作表的想法,除了那些用户必须输入数据的单元格(假设您编写代码以使其他人受益而不仅仅是您自己)。事实上,使用不同颜色为输入单元格和计算单元格着色是一个好主意,以突出显示这种差异。后者应该受到保护,而前者在需要时可以触发将验证输入并更新模型状态的事件(如果有的话,最好使用后端工作)。

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.

保护单元格还允许您在工作表的明确定义的部分中隐藏状态信息,这些部分可用于引用回工作对象。实际上,良好的用例是将明确定义的单元块隔离为特定类实例的用户界面的用例。

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.

在可能的情况下,您应该使用范围来引用同一工作表和其他工作表上的部分。命名范围是你的朋友。经过数据验证的列表对于连续数据也非常有用,应该尽可能使用它们,因为它们非常有效。对于大小有限的非连续数据集,您可以使用ActiveX组合框,如果它们的事件处理程序被传递给后者唯一的id,则它们可以引用内存中对象实例。

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.

在检查事件更改时,您应该注意Worksheet_Change轮询,您将在Web上看到很多示例。如果你不小心,这可能会花费相当多的时间。

To summarize: use the whatever power you can harness from excel and refrain from re-inventing wheels.

总结一下:使用excel可以利用的任何功率,并避免重新发明轮子。

' 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