excel - Follow-Up to VBA inheritance via construction, constructor not working? -

this follow-up this question.

here's use case: want compare 2 excel files cell-by-cell , highlight cells different. each file have few sheets , each sheet have few columns, each header , few values (as typical). here draft activity diagram comparison code:

activity diagram

and here draft class diagram:

class diagram

my goal make vba less cumbersome types of things (such comparing new , old versions of spreadsheets). is, want work more python... in particular, want write code this:

for each sheet1 in file1     name1 = sheet1.name     if file2.sheet_dict.exists(name1)         sheet2 = file1.sheets(file2.sheet_dict(name1))         sheet2.checked = true         each col1 in sheet1.cols             hdr = col1.header             if sheet2.header_dict.exists(hdr)                 col2 = sheet2.cols(sheet2.header_dict(hdr))                 col2.checked = true                 each val1 in col1.vals                     val2 = col2.vals(val1.row_number)                     val2.checked = true                     if not val1.match(val2)                         val1.formatbad()                         val2.formatbad()                     end if                 next val1                 each val2 in col2.vals                     if not val2.checked                         val2.formatbad                     end if                 next val2             else                 col1.formatbad()             end if         next col1         each col2 in file2.cols             if not col2.checked                 col2.formatbad             end if         next col2     else         sheet1.formatbad()     end if next sheet1  each sheet2 in file2     if not sheet2.checked         sheet2.formatbad()     end if next sheet2 

of course i'd have load data objects first, idea. crazy try in vba?

it's not crazy want use object oriented language features of vba use case you're giving isn't far removed built-in objects excel provides , isn't clear how you'll gain complexity you'll adding it. there's fair bit of power harnessed in excel vba it's best play off of it's strengths whenever can.

you colorize differing cells more efficiently using code @ end of post - not intend doesn't require resorting oo single columns , rows.

excel , vba quite different programmers used coming python provide full class inheritance. vba you're stuck having interface inheritance allow reuse code. if you're not careful end lot of stub code have copy class class able satisfy interface want classes implement.

there's thing have wrap head around coming conventional oo language , that's how data replicate in in-memory objects opposed leaving on worksheet , accessing them required. there's natural tendancy want load object , manipulate there - it's urge should think twice in environment.

if have existing server back-end validate data moves between worksheets , database @ least have way of segregating normal mvc concerns. in effect you'd using excel sort of web page additional functionality spreadsheet users love. if don't have back-end have careful validating models , data in environment. should used idea of protecting worksheet except cells users have input data (assuming writing code benefit others yourself). in fact it's idea color input cells , calculated cells distinct colors highlight difference. latter should protected whereas former needed can trigger events validate input , update model state (and ideally work back-end if you've got one).

protecting cells allows hide state information in well-defined sections of worksheet can used reference working objects. in fact use cases segregate defined cell blocks user interface specific class instances.

where possible should use ranges reference sections on same worksheet , on others. named ranges friend here. data validated lists helpful contiguous data , should used whenever possible efficient @ do. non-contiguous data sets limited in size, can use activex combo-boxes can reference in-memory object instances if event handlers passed id unique latter.

when checking event changes, should careful of worksheet_change polling you'll see lot of examples of on web. can chew fair bit of time if aren't careful.

to summarize: use whatever power can harness excel , refrain re-inventing wheels.

' compares sheet 1 of workbook you're in ' sheet1 of workbook file in 'filename' ' , colors cells differ between two. sub compare_workbooks_sheet1()     dim filename string     filename = "c:\mybook.xlsm"      dim wrkbk1 workbook     set wrkbk1 = workbooks.open(filename:=filename)      dim sht1 worksheet ' worksheet you're in     dim sht2 worksheet ' worksheet you've opened compare     set sht1 = wrkbk1.worksheets("sheet1")     set sht2 = thisworkbook.worksheets("sheet1")      dim row long, col long     sht2       row = 1 sht1.usedrange.rows.count           col = 1 sht1.usedrange.columns.count               if sht1.cells(row, col) <> sht2.cells(row, col)                   .cells(row, col).interior.colorindex = 5               end if           next       next     end      wrkbk1.close  end sub