Excel VBA Programmer Interview

[fredlo2010]

Hello guys,

I have been around this forum for a while; also I have studied on my own. So its safe to say I am an ok Excel programmer. I have been taking some programming classes at school and I am on my way to get my Bachelors in Computer Science, application development (I have not taken the major courses yet)

So a few days ago I applied for a job online and they want to hire me. I am extremely scared. This would be my first job as an Excel programmer.

For the professionals out there, what do they usually ask in this kind of interviews?

How much do you need to know say I am able to do this job? I mean I can do most of the things, but is it ok if I look things up online or use a reference book; mostly I know where to look, its not like I am looking for something totally blind.

Is using Macro recording to get some method and properties considered cheating? what if you supervisors sees you going online to research something?

Thanks a lot for the help

[SamT]

If they know your history, then they know that you are a noob.

What you are not is an OK Excel /VBA programmer. You are a noob. Own that fact. Make yourself say it in the mirror every day before work. Accept that you will make mistakes. Say that in the mirror too.

After about 1000 hours programming, you can graduate to Newbie status. :mbounce2:



I have maybe 3000 hours in VBA for Excel. On an apprentice scale of 1 to 7, that only makes me about a a Level 4 Apprentice Excel Progmammer and still a noob at all other Office programs. However those 3000 hours will carry over as soon as I accumulate another 1000 hours in all the other Office applications. That would make me a Level 4 Apprentice MS Office programmer. Note that that does not make me qualified to design worksheets, 'cuz "I know nuttink" about Excel Worksheet Functions.

Studies have shown that it takes about 10,000 hours of practice, doing, and study to become an expert in any field.





The Employer (should) know that you are still a noob and take that into consideration, by putting you into a team of more experienced staff.

When you go to work always remember the noob golden rule: "If you be talkin', you ain't learnin'!"

Show up on time. Respect your elders,even when they are wrong... Make that "Especially when they're wrong." Be willing to work at things you hate. spend at least 10 hrs a week of your own time learning. (those hours add to your 10,000 needed to be an expert.) Just those simple things will make you a very valuable employee.

If you do find that you are Solo on the job, dig in. Don't ever give up. And spend those 10 hrs a week of personal time working the job's problems. They still add to your 10,000.

Good luck and :beerchug:

[fredlo2010]

Thanks a lot for the advise Samt.

I think it will be hard to tell the employer I am a noob. I was wondering what would be a good way to put it. " I am still learning", "there are a lot of things I still don't know" ... and how to make it better with things like "If I don't know I can research about it" or "I could use a reference book"

I don't know all of the sudden I feel like I am not qualified for the job lol. Maybe I need to mature more.

I think it will be a solo job, which already makes it harder. Although I am supposed to have an interview with IT personnel.

Is there a way to inquire about what they expect?
For example : Its not the same if they want me to design a report for a few employees or if they want me to create a big addin to be implemented in Excel with ribbon buttons and all (I don't know a lot about that)

As per practice I am always looking for things I can VBA automate in my office, first to make my job easier, second to learn more and sharpen my abilities.

Thanks a lot for the help. It's good indeed not only for this job but for life in general as I forge my path as a programmer.

[SamT]

Does the employer have your resume or at least work and education history? Then he already know that you are a noob. That menas that he already thinks that a noob is what he needs. And That means you can do the job.

Relax. So what if you don't land the very first offering that came along?

The only thing you must do to guarantee that you DON"T get the job is to tell a lie. All interviewers have "The Nose."

They expect someone who has studied and done the things you said that you have.

Don't worry about it. If you find that you don't know exactly what to do, find out How To. Google is your friend. We are your friend.

All you need is a little help from your friends. :friends:.

We will:whip you when you need it. We will :hug:when you're down, and we will:bigkiss: when you get that raise. Then we will make you buy the:beerchug:


:devil2:

[fredlo2010]

Yes they have my resume.

And yeah its very clear this would be my first purely programmer Jobs. All I have done is make my current admin job better using Excel VBA. Of course I routed it in a way that shows me as a programmer and not an admin.

Instead of "Invoice customers making sure prices are correct" , " Created a spread sheet to import invoices and check for correct prices and find errors"

Thanks a lot for the support and the extended hand. Its really appreciated. :) I will make sure to come back and keep you guys updated on the outcomes.

[Zack Barresse]

Well good luck! I always tell people I'm still learning - because I am. Generally I find when explaining to people what I can/not do in technologies is to keep it broad. Tell them I can work with cells/worksheets/workbooks, tables, PivotTables, create add-ins, modify command bars & the ribbon, charting/visualization, interactivity in other Office applications, etc. Someone conducting the interview will most likely have some goals they're looking to achieve, so they will drill down specific questions.

Things you should know:

You'll never know it all (nobody does)
You'll learn as long as you want to
You can never remember everything (everyone needs resources)
Most people who think they're "good" in an app, aren't
Always be modest
Always be honest
Always over-perform
Always over-deliver
Show respect to others
Always demand respect from others
Always say please and thank you
Always smile - it's contagious


In any case, good luck to you!

EDIT: Forgot to answer those last two questions as well.

"Is using Macro recording to get some method and properties considered cheating?"
I sure as hell hope not, otherwise consider me busted! Sometimes it's the easiest way to get a syntax or object method. It's not cheating at all.

"What if you supervisors sees you going online to research something?"
Doesn't seem relevant to me. I go online all the time. Usually it's for finding a syntax, checking an objects properties, syntax limitations, etc. Perfectly ok. :)

[fredlo2010]

Thanks a lot Zack. I have have been expecting your insights on this (as well as other member's) :) After a while browsing the forum you learn to trust certain people :).

Its awesome to you decided to stop and comment.

I have a phone interview on Monday.

[Zack Barresse]

You're very welcome! I'm not the best by any means, and I'm sometimes cantankerous, so take it for what it's worth. ;)

