A worksheet (or a Word document) is nothing more than an object encapsulating state / data.
You could go out of your way and wrap the worksheet/document with an interface that your code would depend on (e.g. IWorksheet, or IDocument), but that would be a tremendous amount of effort, for indeed little to no benefit - the unit tests would have to be using a "fake" implementation of that interface, which would be responsible for storing the test data/state so that your tests can assert that the code you're testing is working as intended. Complete overkill.
Instead, write your code so that it's given a Worksheet instance (i.e. avoid working against ActiveWorkbook and/or ActiveSheet), and does whatever it needs to do with it. Split up responsibilities so that when you invoke a method you don't have 20,000 things to assert to ensure your code does what it's written to do - but that shouldn't be anything new or different from what you're already doing, right?
'@Description("Adds a table named [tableName] on [sheet]. Returns the created table.")
Public Function AddTable(ByVal sheet As Worksheet, ByVal tableName As String) As ListObject
'TODO: implement
End Function
A test for such a method might look like this:
'@TestMethod
Public Sub AddsListObjectToSpecifiedWorksheet()
'Arrange
Dim sheet As Worksheet
Set sheet = ThisWorkbook.Worksheets.Add
Dim sut As MyAwesomeClass
Set sut = New MyAwesomeClass
Const tableName As String = "TestTable1"
If sheet.ListObjects.Count <> 0 Then _
Assert.Inconclusive "Sheet already has a table."
'Act
sut.AddTable sheet, tableName
'Assert
Assert.IsTrue sheet.ListObjects.Count = 1, "Table was not added."
sheet.Delete
End Sub
The sheet setup & cleanup code could be moved to dedicated TestInitialize/TestCleanup methods in the test module, as every single test method in that test module is likely going to need a fresh worksheet to play with, because you'll want every test to be independent and not share any state with the other tests.
Extracting the setup & cleanup code to dedicated methods in the test module removes fluff from the actual test methods. After all, a test module is a standard module that can have its own private fields and module-level constants:
'@TestMethod
Public Sub ReturnsListObjectReference()
'Arrange
Dim sut As MyAwesomeClass
Set sut = New MyAwesomeClass
If testSheet.ListObjects.Count <> 0 Then _
Assert.Inconclusive "Sheet already has a table."
'Act
Dim result As ListObject
Set result = sut.AddTable(testSheet, tableName)
'Assert
Assert.IsNotNothing result, "Table was not returned."
Assert.AreSame result, testSheets.ListObjects(1), "Wrong table was returned."
End Sub
So you keep writing tests, each verifying one specific bit of behavior:
'@TestMethod
Public Sub TableNameIsAsSpecified()
'Arrange
Dim sut As MyAwesomeClass
Set sut = New MyAwesomeClass
If testSheet.ListObjects.Count <> 0 Then _
Assert.Inconclusive "Sheet already has a table."
'Act
Dim result As ListObject
Set result = sut.AddTable(testSheet, tableName)
'Assert
Assert.AreEqual tableName, result.Name, "Table name wasn't set."
End Sub
That way when you, future you, or whoever inherits your code looks at your test suite, they'll know exactly what your code is supposed to do, and by running the tests they'll know that your code does what it's intended to do.
Whether you want a test that breaks when the code is modified to make the tables have blue borders instead of green, is entirely up to you and your requirements.
In your specific case involving an INI file, IMO the "file" part is an implementation detail, and you wouldn't want a unit test to depend on some file somewhere on the network. Instead, you'll have a class or data structure to hold the configuration key/value pairs; the test's "arrange" part would be responsible for setting up the configuration data, and when you "act" you pass the configuration to your SUT and then assert that the resulting state matches the specified configuration.
The code that reads/writes the actual INI file would be another concern entirely, with its own test code, which would also avoid hitting the file system: you want to test your code, not whether the Scripting Runtime's FileSystemObject does its job.
Note that whether AddTable is a member of MyAwesomeClass or some utility standard procedural module makes absolutely no difference as far as testing is concerned; unit tests don't tell you how you regroup/abstract functionality and organize your code.
The latest version of Rubberduck (pre-release 2.1.x builds) include the beginning of a "fakes/stubs" framework that can be setup to intercept a number of specific standard library calls, by hooking into the VBA runtime itself. For example you don't want a unit test to pop a MsgBox, but if the method you're testing requires one, you can intercept the MsgBox call while the test is running (and even setup its return value, e.g. to simulate the user clicking on [Yes] or [No] or [Cancel]), but that's another topic entirely.
