13

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

Cœur
  • 37,241
  • 25
  • 195
  • 267
Rdster
  • 1,846
  • 1
  • 16
  • 30
  • 2
    When I make my own classes, I often expose a simple `Public Property Get Self() As [WhateverTheClassIs]` and make it `Set Self = Me`, specifically so I can use it in statements like `With New SomeClass` and implement a factory method on the default instance (requires editing the exported module with a `VB_PredeclaredId = True` module attribute, then re-importing it back into the project). But for built-in classes, it's "nope". =) – Mathieu Guindon Jan 23 '17 at 21:10
  • @Mat's Mug: Hey! You just taught me `VB_PredeclaredId = True` , thanks, that's a gem. I can now run classes via the Immediate window, wow. – S Meaden Jan 24 '17 at 16:44
  • @SMeaden you're welcome! I have this empty `StaticClass.cls` class module in my documents with that attribute set to `True` so whenever I need a "static class" I just import that module =) ...it's double-edged though; a `UserForm` class has that attribute set to `True` as well - that's how you can show a form without instantiating one... which isn't a best-practice; objects are *meant* to be instantiated! I usually keep those for factory methods. – Mathieu Guindon Jan 24 '17 at 16:54

4 Answers4

17

The answer is a plain and simple No No.

The With clause facilitates the access to the members and methods of its subject, but it does not provide any facility to reference the subject itself. When that's needed, you have to write the name the object completely or refer to it by other means.

When accessing methods and members of an object that is the subject of an outer With clause, again, you need to name it completely. The inner With, and for the whole of its scope, completely hides the outer With.

Therefore, the way you wrote your code is the correct way to do it.

A.S.H
  • 29,101
  • 5
  • 23
  • 50
1

You could move it out of the inner with and into the outer with. Then you could drop the Activeworkbook qualification.

With Activeworkbook
  'Do Stuff
  With .Sheets(1)
    'More stuff Done
    '...
  End With
  SomeFunctionAnswer = SomeFunction .Sheets(1)
  'Yet more stuff Done
End With
J. Garth
  • 783
  • 6
  • 10
  • That's fine and great if you don't have other lines that need the inside With after it. Obviously, if you don't need to use the inside With, you'd just move the line to the outside one. – Rdster Jan 24 '17 at 12:51
  • Ok. I was just going by the example you posted. In your example, you have stuff to be done before the function call and then nothing after it. I wasn't considering scenarios that you didn't delineate. I thought your question was about this specific scenario. I didn't know it was meant to be more of a generalized discussion. – J. Garth Jan 24 '17 at 14:49
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”.

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Chris Greaves
  • 11
  • 2
0

Perhaps easier to just assign to a variable, but here is one approach:

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 Sheets(.name)
  End With
  'Yet more stuff Done
End With
SJR
  • 22,986
  • 6
  • 18
  • 26
  • In order to fully qualify this though, you'd need to use `Activeworkbook.Sheets(.name)` which ends up being even longer. – Rdster Jan 24 '17 at 12:54
  • In this specific example, you don't need to qualify it as it is the active workbook, but I agree that in general this approach is a bit of a dead end. – SJR Jan 24 '17 at 15:09