Something I should have added to the list is confidence. Be confident in your abilities as well as your inabilities. Every once in a while I get asked what makes me an expert. It's not like there's a degree for Excel. I usually tell people, "because I said so." After I get a funny look I tell them I've been doing this for quite a few years, helped thousands of people, received some awards, etc.

Another good piece of advice I got from someone a while ago is accept failure, it will define your success. :)

[fredlo2010]

lol Thanks for the extra tips :)

Yes, for a split of a second I thought I was not prepared for this. I almost did not click the "Apply here" link. But I am glad I did; and no matter what the actual work outcomes are...I have learned a lot from all of you guys. Its something I will always have with me.

:) :) :) Thanks to you All :) :) :)

[fredlo2010]

OK so I had a phone interview today.

I think it went well. I was asked how much of Excel I know and how much VBA I know. I was asked about functions and SQL to the second one I answered that I did not know.

Apparently they had people in the company that know some VBA and they have been working half time as admins and other half as VBA programmers. They are looking for someone that would take ownership over the programming part. I asked about code structure and documentation and and he replied that the only documented code was pretty much his. (This was the funny moment of the interview, which loosened tensions) He told that the worst codes where done by people who just recorded the macros. (This is good because formatting is a fairly easy part of VBA)

He also asked about Virgin Control and I said I did not know and he said that if anything its something to talk about.

He scheduled a test for tomorrow. (which is a good sign according to the staffing agency) I asked him about the test and he told me not to worry that its a VBA test not one of those Excel where "you have to know under that tab is what command in condensed ribbon" he confessed that if he had to do one of those he would fail. (another round of laughs)

I have one hour tomorrow at 2:00 pm to complete. What kind of questions are asked in these tests? Has anyone done one like that? Any must-know thing?

Thanks a lot

[Zack Barresse]

Glad to hear it went well!

I would assume they would ask more specific questions tomorrow, probably based around processes they've developed or are working with currently. Assuming they're working with SQL back end(s?) or making connections, perhaps they're doing this via VBA and manipulating/creating queries to return data. There are a dozen different ways to do it, so can't really say one way or the other. Perhaps they'll ask you to debug some code? Maybe write some? Or perhaps just ask you about how you would do things?

[fredlo2010]

Thanks Zack I will post tomorrow. In the meantime I will google my question with some luck something will come up.

[SamT]

It sounds like the perfect job for a beginner wanting to become a programmer: Clean up and document a bunch of Macros is an excellent way to learn VBA and see the standard approach to a wide variety of problems.

You might think about getting some 8x5 cards and make notes of each macro name, it's input and output names, and what it does. It won't be long before you start to perceive some patterns in the way all the Macros work together. I would sugest that you buy an artist's sketch book (24"x36") and as soon as you get home, sketch out those patterns you think you see. Eventually they will all come together into something you can call a system.

Then... you can start Refactoring them into a real program.

You should substitute that method that helps you see the over all picture for my artist's sketchbook.

[fredlo2010]

Thanks for the advise SamT :)

[fredlo2010]

Hello guys,

OK I had my test today. I think it went well.

These are some of the questions:
1. Open a .csv File and convert the contents to columns with a pipe delimiter.
This was my answer to this question. By the way I sort of freaked out. I did not know I could not import .csv with workbooks.opentext lol


Sub ImportData()

Dim wbk As Workbook

Set wbk = Workbooks.Open(Filename:="C:\Users\Alfredo\Desktop\data.csv")

Range("A1").CurrentRegion.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, OtherChar:="|"

End Sub


2. Explain the difference between Class, Module, Subroutine and Function.


A class is used to create custom objects
A module is a container to store the subroutines
A subroutine is a series of cod that execute a specific action.
A function is a specific type of subroutine that returns a value


3. Whats a named range and how to reference it in VBA
A named range is a name that can be given to a range in excel and it can later on be used to reference a group of cells and used in calculations.
In VBA you reference a named range as

Range(“MyNamedRange”)
Or
[MyNamedRange]

4. A workbook contains just one sheet named "Sheet1". Give three possible VBA code examples on how to Debug.Print the name of that sheet.

Sheets(1).name
Sheets(“Sheet1”).name
Sheets(thisworkbook.sheets.count).name
Sheet1.name


5. What would be the debug output of calling the TestPrintOutput subroutine
The output would be “John” the sub “TestPrintOutput” passes the variable name to the other sub “SayHello” and returns the new variable the name.There might be an error in this code if the desired result is “”Hello, John”


Public Sub TestPrintOutput() Dim name As String
name = "John"
SayHello name
Debug.Print name
End Sub

Private Sub SayHello(ByRef theName As String)
Dim name As String
name = "Hello, " & theName
Debug.Print theName
End Sub

6. In the included Sample.xlsm file is a module named "Test" with a subroutine named "Question6". Are there any changes you would make to this subroutine/module? If so, why?
It uses selections with is never good there is no need to select the cell to work with it. Also there are better ways to find he last row in a data range. Declarations of variables are usually at the beginning to improve readability. Also the last variable was incorrect. It would be good to use option Explicit to make sure all variables are declared and used.

The original code:

Sub Question6() Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select

Dim startTime As Date
startTime = Now()

For i = 2 To Selection.Rows.Count
Debug.Print Cells(i, 2)
Next

Debug.Print Now

Dim endTime As Date
endTime = Now()

Dim timePassed

timePassed = DateDiff("s", startime, endTime)

Debug.Print timePased & " seconds have passed"
End Sub


The fixed code:

Option Explicit
Sub Question6()

Dim i As Long
Dim startTime As Date
Dim endTime As Date
Dim timePassed As Long

startTime = Now()

For i = 2 To Range("A1").CurrentRegion.Rows.Count
Debug.Print Cells(i, 2)
Next

