Nested With Statement hierarchy

Question

I've come across this a couple of times recently and was just curious if there was an easier way to do this...

With Activeworkbook
  'Do Stuff
  With .Sheets(1)
    'More stuff Done
    '...
    'But now I need to refer to or pass the Sheet in the last With Statement
    SomeFunctionAnswer = SomeFunction Activeworkbook.Sheets(1)
  End With
  'Yet more stuff Done
End With

Does it have to be fully written out, or is there some way of notating it that makes it easier/cleaner? Perhaps there is some sort of property or method to pass itself for just this instance? What about referring to a property or method from the higher With?

SomeFunctionAnswer =  SomeFunction .Self  '???
'OR
SomeFunctionAnswer =  SomeFunction .Parent.Name  '???

Hope that makes sense...

Answer 1

... curious if there was an easier way to do this... Does it have to be fully written out,...?

I too have had this problem; an example is transferring data from a TYPE structure to a database RST structure, where I would like to use the dot-notation in the innermost With/EndWith statement block.

I came up with the following:

Function AddTypdataToDB(dbs As Database, typData As typAddressInfo)
    Dim rst As Recordset
    Set rst = dBase.rstOpenRecordset(dbs, "SELECT * FROM Master")
        rst.AddNew
        With typData
            rst.Fields("Amenities") = .strAmenities
            rst.Fields("BusinessName") = .strBusinessName
            rst.Fields("CountOfBathrooms") = .strCountOfBathrooms
            rst.Fields("CountOfBeds") =
            rst.Fields("CountOfGuests") =

Here, I am partway through writing the VBA program code with my least-effort mechanism to date:

1. I typed in the first assignment (“amenities”) in full.
2. I copied that full statement to the line below, ...
3. ... stripped away the .strAmnenities part and then ...
4. ... copy/pasted that skeletal line many times.
5. From the Access MDB table definition, I copy/pasted the field names (Alt+Tab between the two windows)
6. Finally, I typed a period after each successive equals sign, and scrolled to the appropriate field name from my TYPE structure.

This sounds complex when I write it out in English, but it is the fastest error-free way I have found to generate code manually. I have eliminated the outer WITH statement and used explicit RST code, but the bulk of that is copy/pasted.

Not only do I reduce the elapsed time to generate a block of VBA code for my AddRecord procedure, but I save time by not having to correct the myriad errors that creep in when I code in “long-hand”.