Coding method? (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 18:13
Joined
Sep 21, 2011
Messages
14,046
Hi all,
Just curious after helping someone in another forum with this possible issue.
How do the experts here write their VBA.?

I myself would

Create object
Set object
Open object

then write all my code, then

Close the object
Release the object.

Same with If, Do and For, in that I do not write If, then the Endif and then insert code between those words. I would just write If statement, then what happens if true/false, and then the Endif at the end.

It has caught me out once in a while, but nothing to make me change now, and I'm a little to set in my ways at my age, plus I do not hardly ever code anymore.

Just curious as to how the experts who make their living from coding here do it.?

TIA
 

Ranman256

Well-known member
Local time
Today, 14:13
Joined
Apr 9, 2015
Messages
4,339
It all has to be done, so it doeSnt matter the order you write it.
Vb will tell you if you miss something.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:13
Joined
Sep 21, 2011
Messages
14,046
It all has to be done, so it doeSnt matter the order you write it.
Vb will tell you if you miss something.
I don't hink VBA will tell you if you have not released an object (perhaps not so important) or then try and open it again, without closing it?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:13
Joined
Feb 19, 2013
Messages
16,553
I tend to write the closing lines at the same time as the opening lines if I anticipate the code between is going to be long or contain more loops/ifs within the code. Just gives a skeleton to hang the code on and your shoulder doesn't end up being an elbow.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:13
Joined
Feb 28, 2001
Messages
26,999
It depends on whether I am expecting the code to be a really huge story that grows in the telling or whether it is just a nuanced little routine with a very narrow purpose.

When it is a tiny function I can usually grasp the whole thing in my head and write it linearly. Small is good here, and the "divide and conqure" approach that I usually take usually leads to that kind of coding.

When it is something I know will be non-trivial, I write the object creation and destruction sections first. Then in between those two pieces, I write multiple whole-line comments (i.e. apostrophe in column one) to represent the actions for each section of code. Then I go back and create each section. The point being my tired little old brain can still do things like parsers and complex processing - but I can't keep all of it in my head at once. My "mental image" is small-screen.

It's a good thing, though, that my shoulder doesn't end up being my elbow, 'cause if that happened, I couldn't scratch my butt when it itched.
 

Isaac

Lifelong Learner
Local time
Today, 11:13
Joined
Mar 14, 2017
Messages
8,738
in that I do not write If, then the Endif and then insert code between those words
I actually switched to where I DO do that, a few years ago. Too many times forgetting and having a hard time figuring out when too many nested pieces.

For example, if I am looping a recordset, I always write:

do until rs.eof=true

rs.movenext
loop

.......and THEN i write code in between.

I also write:

If something

End if
.....and THEN write code in between.

It looks funny to watch, but also guarantees I never make a mistake (that particular one, anyway).

Very interesting question thanks for posting!
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 14:13
Joined
Apr 27, 2015
Messages
6,286
Same with If, Do and For, in that I do not write If, then the Endif and then insert code between those words. I would just write If statement, then what happens if true/false, and then the Endif at the end.
I used to do this as well and 99% of the time I would forget to include the "closer". While watching the Steve Bishop videos I noticed that he used the technique you described and have adapted it ever since.

So simple yet it never occurred to me to me until I saw somebody else do it. I don't think I have had an original thought in me head since 1965...
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:13
Joined
May 21, 2018
Messages
8,463
It is kind of disappointing that there has been no upgrade to VBA to handle coding constructs and code blocks. Visual Studio does so much of this which make coding in VBA feel like 1980. I have been using MZ Tools which does some nice things, but still very limited.
 

Isaac

Lifelong Learner
Local time
Today, 11:13
Joined
Mar 14, 2017
Messages
8,738
even just intellisense on declared variables, i mean how easy would that be it seems!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:13
Joined
Aug 30, 2003
Messages
36,118
I use MZ Tools in part for the code templates. With things like recordsets, I just right click and it drops in all the parts at once. If I'm just writing something like an If/Then, I often add the Else and End If bits, then fill in the code. I don't always remember, but I feel it helps make sure I don't forget and hit a compile error.
 

Minty

AWF VIP
Local time
Today, 18:13
Joined
Jul 26, 2013
Messages
10,355
I spent 20 minutes looking at a non-functional 3 deep nested sub-query earlier today (SSMS) before I realised I had the final joins in the wrong place.

If I had written them out in order properly, I would either have not made the mistake, or spotted my error immediately.
Cut and Paste laziness cosy me 1/2 an hour... Dummkopf
 

Isaac

Lifelong Learner
Local time
Today, 11:13
Joined
Mar 14, 2017
Messages
8,738
Right now I've got a situation where I keep running an Update statement to remove text (set to "" or Null) on a single field, on an entire SP list. Very simple.
currentdb.execute "update tablename set fieldname=null",dbfailonerror
(tried a regular Query object too!)

It runs successfully, I open the SP list, and the field is not nulled out yet.

I have a feeling it's something ridiculously stupid or simple...but can't quite put my finger on it yet. Meanwhile the clock ticks LOL
Edit! I just figured it out. It's because the SP column is of the type enhanced rich text, which I know can't be edited in MS Access. And the dumb thing just allowed the Update statement to complete with no errors. Shame : )
 