Debug.Print Now

endTime = Now()

timePassed = DateDiff("s", startTime, endTime)

Debug.Print timePassed & " seconds have passed"
End Sub

7. In the included Sample.xlsm file there is a sheet named "Sample Data" that represents a key/value pair. column A is the key where column B is the value for that key. Write a new function named "Question7" in the "Test" module. It must accept a number as a parameter, look up that number as the key, and return the value for that key.

This question freaked me out too when I read it and I saw "Items" and "Keys" I was oh no they want me to make a collection, something I not too familiar with...ok I will admit it Arrays and collections are extrememly hard for me. lol But kept calm and I hoped the intellisence would help me ( was reading about scripting.dictionary last night)

This was the code I came up with:

Function Question7(iNumber As Integer) As String

Dim lRow As Long
Dim rFound As Range

lRow = Sheets("Sample Data").Cells(Rows.Count, "A").End(xlUp).Row

Set rFound = Range("A1:A" & lRow).Find(What:=iNumber, LookIn:=xlValues)

Question7 = rFound.Offset(0, 1).Value

End Function

After I finished we spoke for a little while and he told me that for this question he was expecting me to use worksheetfunction.vlookup() but that thats just what he thought that my approach was correct.
He wanted a single line code like this:

Function Question71(iNumber As Integer) As String
Question71 = WorksheetFunction.VLookup(iNumber, Sheets("Sample Data").Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row), 2, False)
End Function

So thats all. He told me he was happy with the results. He was also happy that I finished the test a little before time was up ( not a lot lol 5 min).

Then he went on asking about availability for a second interview. He didn't tell me there was going to be one yet. But I think things look very good.

Once again thank you guys for all the advise and support. I will keep you posted.

[SamT]

ok I will admit it Arrays and collections are extremely hard for me.You absolutely, positively must get past that.

This will be just enough to get you started. BTW, you used a Collection 4 times is question 4 on your test.

A Collection is the easiest of the three. Basically, you just throw Objects in and take them out with a For...Each. They are automatically assigned a number, (Long,) starting at 1, as the Key. A collection is the only one of the three that you can use a For... Each loop with. Collections are one of the advantages VBA has over other languages. Collections are generally used when you don't care about the order you put things in, so you usually don't bother with a Key. A bucket if you wish. Actually it's more like a endless tube you fill from the top and empty from the bottom. A Collection is an Object that Microsoft made for us to use.

They are easy to build
Dim I-OControls As Collection


An Array is finite block of RAM Memory that the VBA Compiler sets up for our use, but you can also think of it as of boxes laid side by side, but you have to set all the boxes that you think you will need at the time you build the Array, (so if you don't know how many boxes you need, you should use enough to handle any contingency, it's only Memory.:D) An Array only has Indices, (Actually: Memory offsets,) and Values. The Indices are numbers starting at 0 and going up as needed to ID all the boxes. (Count - 1)

When you declare an Array, you have to tell the Compiler what Variable Type you are using and how many possible units you want to store. The Compiler will use the Variable Type memory requirements and unit count to assign the Memory block.
Dim PhoneNumbersUSA(100) As String(17) '100 * (17 * (Bytes/Character))
'Note that MS Memory management is excellent. Don't use fixed length strings. MS does it better.
If you want the first Value stored in that Block of Memory, don't use an Offset. Use 0 for the Index number. If you want the third value, then offset 2 from the start of memory. Now you know why Array indexes start at 0.

A Range is like a Photon, It's a Collection and an Array, depending on how you look at it. :fainted:

A Dictionary is another Object MS made for us. It is a wired combination of a collection and an array. Like an Array, you have to loop thru it by Index Number
For i = 1 to Dict.Count Like a Collection you can retrieve Items by Key. Uniquely, you can use Objects as the Key. Unlike an Array, it can't hold UDTs.

A Dictionary is built with two Arrays (Keys and Items) with linked indexes, such that the index# of the Key array is passed to the Items array for retrieval.You can load (Add) the Keys Array now and randomly load the Items Array later by referencing the Keys' Values

[fredlo2010]

Thanks a lot for the detailed explanation SamT. I understand the basic concepts of the arrays, the problem comes when I have to implement it in code. I have seen in forums all around the web and programmers use arrays to get data from the sheet and then they manipulate it, expand it using redims and them just put it back on the sheet.

Right now I am trying to grasp the concept from This Thead Post #8 (http://www.ozgrid.com/forum/showthread.php?t=184772&page=3) I mean this guys solved this using arrays and dictionary.

Thanks a lot for your post again SamT. :)

[Kyle234]

I agree with Sam, collections and dictionaries are really useful in VBA due to the poor features of arrays, but a bit of clarification if I may,:


Collections are generally used when you don't care about the order you put things in, so you usually don't bother with a Key. A bucket if you wish. Actually it's more like a endless tube you fill from the top and empty from the bottom.

I find this a little contradictory, actually the key benefit of a collection over a dictionary is that you can rely on the order of elements - they aren't guaranteed in a dictionary, as such you can use collections like a stack. That's why you can add items by index into a collection using the additional parameters in the Add method, dictionaries can't offer this functionality.



A Dictionary is another Object MS made for us. It is a wired combination of a collection and an array. Like an Array, you have to loop thru it by Index Number
For i = 1 to Dict.Count

This isn't strictly true, you can use a for each construct on an array, which is all the items method returns anyway, it's just not as efficient. You can also return the Keys of a dictionary, which you can't do with a collection.


Like a Collection you can retrieve Items by Key. Uniquely, you can use Objects as the Key. Unlike an Array, it can't hold UDTs.

Nor can a collection, but they can both hold objects which will overcome this


