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.
Excel VBA Programmer Interview
Reply With Quote
.
you when you need it. We will 
when you're down, and we will
when you get that raise. Then we will make you buy the
I will make sure to come back and keep you guys updated on the outcomes.
) 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)
Originally Posted by SamT