Last edited:

Sun_Force

Active member
Local time
Tomorrow, 03:13
Joined
Aug 29, 2020
Messages
396
Vb will tell you if you miss something.

With all respects, VBE is just a joke.
Try this code:
SQL:
Function Test()
    
    Dim db As DAO.Database
    Dim Rs As DAO.Recordset
    
    Set db = CurrentDb
    sql = "SELECT * FROM tblEstimate WHERE unit is null"
    Set Rs = db.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)
    
    With Rs
        If .EOF Then
            MsgBox "test"
                  
    End With
    
End Function

I'm missing an End IF

This is the error message that Microsoft 365 gives me :

2021-01-14_9-22-07.jpg


And end with line is high lighted. The other day in a very long function, I wasted 2 hours on this error until I found out I'm missing End IF, not End With.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:13
Joined
Feb 28, 2001
Messages
26,999
Actually, that error is exactly correct.

What has happened is that you started a WITH sequence. Then you started but didn't end the IF sequence. That END WITH is not in the same scope-block as the WITH, so it doesn't match up to the WITH RS statement and therefore doesn't close that WITH sequence. An END WITH only closes a WITH at the same code level, which in this case you don't have. Then you hit END FUNCTION and the FIRST thing you hadn't closed was the WITH sequence, The SECOND thing you hadn't closed was the IF sequence. So the error handler told you the first error it found. You are correct that it didn't tell you the real reason - but that is life with ANY compiler that has block scope capabilities (like VBA does).

People don't think about this very often but the truth is that an IF/THEN/ELSE block is NOT repeat NOT in the same scope as the code immediately around the IF block. You can't put a label inside the IF block and transfer into it. You CAN jump OUT of the block with a GOTO - but not into it. When you see that, you know have a micro-scope issue. If you have a lengthy IF block, you CAN put a label in the sequence and jump to it from inside the IF block (with further restrictions). Each IF, ELSE, or ELSEIF terminates a sub-block and starts a new sub-block. Only an END IF terminates an IF block with out starting a new block.
 

Sun_Force

Active member
Local time
Tomorrow, 03:13
Joined
Aug 29, 2020
Messages
396
@The_Doc_Man to be true, it's confusing for me. But regarding to your experience in VBA compared to a student (me), you are possibly right.


In case of VBE, Is it always this way? I mean any error within With clause, directs the error to With and not the error itself?
 
Last edited:

Sun_Force

Active member
Local time
Tomorrow, 03:13
Joined
Aug 29, 2020
Messages
396
@The_Doc_Man One more question if you don't mind. What if I have a very complex function with multiple nested IFs, Do Whiles, etc.
If only one of them is not closed correctly and VBE sends the error pointer to With Statement, How can I find what causes the error?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:13
Joined
Feb 28, 2001
Messages
26,999
In case of VBE, Is it always this way? I mean any error within With clause, directs the error to With and not the error itself?

Not necessarily. There are other reasons why an isolation block fails to close. But you get errors for the FIRST thing not properly closed regardless of why it wasn't closed. IF/THEN/ELSE/END IF blocks are probably the most common cause of this, but certainly not the only cause. i would imagine certain types of loops would have this effect. Overlapping isolation blocks (that overlap on one end only) would also sometimes have this effect. You are isolating code with things like WITH/END WITH or DO/LOOP UNTIL or SELECT CASE/END SELECT or IF/END IF. (Though you CAN jump into a WITH block in some cases - but not the others.)

I call them isolation blocks because there is a small block of code between the starting and ending parts that is isolated with respect of the rest of the code. If it is one of the cases where you are forbidden to jump into that block then it is in some manner isolated from the code around it. And there is the most likely place to have an error of this type.

What if I have a very complex function with multiple nested IFs, Do Whiles, etc.
If only one of them is not closed correctly and VBE sends the error pointer to With Statement, How can I find what causes the error?

To prevent this sort of thing, I developed a habit a long time ago where I coded from higher scope to lower scope.

If I had a WITH block, I wrote the WITH starting statement, then the corresponding END WITH on the next line, Then I filled in the middle AFTER I had the block endpoints defined. I do my IF/END IF sequences the same way. Build the ends and THEN build the inner content. Yeah, I know, it's sounds like a real pain in the patootie - but it prevents a lot of "dangling block" errors.

As to how you find the error when you have one - the first thing to do is recognize when you are starting a block - and when you are starting another block that becomes nested. Consistent program indentation is a trick that helps you recognize when you have an issue because if you TRY doing a consistent indentation and it comes out wrong, you have found the tail-end of the error.
 

Users who are viewing this thread

Top Bottom