A Dictionary is built with two Arrays (Keys and Items) with linked indexes, such that the index# of the Key array is passed to the Items array for retrieval.You can load (Add) the Keys Array now and randomly load the Items Array later by referencing the Keys' Values
Which is useful as you can't do with a collection, simple types are not editable once in a collection.

Don't get too bogged down with snb's and jindon's code, they have nifty solutions; which are usually very efficient but hardly follow good practice. They tend to use concise variable names and no comments, the code is completely cryptic to other people maintaining the code and even to yourself when coming back to it 6 months later. I'd have long clean, readable code with comments and useful variable names over the shortest code possible any day. When writing code, the question you need to ask is "how easy is this going to be to change in 12 months time when I can't remember what it does?"

[fredlo2010]

I will have to do more research on this matter. For now I can use an array when I think I need one an a collection when I need one. Nothing too fancy though.

I will use arrays to go through lists, load arrays with lists or strings or values and them use them to for example see the recurrence of a specific string.

I will use string collections to add unique values. Other collections as sheets and range I use them to go through each item and perform a task.

I know that the codes from jundon and snb are extremely condensed I see like some kind of challenge. I wish there was a VBA book with exercise to sort of make the knowledge stick. It works for me and .NET lol :)

Thanks a lot for the comment. The important thing here is to learn and don't get frustrated.

[mancubus]

good luck fredlo :)

[SamT]

Yes, snb is a master of arrays and terse code, but he does have a naming convention and you will soon see it, if you study his examples.

[fredlo2010]

I am pretty sure he does. I mean he is a legend here. He is one of the first people that ways helped me with my code.

BTW I have a confirmed interview for tomorrow at 10:45. I just finished my cover letter to present with my resume in yellow parchment paper with golden letters folder. Presentation always helps

[Bob Phillips]

Good luck with the interview fredlo.

I hope the interview is easier than the first, the exercise was much simpler than the interview was in my mind. I have no idea what a Virgin Control does, nor do I know what a condensed ribbon means, nor what documentation is. I would have struggled if it had been me.

Arrays should be mastered, even allowing for any shortcomings they have, they are very powerful. Personally, I rarely ever use custom collections, and I cannot recall the last time I had a need for the dictionary object. I would always carve out a class for any such 'peculiar' needs (this is where I would use collections, in a collection class, but that is basic collections, the good stuff is in the classes), but I would think you could get by for a good few years without custom classes.

I would suggest that you learn ADO and SQL though, it is what I spend most of my time on, that and outputting the recordset onto a template.

And I agree with Kyle, good lucid code is far superior to clever-clever condensed code in my mind. I would never even use [A1] in my code, always type my variables, and I would always add the property, even it is a default property

[Zack Barresse]

I'm with Bob, on the congrats and pointers. :)

Once you start using array's you'll love them. I'm partial to collections and never use dictionaries. And sometimes I use an array of collections.

Good luck with your next interview!

[fredlo2010]

Hands down, another person I was expecting to comment on my threat just stopped and gave me a piece of advice.

Thanks a lot xld.

[Beatrix]

Thanks for giving feedback about the VBA test fredlo. I found it very useful. Hope everyting goes well with your interview.

[fredlo2010]

So I had my second interview today.

It was a very long interview-tour-conversation with about four people. Not all of them at the same time. They were extremely nice so even though the stress was on, it did not feel uncomfortable or threatening at all.

So they are looking for a person to look at the macros and try to figure out how they work. They mentioned that they had macros that would take up to 15 min to run. They want to gradually stop relying on macros and translate those into the in-house program. So the position will slowly but steadily transition to other fields and not just VBA. I told them I was interested to learn and that it would benefit me in my current Computer Science career.

They asked me about SQL they run a lot of reports with it, they use also talend, and jasper none of which I know; and they said it was ok as long as I was willing to learn. They also asked me about Linux. At this point I don’t know if I am a suitable candidate anymore…I am having split thoughts though. I will read about this though. Although they are looking for a junior willing to learn.

At this point I just have to sit and wait for the results.

But I have positive stuff in my hands already:) :
1. I should learn SQL
2. I should learn Linux
3. The advice from all of you in the forum

Thanks a lot for all your help guys. It has meant a lot to know I had some support out there. I am pretty sure all of you were wishing me the best. :) :) :)

[SamT]

Learn Linux?

LFS Project Homepage - Welcome to Linux From Scratch! (http://www.linuxfromscratch.org/lfs/)

I like there manual so much I use the same format.

[Zack Barresse]

Nothing wrong with not knowing, as long as you're willing to learn - a precious commodity these days.

I see a lot of customers looking to re-work someone's macros, make them more efficient, etc. Had one the other day, someone wanted to pay to have this code re-written to be forward compatible, took me about 10 min to re-write the code, couldn't bear to charge them anything.

I hope they pay you what you're worth. :)

[Bob Phillips]

Also remember that even if they do rewrite all of the VBA to in-house, that will bring it under IT's jurisdiction, so the business will soon be writing new VBA because IT don't deliver :)

[fredlo2010]

Thanks guys.

