VBA testing

[dk]

...The other thing to remember is that we don't have every piece of information in our heads. You should perhaps have something that determines thier searching for answers skills? Ie. Using the Online help, web sites, etc... If I don't know how to solve a problem now, I can most likely find a solution within a couple of hours using my other resources.

HTH
Cal

Very true. I use the help files, Google Groups, the MSDN, books constantly and I agree that the ability to get an answer, even when one does not know it off the top of their head, is an indispensable skill. However, at the same time I needed to test the individual's own knowledge. I should mention that I set no time limit for this test. If I had said to the candidate "here's a laptop, here's an internet connection" then given enough time they could have answered all the questions, and I'm not sure if that would have given me the measure I wanted.

Regards,
Dan

 

[Legacy 5624]

Exactly.
It's all about knowing how to find a solution.
If everyone were competent in this respect, there would be a greatly reduced need for this board.
Unfortunately, everyone isn't.

 

[Norie]

OK, I guess that would pass but then I'd punch the interviewee in the head for **** poor programming technique

Which Excel/VBA sort of condones.

I learned how to program using Turbo Pascal, C, Prolog, Java and machine code (Z80 on a Sinclair Spectrum and later 8080 on a PC).

All of which are very unkind if you don't do things correctly.

I always use Option Explicit and try to name variables/objects using the proper conventions.

But sometimes if you just want a piece of code that is only going to be used once then it is easier (sloppier) to ignore the conventions.

I know it's probably the wrong way to do things but when somebody asks you for something "immediately" what choice do you have.

I once worked in the insurance section of the finance department for a large utility company and their IT department told them that it would take 6 months for them to create the database they required.

I was able to produce what they wanted in 2 weeks - including Help files and documentation.

It wasn't perfect but it did the job, and then I was asked by other sections of the department to create databases for them.

BTW I've found that the Help files for Access/Excel VBA can be quite helpful - I just think that you need to know what you are looking for.

 

[Greg Truby]

Hi dk,

I thought the content of the test quite appropriate given the circumstances you describe. I'd score myself as getting 7.5 out of 8. A couple of thoughts...

Q#1. I would call this "easy". If the candidate has done very much VBA Excel programming, I would expect them to have had an integer/row variable blow up in their face at some point. Furthermore, I would hope that the candidate would call me to task for looping instead of using Autofilter & SpecialCells.

Q#2. I don't use SQL frequently enough to be rock-solid sure of myself. The answer I jotted down was on the money, except that I didn't have the ALL keyword. But I knew it would be a UNION query and I know exactly which chapter in my Ken Getz book that I'd look in for help. So I have myself 1/2 point on that one.

Q#3. At first I thought it a bit tough to be included. But on thinking more about it, even if the candidate had not created an application class module, I would hope that he had at least used a class module to handle groups of controls on a user form, where he would have learned about the WITHEVENTS being required and could guess that this would be the case for your example. [I might even have included a fourth option using the Public keyword without WithEvents to make it harder to simply guess based on option "c" being the only one that declared it as Public.]

Q#4. I guessed on LenB(). I don't know how many people ever use the --B() versions of Len() or Char(). It's one of those functions that I know are out there because I've read about them; but never had need to use them.

Q#5, #6 & #7 - All were "easy" questions in my book. I'd probably scratch off any candidate that couldn't answer these.

Q#8 - I think it's a fair topic. However, this is not my strong suit so I made an educated guess about what you were expecting to hear. But the question left me wondering if late binding is better or whether one would be better off trying early binding, adding the reference from the GUID and setting the Major argument to the lowest possible version. My guess is that error trapping and handling might be easier if I could early bind. Am I wrong about this? [Seriously -- I'm hoping for enlightenment here. As I said, this is not my forté and I'm always eager to learn more about the advantages/disadvantages of early vs. late binding in various situations.]

Like the rest of the gang -- hope you find/found someone to fill your shoes while you're on holiday.

 

[XL-Dennis]

Hi Dan

Well, You already got some good points so I will not add more to the general impression except that I find Tom's points to be very good and interesting to which I agree with.

Several questions are not complete in that they explicit gives the whole picture so to speak.

A typical one is the #7
In general there is no guarantee (as we all know) that code will actual work

Using LBound is a lazy (and poor) coding when not consider the actual circumstances which means that 0 to UBound(....) can work very well too.

For #8 there exist an additional alternative if You have all the versions involved available on the developing computer(s):

c) Use early binding and set a reference to the earliest version the solution should be working with.

#5 is not a good question due to the lack of problem-solving.

 

[dk]

Hi Dan

Several questions are not complete in that they explicit gives the whole picture so to speak.

A typical one is the #7
In general there is no guarantee (as we all know) that code will actual work

Using LBound is a lazy (and poor) coding when not consider the actual circumstances which means that 0 to UBound(....) can work very well too.