I don't know if I should laugh or cry at this but...since one of their bigger issues is the time the macros take (Yes I know I don't have the job but I got inspired) I decided to create a stopwatch to help me monitor the time...and I failed big time :(

[Bob Phillips]

Thanks guys.

I don't know if I should laugh or cry at this but...since one of their bigger issues is the time the macros take (Yes I know I don't have the job but I got inspired) I decided to create a stopwatch to help me monitor the time...and I failed big time :(

Are you saying that they said no, or they haven't got back to you (I hope for the latter for you?)

Timing is easy. Create a global variable called say nTime


Global nTime As Double

Then in the code just set nTime as compare it against the Timer at various points


Public Sub Control()

Set nTime = Timer

'some code that does something

Debug.Print "After control that did something " & Timer - nTime

Call ProcA

'some code that does something else

Debug.Print "After control that did something else " & Timer - nTime

Call ProcB

'more stuff etc.
End Proc

Public Sub ProcA()

'some code in ProcA

Debug.Print "Proc A some code " & Timer - nTime
End Sub

Public Sub ProcB()

'some code in ProcB

Debug.Print "Proc B some code " & Timer - nTime
End Sub


Not particularly sophisticated, but it should get you started.

Start with just a few debugs, find where the major time is spent, then add some more debugs in there to get a more detailed picture. And so on until you find a block of code that takes a lot of time and can be reduced.

[fredlo2010]

Hi xld,

They have not gotten back to me yet, formally. They did reply to my "thank you" email.

As for the time; I am sorry I was not more specific. I guess I got distracted or something and forgot to include a link to what I was working on. I was making a timer object but I was getting error 28. It was a small mistake on my end. Paul_hossler helped me with it yesterday. This is the link to the thread http://www.vbaexpress.com/forum/showthread.php?48504-Out-of-Stack-Space-Error-28

and this is the class (working perfectly no issues anymore):

Option Explicit
'==================================
'Class Name: xlMyStopwatch
'Class Instancing: 1-Private
'==================================

' Declare the variables for the class
Private d_Start As Date
Private d_Final As Date

' Declare the properties for the class
Public Property Get timeStart() As Date
timeStart = d_Start
End Property

Public Property Get timeEnd() As Date
timeEnd = d_Final
End Property

Public Property Let timeStart(ByVal tStart As Date)
d_Start = tStart
End Property

Public Property Let timeEnd(ByVal tEnd As Date)
d_Final = tEnd
End Property

' Methods and Functions
' Function to get the difference of time
Private Function Difference(sInterval As String) As Long
Difference = DateDiff(sInterval, Me.timeStart, Me.timeEnd)
End Function

Public Function ElapsedSeconds(Optional bUnits As Boolean = False) As String
If bUnits = True Then
ElapsedSeconds = Difference("s") & " " & "seconds"
Else
ElapsedSeconds = Difference("s")
End If
End Function

Public Function ElapsedMinutes(Optional bUnits As Boolean = False) As String
If bUnits = True Then
ElapsedMinutes = Difference("m") & " " & "minutes"
Else
ElapsedMinutes = Difference("m")
End If
End Function

Public Function ElapsedHours(Optional bUnits As Boolean = False) As String
If bUnits = True Then
ElapsedHours = Difference("h") & " " & "hours"
Else
ElapsedHours = Difference("h")
End If
End Function

Public Function ElapsedTime() As String
ElapsedTime = Format(Me.timeEnd - Me.timeStart, "hh:mm:ss")
End Function

I am sorry for the misunderstanding and making you type a code for me. I am really sorry.

[Bob Phillips]

No problem, the code took minutes to knock-up.

If you are going to develop this timer as a one of your VBA tools, take note of the approach I suggested using, timing in chunks, starting with large chunks and reducing the size until you start to see the wood, it makes sense. Also, I think Timer is a better granularity for timings rather than Now().

[fredlo2010]

Thanks a lot for the help xld.

I will look into modifying the variables to work with Time rather than Now. yes this is a tool I would like to develop and use to find parts of a code that are slow.

I was thinking I could add several mark tools and get the time difference between them. This would allow me to pin point and triangulate which is the slowest section in the code. I was working on that last night but I stopped to read about Corporate Standars for VBA Writing (http://exceluser.com/formulas/vbastds.htm) I started adding those to my class but it started to look extremely over-commented, I think. (thats why my code looks a little too much now) I did not add all the Headings-Tittles to all the functions and subs.

This is my class now:

Option Explicit

' ======================================================
' Name: xlMyStopwatch
' Purpose: Creates a stopwatch that can be used to
' debug other applications and macros.
' Programmer: Alfredo Lopez
' Comments: Code still in progress new funtionalities
' will be added.
'
' Changes------------------------------------------------
' Date Programmer Change
' 12/19/2013 Alfredo Lopez Written
' 12/20/2013 Alfredo Lopez New functionality for
' total time added.
' =======================================================



' =======================================================
' Declare the variables for the class
' =======================================================
Private d_Start As Date
Private d_Final As Date
Private i_Lap As Long
Private d_InitialTime As Date


' =======================================================
' Declare the properties for the class
' =======================================================


' Inicial Time Property
' -------------------------------------------------------
Public Property Get initialTime() As Date
initialTime = d_InitialTime
End Property
Public Property Let initialTime(ByVal tInitial As Date)
d_InitialTime = tInitial
End Property




' Time Start Property
' -------------------------------------------------------
Public Property Get timeStart() As Date
timeStart = d_Start
End Property
Public Property Let timeStart(ByVal tStart As Date)
d_Start = tStart
End Property




' Time End Property
' ------------------------------------------------------
Public Property Get timeEnd() As Date
timeEnd = d_Final
End Property
Public Property Let timeEnd(ByVal tEnd As Date)
d_Final = tEnd
End Property




' Number of Laps Property
' ------------------------------------------------------
Public Property Get lapNumber() As Integer
lapNumber = i_Lap
End Property
Public Property Let lapNumber(ByVal iLapNo As Integer)
i_Lap = iLapNo
End Property




' ======================================================
' Methods and Functions
' ======================================================


' =======================================================
' Name: Class_Initialize()
' Purpose: Initializes the class a Lap count of 0
' and the current time as the Inicial time.
' Programmer: Alfredo Lopez
'
' Changes------------------------------------------------
' Date Programmer Change
' 12/20/2013 Alfredo Lopez Written
' =======================================================
Private Sub Class_Initialize()
i_Lap = 0
d_InitialTime = Now
End Sub




' =======================================================
' Name: Difference(sInterval As String) As Long
' Purpose: Find the difference between two dates or
' times and returns an integer
' Programmer: Alfredo Lopez
' Called by:
' Call:
' Arguments: sInterval -- A string variable that feeds
' into the DATEDIFF function to specify the
' interval.
' Comments: It will be used several times.
'
' Changes------------------------------------------------
' Date Programmer Change
' <<date>> Alfredo Lopez Written
' =======================================================
Private Function Difference(sInterval As String) As Long
Difference = DateDiff(sInterval, Me.timeStart, Me.timeEnd)
End Function


' =======================================================
' Name: <<Name>>
' Purpose: <<Purpose of the code>>
' Programmer: Alfredo Lopez
' Called by:
' Call:
' Arguments:
' Comments: <<Some Comments>>
'
' Changes------------------------------------------------
' Date Programmer Change
' <<date>> Alfredo Lopez Written
' =======================================================
Public Function ElapsedSeconds(Optional bDisplayUnits As Boolean = False) As String
If bDisplayUnits = True Then
ElapsedSeconds = Difference("s") & " " & "seconds"
Else
ElapsedSeconds = Difference("s")
End If
End Function


Public Function ElapsedMinutes(Optional bDisplayUnits As Boolean = False) As String
If bDisplayUnits = True Then
ElapsedMinutes = Difference("m") & " " & "minutes"
Else
ElapsedMinutes = Difference("m")
End If
End Function


Public Function ElapsedHours(Optional bDisplayUnits As Boolean = False) As String
If bDisplayUnits = True Then
ElapsedHours = Difference("h") & " " & "hours"
Else
ElapsedHours = Difference("h")
End If
End Function


Public Function ElapsedTime() As String
ElapsedTime = Format(Me.timeEnd - Me.timeStart, "hh:mm:ss")
End Function


' Lap Function
Public Function Lap(Optional iLapNumber As Long) As String

Dim tempLap As String

' Check if the optional parameter was included.
If iLapNumber = 0 Then
Me.lapNumber = Me.lapNumber + 1
Else
Me.lapNumber = iLapNumber
End If

' Reset the values for the properties si I can get the difference
' in time. and add to the global time to keep a total count
Me.timeEnd = Now

' Display the values
tempLap = ElapsedTime()
Lap = "Lap no " & Me.lapNumber & ": " & tempLap

' Reset the values for the properties si I can get the difference
' in time. and add to the global time to keep a total count
Me.timeStart = Now
End Function


'Function to get the total time
Public Function TotalElapsedTime() As String
TotalElapsedTime = Format(Me.timeEnd - Me.initialTime, "hh:mm:ss")
End Function

PS: Formatting of the lines might get a little messy because of the formatting tags. I have included a sample file here.10995
Thanks a lot.

[Bob Phillips]

I agree, apart from the class declaration comments, every other comment there is redundant. None of them tell a reader anything that is not obvious from the code. I am a very minimalist commenter, because they clutter the code, they usually state the bleeding obvious (like those you have - no offence :)) or are badly worded and offer no help, and unless they are religiously updated (which never happens), they can often mislead.

Take a few of the comments you have added:

' =======================================================
' Declare the variables for the class
' =======================================================

Any coder should know that is what the code is doing

' Inicial Time Property
' -------------------------------------------------------

The property name tells us that

etc.

Class_Initialize comments

Far too much for so little code. I would have called the variable i_LapCounter rather than just i_Lap, then it is obvious what it does, i_Lap could be the number of laps, the time taken, or so on.

Difference method comments. A lot of comments for a trivial piece of code that is obvious.

And so on ...



To my mind comments only have a value to describe a design pattern that may not be obvious in the code, or if the client demands them.

[SamT]

You can speed up macros a lot just be getting rid of all the Selects. Also, use
Sub Faster()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
'.DisplayAlerts = False
End With
'
'Code here
'
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
'.DisplayAlerts = True
End With
End Sub

Nested loops and loops that use the the worksheet can be very slow. Especially if any loop has a lot of dots.

Loop
Workbooks("Book1").Sheets("Sheet1").Range("A1").Font.Name = "Somefont"
Workbooks("Book1").Sheets("Sheet1").Range("A1").Font.Weight = Bold
LoopEnd

Yet another example of your Stopwatch class

[fredlo2010]

Hi,

I am sorry for the late replay but I have been extremely busy. Thanks a lot for all the comments and the tips. They are amazing I feel like its early Christmas presents for me.

@xld
Yes, you are right I guess I am just trying to lean and adhere to standards. They were extremely hard to follow and update.
I also change the iLap variable to iLapCouter since the name can be a little ambiguous.

@SamT:
Thanks a lot for the sample and the detailed explanation. I guess I was creating this as something that I might use for other stuff. That's why I added so many properties and methods. In the end I thing the one I will be using the most is Lap() I also went a little creating since I have never done a class in VBA (only in .Net) When I was in my interview hey told me they had macros that would take 15 min to run. The dream feature of my class would be to add a "Lap" at the end of sub and then collect the data in a sheet with the sub name and the time it took to process and use it as a sort of report. (I need to look into that I have no idea if this can be done)

As for the variables I always like to start them with the initial of the type or the 3 letters identifier. I don't really use the scope as an identifier, perhaps I will when I get to more complicated code. ex: m_strName

As for the selects I haven't used one of those in ages. I only use it if I want to focus a specific cell after a macro has finished (Usually "A1" lol ) and .ScreenUpdating, .Calculation, .DisplayAlerts, .EnableEvents are well known to me and are one of my first sources to reduce speed. Of course I think that the master or all speed macros are Arrays.

Thanks a lot guys once more for helping me on this. :)

[Bob Phillips]

When I was in my interview hey told me they had macros that would take 15 min to run. The dream feature of my class would be to add a "Lap" at the end of sub and then collect the data in a sheet with the sub name and the time it took to process and use it as a sort of report. (I need to look into that I have no idea if this can be done)

That is an excellent idea, time them all, save them in a file with a application name, procedure name, and timestamp, and you have the start of macro performance BI.

[SamT]

@ Fred

My Personal.xls book is huge. Mostly it is filled with modules that start
Option Explicit
Option Private Module
#If False Then and end with "#End If". The Line "#If False Then" prevents the Compiler from even looking at the contents of the module.

In your Personal.xls, in VBA, set Sheet1's Visible property to xlVisible. Then use the Excel Windows menu to unhide Personal.xls and insert a sheet just for the Lap Report.

Place the Lap code in a Public (Class?) Module in Personal.xls and call it as you will.

You can also put a Laps workbook in the "/Microsoft/Office/*/XLStart folder. IMO, the advantage of using Personal.xls is that it is always hidden, yet always present.

I was not suggesting that you do or should use Select...Selection. I was saying that when reviewing code, that is what to look for and Refactor first.

[fredlo2010]

Thanks SamT,

So if I add code between those tags like you said I can have a whole bunch of code that I can use later on when needed correct? how do I access it with : CALL?