I disagree that the question is incomplete. Of the 4 answers, only 2 are viable (answer a and b). Answer b will ensure that every element is looped through whereas a would not if the first index was anything other than zero. To state that "LBound is a lazy (and poor)" is a bold statement. How about if you wanted a function which simply accepted an array and performed an operation on it, and returned the processed array? If you wrote this function to be robust then you would HAVE to use LBound and UBound, unless you make the assumption that the array being passed is always zero based. If you pass an array of values from a range then it will be 1 based.

For #8 there exist an additional alternative if You have all the versions involved available on the developing computer(s):

c) Use early binding and set a reference to the earliest version the solution should be working with.

The scenario I was working with assumes that the workbook in question will be shared amonst other people using different version and each user will need to save the workbook. Try this simple test. Put this code into a module and set a reference to the earliest version of Outlook available (call this machine A).

Sub TestOfEarlyBinding()
Dim olApp As Outlook.Application
Dim olMsg As Outlook.MailItem

Set olApp = New Outlook.Application
Set olMsg = olApp.CreateItem(olMailItem)
olMsg.Subject = "Test"
olMsg.Display
End Sub

It should run without problem. Now open the same workbook on a machine with a later version of Outlook (call this machine B). The reference should be updated to the later version of the Outlook object library. Save the workbook. So far so good. Now open the workbook on a machine with an earlier version of Outlook than that of machine B (which does not have multiple versions installed). What happens to the reference? It goes AWOL! This is the behaviour I've witnessed whilst distributing Access databases and Excel workbooks within our organisation and the reason I would opt for late binding. However, if the interviewee was to provide an argument such as yours then they would still get the job

#5 is not a good question due to the lack of problem-solving.

I take it you didn't get this one right :wink: A completely valid question in my opinion related to the job at hand. To avoid hard coding addresses I often use code such as this:-

            For lngCol = consts.COLUMN_HISTORY_FIRST + 1 To consts.COLUMN_HISTORY_LAST + 1 Step 2
                shtBudget.Cells(lngRow, lngCol).FormulaR1C1 = "=IF(R" & lngPerUnitRow & _
                        "C[-1]=0,0,RC[-1]/R" & lngPerUnitRow & "C[-1])"
            Next lngCol

The ability to understand R1C1 formulas and to convert between A1 and R1C1 is therefore very relevant to this position.

Thanks for your feedback Dennis. And I'm glad to report that I did find someone to fill the position.

Dan

 

[XL-Dennis]

Dan,

I still disagree and the mainexplanation is propably due to the different approaches we take when it come to work with different task.

I nowadays split the code and the data from each other which means that saving is only related to the data involved and never to the code.

So You scenario will never happend in my case

As for #5 I believed i learned that when version 2.0c of Excel was shipped in 1987 or was it 2.0 d

LBound is still no good coding practice as this can be controled by the use of Option Base as well related to if we work with arrays that read data from ranges or not

 

[NateO]

Hello,

LBound is still no good coding practice as this can be controled by the use of Option Base as well related to if we work with arrays that read data from ranges or not
{snip}

I thought we already determined that Option Base is a very risky approach to iterating through arrays:

http://www.mrexcel.com/board2/viewtopic.php?t=78319&start=11

You're safer [and more dynamic, as the question put it] using LBound().

 

[dk]

Dennis my man,

OK, let's agree to disagree on the other points. We obviously have different approaches to tasks and many problems have many possible solutions.

LBound is still no good coding practice as this can be controled by the use of Option Base as well related to if we work with arrays that read data from ranges or not

On this one, I do disagree. Option Base has no effect when creating an array from a range:

Option Base 0

Sub Test()
Dim vArray()
vArray = Range("A1:A10").Value
MsgBox LBound(vArray)
End Sub

All the best,
Dan

 

[Greg Truby]

Dan (or anyone else that can answer),

Thank you for discussing the issue of binding and your example on Outlook. My interest in the question is more than academic. I've written an add-in that is used by a few dozen people in my company. I set references using early binding. Here's a snippet of the code from a function that is called by the Open event handler to make sure that everything is in order:

<font face=Courier New>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>

    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> rngRefs.Rows.Count
        strName = rngRefs(i, 2)
        strDesc = rngRefs(i, 3)
        strGUID = rngRefs(i, 4)
        intMajor = rngRefs(i, 5)
        intMinor = rngRefs(i, 6)
        <SPAN style="color:#00007F">Set</SPAN> objRef = DataScraperWorkbook.VBProject.References
        objRef.AddFromGuid strGUID, intMajor, intMinor
        <SPAN style="color:#00007F">If</SPAN> Err.Number <> c_lngErrAlreadyRefd And Err.Number <> 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> ErrorHandler
    <SPAN style="color:#00007F">Next</SPAN> i
    
    CreateReferences = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>

I thought that by using the GUID and controlling the major version and using error checking that I would be able to exit more gracefully should the needed references not work out. Do I have an Achilles heel here that I'm not aware of? Couldn't you do this with the Outlook example you cited?

{Edit}And before any of you wiseguys point it out, i is an integer, but Heaven help me if I ever have to set over 32,000 references!{EndEdit}