Option Explicit
Option Private Module

#If False Then

' My code goes here

#End If

Yes I have a few codes that I use frequently in my Personal/ Normal (for word) files. They are there for easy access and its very simple to share them as well.

lol Regarding the Lap feature of creating a report that will have to wait for a while. The code I have works for now; I would rather focus my attention on other stuff that I need to polish before moving on.

By the way have you guys seen these programs?
http://www.appspro.com/Utilities/Documentor.htm
http://www.aivosto.com/project/project.html
http://www.aivosto.com/visustin.html

They are amazing you can analyze code with those; they will even give you a rating and point out things that might me worth looking into. They have a freeware version of it but its more than enough for the novice programmer.

Also I was thinking. We have been discussing a lot of things in this post. Very important things that others might find helpful in the future and even though the post will always be here I think it would be better if we could gather all the information, synthesize it and perhaps add it as one of those Sticky notes on the tops. Maybe the Moderators can guide us on what would be the best approach.

I am picturing a post just like: "Suggested Books" as "Novice VBA Programmer Tips" or something similar.

Thanks

[SamT]

Option Explicit
Option Private Module
'Cannot CALL code in Option Private Module

#If False Then
'Any code inside #IF False...#EndIf will NEVER run
#EndIf

I use the above Module structure in large projects as a User Manual describing the Projects code organization, flow, and other things that some maintainer would need to know.

For Pasteable code, I just use Option Private Module for complete Procedures that have been tested and will work as is. I only enclose pasteable snippets in #if False...#EndIf's, because they would raise errors otherwise.

For code snippets, use

Sub Last_Range_Snippets() 'Sub Name to organize by function AND show in Dropdown.
#If False Then
'
LastRow = Cells(Rows.Count, [Column Letter] ).End(xlUP).Row
'
LastCol = Cells( [Row number] ,Columns.Count.End(xlToLeft).Column
'
'More Snippets here
'
#EndIf
End Sub


Just about the only code I keep in Public modules is code I use to analyze and troubleshoot other excel projects. These subs are available from the Macros menu.

I've attached the Exported module containing the ones that I use the very most. I had to Zip it because one can't upload a .bas file. Unzip and open with notepad to view or import into a VBA project to use.

Unfortunately, I lost both copies of my most advanced Personal.xls.

The User Manual Module name is always prefixed with "A_" or "a_" to put it at the top of the list in the Project Explorer. (A_notes, a_ProgramFlow, a_AboutThisProject.)

For the manual's Section Headings I use
Sub About_ModuleXYZ() : End Sub
'and
Sub About_Globals(): End Sub
'and etcThis puts the Sub Name in the Dropdown for easy navigation. All plain text is commented. Important Notes and Warnings are not commented, making them appear in red in the VBE. If the module becomes extremely long, I will put an index at the top for when the module is exported for printing. Yes, you can use multiple User Manual Modules.

That scheme is also to makes the exported .bas file easier to convert to navigable html with a regex editor.

[Bob Phillips]

By the way have you guys seen these programs?
http://www.appspro.com/Utilities/Documentor.htm
http://www.aivosto.com/project/project.html
http://www.aivosto.com/visustin.html

Yes I know them. Code Documentor is a decent program, and is free. Aivisto's offerings are quite something, but they are not cheap. I have tried to negotiate some sort of promotional discount with them, but they were having none of it.

[fredlo2010]

Thanks again guys.

@SamT those tips are amazing it offers a very nice and creative way of documenting code.

@ xld I know they are not cheap but they help a lot and you can use it for free in up to 10 modules.

I have been working on codes a lot!!! You guys were right. Sometimes I need to be a little more confident on what I can do. The other day I was helping a guy in a Forum and I used a "Collection" remember how I said that that was a weak spot for me. and I am relying on Intelisence a lot. Sometimes you just need to breath and say I can do it!

Regarding my interview, I have not received any news yet. Its a kinda busy time of the year though. I don't want to spoil anything but the guy I answered "No" the most in my interview added me to LinkedIn. That's something. Yet another benefit even if I don't get the job, that guy is a genius. I will keep on waiting.

Did you guys had a chance to take a look at this:


Also I was thinking. We have been discussing a lot of things in this post. Very important things that others might find helpful in the future and even though the post will always be here I think it would be better if we could gather all the information, synthesize it and perhaps add it as one of those Sticky notes on the tops. Maybe the Moderators can guide us on what would be the best approach.

I am picturing a post just like: "Suggested Books" as "Novice VBA Programmer Tips" or something similar.
Thanks

Thanks

[SamT]

Did you guys had a chance to take a look at this:

It's your idea, grab it an run with it. When you're satisfied, submit it to the potential articles forum


Then we can tell you how stupid it sounds.

:devil2: :whip:board::rtfm::tease::SHOCKED::dau::bonk::2p::jester::rotflmao::soupbox ::cry::haha::omg::oops::fireman:


No. I'm just kidding. We have way too much respect for coders, here.

[fredlo2010]

Hello guys,

I got the job!!!

Thank you very much to all of you for the help, the support, the advice. I have learned so much from this post. :)

[Bob Phillips]

Fantastic news Alfredo.

Now just make sure you take advantage of the opportunity, experiment, try new things, stretch yourself, explore, and on and on. We are all more engaged with interesting topics, beyond the usual 'how do I delete all rows with condition x', so you can use us to discuss those things.

[Beatrix]

Congratulationsss Alfredo:thumb


Hello guys,

I got the job!!!

Thank you very much to all of you for the help, the support, the advice. I have learned so much from this post. :)

[SamT]

Congratulations. :beerchug:




Told ya so.

[fredlo2010]

Now just make sure you take advantage of the opportunity, experiment, try new things, stretch yourself, explore, and on and on. We are all more engaged with interesting topics, beyond the usual 'how do I delete all rows with condition x', so you can use us to discuss those things.

Thanks. I know this is a very special Forum.

I will come back here countless times with topics, suggestions, problems.. pulling my hair out asking for help :) lol

[SamT]

For your PM to me, 'cuz PM's don't take attachments

@ Others: Attached is one of the best descriptions/tutorials on database structures I have seen. IF you have others that are as good or better, please add them.

[fredlo2010]

Hi,

Thanks a lot Sam

I was studying some SQL and VBA from this website http://www.fontstuff.com/access/acctut15.htm
I am also watching some videos on Microsoft Virtual Academy. to get and introduction to SQL. ( I really like this website, Thanks Jacob for the suggestion) http://www.microsoftvirtualacademy.com/#?fbid=EVQyY-sjpw7

PS: I am sorry if my answers are kinda late but I am having some difficulties getting notifications. ( I think it was fixed)

[Zack Barresse]

Sorry I didn't see this earlier. Congratulations! Very happy for you. :)

[fredlo2010]

Hello guys,

Today, I started my second week in the new job, and I love it. The code is a big mess, and I am not saying that my code is great, but... yeah its a headache... which is kind of good because I will be able to improve it.

I have been working on a workbook that runs some reports and imports and modifies the data. Its a little hard to understand; I have to admit it. There are about 2200 lines ( I already refactored it a little, there were 2900) of code spread out in about 40 procedures and 15 modules.
I went ahead and separated some of the code. One of the procedures had a Select Case and the code between the cases was about 30 lines. I split that and created a three different procedures. It was way too much. The code is bursting with public variables of all kind including several multidimensional arrays (my best Friend lol :) ) They also had a lot of loops; they would use a loop to find the row number of a value in a range; nothing range.find could not solve. I reduced run speed from 34 seconds to 26 seconds; that's not a lot but its something, although must of the delays are caused by Shell. I don't think there is a way to speed those up.

I attached two files for you guys to see some statistics...
11168

There is some logic that I don't understand, perhaps because I need to know a little bit more about the business. Also there are lines of code that I don't understand. For now I will leave those alone and try to work in what I know.

There is still a lot of work to do but it starts to look better.. at least readability has improved lol

I just felt it was fair to share with your guys my first job assignment. After all, you guys helped me and supported me so I could get the job.

Thanks a lot

See you guys around.
Alfredo :)

[Bob Phillips]

Glad to see it is going well and you are enjoying it.

I often look at my code and don't understand what it is doing, you are not alone :dunno

The procedure treeview looks good, how would it look with less hierarchical code and recursive code?

[fredlo2010]

Thanks,


how would it look with less hierarchical code and recursive code?
I am not sure what you mean by that. But I was messing with the free version of Visustin today and the code flowchart is even scarier lol

This is just a sample. (Sorry for the quality.)

11180

[BrianMH]

Hi Guys,I know this is an old thread but I just wanted to add my two cents for anyone out there wondering if being self taught is enough to land a job. I learned VBA and lots of other excel and office skills by solving problems at my last job. This website was absolutely essential to that. Asking questions and helping others solve their problems and then seeing how others did it better really helped me. Through that knowledge about a year and a half ago I landed my first real job that extensively used VBA for the 'real' job purpose. I've really been able to revolutionize the way they work here and I've got lots and lots of praise.Now I've just been offered a new job with a significant salary increase and much more analytical training. I'm over the moon, I never thought messing around with macros would get me this far.I just wanted to share with the members of this site my appreciation for the assistance through out my learning thus far. I also wanted to share with anyone aspiring to do the same that it is possible. A formal education is great but if like me you just didn't have the opportunity with the right drive and hunger for knowledge you can get further than maybe you think. Good luck and remember your attitude rather than your knowledge has more bearing on your future. Good Luck!

[Beatrix]

Hi BrianMH ,

Thanks very much for sharing your experience and giving feedback about VBA jobs as that would be a great motivation for myself.

Cheers
B.

[fredlo2010]

Hello everyone,

Well, time has passed since was asking for help on this matter. I was the interviewed person; but now lets say I am on the other end of the situation; I am now the Interviewer.


Any extra help here. I want to be able to ask good questions; because I want to make sure the person we hire can perform; but at the same time I want to be nice and just because a person does not know something it does not mean he/she cannot perform adequately (I was on that person's shoes not long ago) I am not only new to Interviewing for a VBA position I am new to the whole thing.


Thanks :) :) :)

[Bob Phillips]

My suggestion - don't ask specific questions about programming aspects, but provide a set of situations, increasing in complexity, and ask them to state how they would approach the problem with a view to solving it. Don't look for right answers, look for a general VBA/object model appreciation, and how they put that to use.