Nick's guide to Excel / VBA Interview Questions

Submitted by Nick on 12 January, 2009 - 14:33

I have hosted hundreds of Excel / VBA interviews, and I can work out very quickly how good someone is.

The biggest blunder to make in an interview is to say something like: "Oh, that's easy, I can do that in 5 minutes with the compiler and help files"

Well, in my interview, you have no compiler and no help files, so don't ignore syntax, or rely too much on help files.

You wouldn't turn up to a French interview with a dictionary and translate every word now would you ?

; - >

Here are some Excel VBA interview questions I might ask you.. Add a comment if you think you have a good answer.

1. Dates

Submitted by Nick on 12 January, 2009 - 14:35.

Name as many Date functions as you can in both Excel and VBA.

Date function for VBA and Excel

Submitted by Anonymous on 15 February, 2010 - 04:20.

Date, Date Value, day, days360, today,weekday,year

to add more: month, datedif,

Submitted by Anonymous on 23 February, 2010 - 09:46.

to add more:
month, datedif, datediff

To add another

Submitted by Anonymous on 19 May, 2010 - 16:14.

Do not forget:
networkdays

Date & Time Date (VBA) Date

Submitted by Anonymous on 18 October, 2011 - 05:46.

Date & Time Date (VBA) Date function returns the current system date. Th...
Date & Time DateAdd (VBA) DateAdd function returns a date after which a cert...
Date & Time DateDif DateDif function returns the difference between tw...
Date & Time DateDiff (VBA) DateDiff function returns the difference between t...
Date & Time DatePart (VBA) DatePart function returns a specified part of a gi...
Date & Time DateSerial (VBA) DateSerial function returns a date given a year, m...
Date & Time DateValue DateValue function returns the serial number of a ...
Date & Time Day Day function returns the day of the month (a numbe...
Date & Time Days360 Days360 function returns the number of days betwee...
Date & Time Hour Hour function returns the hour of a time value (fr...
Date & Time Minute Minute function returns the minute of a time value...
Date & Time Month Month function returns the month (a number from 1 ...
Date & Time MonthName (VBA) MonthName function returns a string representing t...
Date & Time Now Now function returns the current system date and t...
Date & Time Second Second function returns the second of a time value...
Date & Time Time Time function returns the decimal number for a par...
Date & Time TimeSerial (VBA) TimeSerial function returns a time given an hour, ...
Date & Time TimeValue TimeValue function returns the serial number of a ...
Date & Time Today Today function returns the current system date. Th...
Date & Time Weekday Weekday function returns a number representing the...
Date & Time WeekdayName (VBA) WeekdayName function returns a string representing...
Date & Time Year Year function returns a four-digit year (a number ...

2. Calculation

Submitted by Nick on 12 January, 2009 - 14:36.

Name as many ways as you can to make Excel calculate something.

Name as many ways as you can to make Excel calculate something

Submitted by Anonymous on 30 April, 2009 - 10:28.

Unless the answer isn't every function from absolute to year (is there a Z?), pivot tables, recorded Macros, VBA etc I'm not sure what the question is asking. Can you expand?

Excel Calculation

Submitted by Nick on 30 April, 2009 - 15:23.

ok, I'll start u off with some easy ones:

Press F9
Enter a formula in a cell
Select a cell containing a formula, press F2 then ENTER
Create a pivot table
Refresh a pivot table

... there are tonnes... the question is really to see how deep your understanding of Excel is

For example... would you know that when you have the formula entry screen up, tabbing through the arguments will cause a calculation of the function to occur.

Would u know that double clicking on a column heading can cause several types of calculation to occur...?

etc...

Excel Calculation

Submitted by Anonymous on 10 October, 2009 - 10:32.

6. shift + F9
7. F2 a cell then enter

Not well known

Submitted by Anonymous on 29 June, 2010 - 17:38.

Application.Evaluate()
Worksheet.Evaluate()
will both evaluate a statement you pass in as if it were a formula

Application.Volatile

Submitted by Anonymous on 6 February, 2011 - 21:03.

Application.Volatile

Another one

Submitted by Anonymous on 9 June, 2011 - 16:49.

Application.Calculation = xlCalculationAutomatic

Application.Calculate

Change value with Scroll bar

Submitted by Cyril Myshkin on 23 December, 2009 - 13:44.

Change value with Scroll bar or Spin button

Select a formula in the

Submitted by Anonymous on 24 July, 2010 - 17:39.

Select a formula in the formula bar and press F9. this will calculate the formula but will not print it out in the cell

3. VBA Range Selection Question

Submitted by Nick on 12 January, 2009 - 14:47.

You have the following data:
Data

- A1 is always populated
- the rest of the data may change
- write a VBA subroutine that selects A1 and up to and including the last populated cell on the diagonal going downwards and to the right.

So in this case: it will select A1:D4
Data

Diagonal

Submitted by Unsubscribed on 19 May, 2009 - 22:48.

Option Explicit
Sub Diagonal()
    Dim i As Long
    Do
        i = i + 1
    Loop Until Range("A1").Offset(i, i) = ""
    Cells(1, 1).Resize(i, i).Select
End Sub

Diagonal

Submitted by Nick on 20 May, 2009 - 08:27.

Excellent start, but that won't always work

Diagonal

Submitted by Unsubscribed on 21 May, 2009 - 19:35.

My mistake, I read it as a continuous diagonal. So we start from the other end.

Option Explicit
Sub Diagonal()
    Dim i As Long
    With ActiveSheet.UsedRange
        i = Application.Min(.Rows.Count, .Columns.Count) + 1
    End With
    Do
        i = i - 1
    Loop Until Range("A1").Offset(i - 1, i - 1) <> ""
    Cells(1, 1).Resize(i, i).Select
End Sub

Diagonal

Submitted by Nick on 21 May, 2009 - 23:17.

closer, but still not bullet proof..

Diagonal

Submitted by Anonymous on 28 May, 2009 - 21:59.

Sub Diagonal()

Dim iLast As Integer
iLast = 1
Do While Trim(Cells(iLast, iLast)) <> ""
iLast = iLast + 1
Loop
iLast = iLast - 1
Range(Cells(1, 1), Cells(iLast, iLast)).Select

End Sub

Diagonal

Submitted by Nick on 28 May, 2009 - 23:08.

this won't always work

Could you please give an

Submitted by Anonymous on 28 May, 2009 - 23:29.

Could you please give an example when this won't work ? (this is assuming A1 always in not empty and "space" is not being an "entry"). May be I didn't understand the requirement correctly.

diagonal

Submitted by Nick on 29 May, 2009 - 08:34.

That'll give it away..

; - >

There are 2 cases that will break your code.

Maybe this is better: Public

Submitted by Anonymous on 3 November, 2010 - 10:43.

Maybe this is better:

Public Sub Diagonal()
Dim lCnt As Long

lCnt = Sheet1.UsedRange.Columns.Count
Do While IsEmpty(Sheet1.Cells(lCnt, lCnt))
lCnt = lCnt - 1
Loop

Sheet1.Range("A1", Sheet1.Cells(lCnt, lCnt)).Select
End Sub

What about this one: Sub

Submitted by Anonymous on 10 January, 2010 - 18:34.

What about this one:

Sub GetDiagonal()
Dim lRowCt As Long
Dim lColCt As Long
Dim lCt As Long
With ActiveSheet.UsedRange
lRowCt = .Rows.Count
lColCt = .Columns.Count
For lCt = Application.Max(lRowCt, lColCt) To 1 Step -1
If .Cells(lCt, lCt) <> "" Then
ActiveSheet.Range(ActiveSheet.Range("A1"), ActiveSheet.Cells(lCt, lCt)).Select
Exit For
End If
Next
End With
End Sub

select diagonal

Submitted by Nick on 11 January, 2010 - 02:50.

close, but it won't always work.. there's a small bug too, you'll kick yourself when you see it.

I see a couple of potential

Submitted by Anonymous on 11 January, 2010 - 11:15.

I see a couple of potential problems with my previous entry:

- The used range has more rows than there are columns available
- The active sheet isn't a worksheet

To cater for this, I modified the code like this:

Sub GetDiagonal() Dim lRowCt As Long Dim lColCt As Long Dim lCt As Long If TypeName(ActiveSheet) = "Worksheet" Then With ActiveSheet lRowCt = Application.Min(.UsedRange.Rows.Count, .Columns.Count) lColCt = .UsedRange.Columns.Count For lCt = Application.Max(lRowCt, lColCt) To 1 Step -1 If .Cells(lCt, lCt) <> "" Then .Range(.Range("A1"), .Cells(lCt, lCt)).Select Exit For End If Next End With End If End Sub

Another problem may be that a

Submitted by Anonymous on 11 January, 2010 - 11:17.

Another problem may be that a cell evaluates to an error result, to avoid a runtime error the cod emust be modified further to:
Sub GetDiagonal() Dim lRowCt As Long Dim lColCt As Long Dim lCt As Long If TypeName(ActiveSheet) = "Worksheet" Then With ActiveSheet lRowCt = Application.Min(.UsedRange.Rows.Count, .Columns.Count) lColCt = .UsedRange.Columns.Count For lCt = Application.Max(lRowCt, lColCt) To 1 Step -1 If .Cells(lCt, lCt).Text <> "" Then .Range(.Range("A1"), .Cells(lCt, lCt)).Select Exit For End If Next End With End If End Sub

Congratulations

Submitted by Nick on 11 January, 2010 - 11:33.

well done... u nailed it.

GetDiagonal

Submitted by Anonymous on 20 September, 2010 - 10:06.

Sub GetDiagonal()
Dim lRowCt As Long
Dim lColCt As Long
Dim lCt As Long
Dim i As Integer
If TypeName(ActiveSheet) = "Worksheet" Then
With ActiveSheet
lRowCt = .UsedRange.Rows.Count
lColCt = .UsedRange.Columns.Count
lCt = Application.Max(lRowCt, lColCt)
If .Cells(lCt, lCt).Text <> "" Then
.Range(.Range("A1"), .Cells(lCt, lCt)).Select
Else
For i = 1 To lRowCt
If .Cells(i, lColCt).Text <> "" Then
.Range(.Range("A1"), .Cells(i, lColCt)).Select
End If
Next i
End If

End With
End If
End Sub

GetDiagonal simple way

Submitted by Anonymous on 23 May, 2011 - 09:42.

Range("A1").Select
Do While ActiveCell.Offset(1, 1) <> ""
ActiveCell.Offset(1, 1).Select
Loop
Range("A1:" & ActiveCell.Address).Select

hi guys, thanks a lot for

Submitted by Anonymous on 11 May, 2010 - 22:03.

hi guys, thanks a lot for this brain teasing assignement.
Nick, please can you have a look at my code fully based on the previous one.
i change MAX by MIN in this line:
For lCt = Application.Max(lRowCt, lColCt) To 1 Step -1

doing so i think that it might increase the execution time as we dont need to evaluate unnecessary cells.

Sub GetDiagonalBonero()
'code fully on previous.

Dim lRowCt As Long
Dim lColCt As Long
Dim lCt As Long
If TypeName(ActiveSheet) = "Worksheet" Then
With ActiveSheet
lRowCt = Application.Min(.UsedRange.Rows.Count, .Columns.Count)
lColCt = .UsedRange.Columns.Count

For lCt = Application.Min(lRowCt, lColCt) To 1 Step -1
If .Cells(lCt, lCt).Text <> "" Then
.Range(.Range("A1"), .Cells(lCt, lCt)).Select
Exit For
End If
Next
End With
End If
End Sub

My first try in VBA

Submitted by Cyril Myshkin on 11 January, 2010 - 12:09.

Sub SelDiag()

ActiveSheet.Range("a1").Select

Do Until Selection.Value = Empty

Selection.Offset(1, 1).Select

Loop

DiagLen = Selection.Column - 2

ActiveSheet.Range("a1", Range("a1").Offset(DiagLen, DiagLen)).Select

End Sub

Excellent 1st try

Submitted by Nick on 11 January, 2010 - 12:26.

...good effort.

Have a look at the other solution posted recently..

What most people omit are the following:

Column limitations
Handling errors returned by the cell formula
Work on all types of sheet

select diagonal range

Submitted by richardd on 17 March, 2010 - 18:55.

Public Sub selectDiagonal()
Dim c As Range, rDiagonal As Range
Dim i As Long

Set c = ActiveSheet.[b2]

While Not c.Column = Sheet1.Columns.Count
If LenB(c.Value2) <> 0 Then Set rDiagonal = c
Set c = c.Offset(1, 1)
Wend

ActiveSheet.Range([a1], rDiagonal).Select
Set c = Nothing
Set rDiagonal = Nothing
End Sub

Re: VBA Range Selection Question

Submitted by Anonymous on 1 February, 2011 - 07:37.

Hope the below code will answer your question:

Sub GetDiagonal()
Dim lLRow As Double
Dim lLCol As Double

ActiveSheet.Cells(1, 1).Select

With ActiveSheet.UsedRange
ActiveCell.SpecialCells(xlLastCell).Select
lLCol = Selection.Column
lLRow = Selection.End(xlUp).Row
End With

ActiveSheet.Range(Cells(1, 1), Cells(lLRow, lLCol)).Select
End Sub

-Boss-
-Excel Student-

Re: VBA Range Selection Question

Submitted by Bhaskaran S on 1 February, 2011 - 08:21.

The Above code submited by me.

-boss-

-Excel Student-

how about this?

Submitted by Abishek Mouli T... on 11 May, 2011 - 14:54.

Sub selectdiagonal()
Cells(1, 1).Select
i = 1
While Cells(i, i) <> ""
i = i + 1
Wend
i = i - 1
Cells(i, i).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlUp)).Select
End Sub

Efficient diagonal selection

Submitted by Anonymous on 7 September, 2011 - 10:35.

With Sheets(1)
Set f = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
.Range(.Range("A1"), .Cells(f.Row, f.Column)).Select
End With

My solution

Submitted by Anonymous on 12 February, 2012 - 19:21.

Sub Diagonal()
Dim Rowcount, ColumnCount As Long
Rowcount = ActiveSheet.UsedRange.Rows.Count
ColumnCount = ActiveSheet.UsedRange.Columns.Count
Dim i As Long
i = WorksheetFunction.Max(Rowcount, ColumnCount)
Do While ActiveSheet.Cells(i, i) = ""
i = i - 1
Loop
Cells(1, 1).Resize(i, i).Select
End Sub

another solution

Submitted by Anonymous on 2 March, 2012 - 20:41.

Sub Diagonal()

Dim i As Integer, j As Integer

' take advantage of fact that we have only 256 columns (at least up to 2003 :))
Do While (Cells(256 - i, 256 - j) = "")
i = i + 1
j = j + 1
Loop

Range(Cells(1, 1), Cells(256 - i, 256 - j)).Select

End Sub

4. Excel function

Submitted by Nick on 12 January, 2009 - 14:57.

Write an Excel function that calculates the first day of the current month

The first day of the current month right now is: 01-Jan-2009

First day

Submitted by Unsubscribed on 19 May, 2009 - 22:37.

=TEXT(WEEKDAY(TODAY()-DAY(TODAY()-1)),"dddd")

First day of the month

Submitted by Nick on 20 May, 2009 - 08:25.

that's the day of the week of the first day of the current month

First day

Submitted by Unsubscribed on 21 May, 2009 - 19:42.

I seem to have problems with these questions! What else should the answer display?

First Day

Submitted by Anonymous on 28 May, 2009 - 22:09.

If I understand the question right - below is the number of days in current year starting from Jan 1 to the first day of this month (including)

=DAYS360(DATE(YEAR(NOW()),1,1),DATE(YEAR(NOW()),MONTH(NOW()),1))

First Day

Submitted by Nick on 28 May, 2009 - 23:12.

maybe I need to clarify

The first day of the current month right now is: 1st May 2009

The it will be only the part

Submitted by Anonymous on 28 May, 2009 - 23:20.

The it will be only the part of the above:
=DATE(YEAR(NOW()),MONTH(NOW()),1)

1st day

Submitted by Nick on 29 May, 2009 - 08:29.

is correct... u cld also use TODAY instead of NOW.

1st day

Submitted by Anonymous on 24 February, 2010 - 18:34.

you could also use =eomonth(today(),-1)+1

First Day

Submitted by tonybot on 21 May, 2009 - 18:30.

=TEXT(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1)),"dddd")

First Day of current date

Submitted by Anonymous on 8 January, 2010 - 17:20.

=TEXT(TODAY()-DAY(TODAY())+1,"ddd, mmm dd, yyyy")

1st day better command

Submitted by Anonymous on 3 February, 2010 - 23:35.

= DateSerial(Year(Date), Month(Date), 1)

DateSerial

Submitted by Nick on 4 February, 2010 - 12:10.

DateSerial is a VBA function.. not Excel

ANSWER-First Day of current month

Submitted by Anonymous on 24 February, 2010 - 07:23.

=TEXT(MONTH(NOW),"DDDD") Thanks

Correct One: First day of Current month...

Submitted by Anonymous on 30 April, 2010 - 05:29.

=TEXT(WEEKDAY(TODAY()-DAY(TODAY()-1)),"dddd") for more help, just mail me urs problem.. Thanks

find currect day of any date

Submitted by Anonymous on 13 November, 2011 - 20:09.

=text(date(year(today),month(today),1),"dddd")

First Day of Current month

Submitted by Anonymous on 11 May, 2012 - 11:29.

Sorry, your answer is wrong. I guess it will work.

=Text(Month(Now()),"DDDD")

An even tougher question is last day of current month :)

Submitted by chipbeaulieu on 12 June, 2010 - 05:57.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1

first day of the current month

Submitted by Anonymous on 26 July, 2010 - 06:27.

dt = Year(Now()) & "/" & Month(Now()) & "/" & "1"

=TEXT (MONTH (TEXT (TODAY(),"MM"

Submitted by Anonymous on 17 August, 2010 - 14:02.

=TEXT(MONTH(TEXT(TODAY(),"MM")),"DDDD")

=TEXT(WEEKDAY(A1),"dddd

Submitted by Anonymous on 1 February, 2011 - 08:04.

=TEXT(WEEKDAY(A1),"dddd").
Enter your date in cell A1.

Ans: 4. Excel function

Submitted by Bhaskaran S on 1 February, 2011 - 11:09.

=TEXT(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1)),"DDDD")
or

=TEXT(WEEKDAY(DATE(YEAR(Today()),MONTH(Today()),1)),"DDDD")

=TEXT(WEEKDAY((MONTH(Today())&"/1/"&YEAR(today()))*1),"DDDD")

=TEXT(WEEKDAY(DATE(YEAR(a1),MONTH(a1),1)),"DDDD")
or

=TEXT(WEEKDAY((MONTH(A1)&"/1/"&YEAR(A1))*1),"DDDD")

-Boss (a) Bhaskar-

Ans 4 or may be this

Submitted by Anonymous on 12 December, 2011 - 16:22.

=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-DAY(TODAY()-1),"dddd")

5. Excel Function

Submitted by Nick on 12 January, 2009 - 15:05.

You have the following data: Data

Write a function to go in cell C3 that will return the house name that corresponds to the number typed in C2

Ans 5

Submitted by Anonymous on 12 May, 2009 - 05:29.

=IF(B2="","", VLOOKUP(B2,E2:F8,2,0))

Ans 5

Submitted by Nick on 12 May, 2009 - 08:15.

Nop

Ans 5

Submitted by Unsubscribed on 19 May, 2009 - 22:27.

why ?

Ans 5

Submitted by Nick on 20 May, 2009 - 08:32.

there's a small oversight in the references

VLOOKUP(C2,E2:F8,2,0)

Submitted by tonybot on 21 May, 2009 - 18:34.

VLOOKUP(C2,E2:F8,2,0)

It would say this was wrong...

Submitted by Anonymous on 5 June, 2009 - 02:00.

i think its
VLOOKUP(C2,E3:F8,2,false)

that is E3 instead of E2

Solution

Submitted by Anonymous on 22 June, 2010 - 06:32.

No need of using if function for this.

Formula is =VLOOKUP(C2,E2:H8,2,FALSE)

Thats it, it returs the value searching for is Building name

6. VLOOKUP vs INDEX MATCH

Submitted by Nick on 12 January, 2009 - 15:10.

What's the difference between using VLOOKUP vs INDEX + MATCH ?

Vlookup will allow you to

Submitted by Anonymous on 10 June, 2009 - 22:22.

Vlookup will allow you to search for an item in the 1st column.
The other will allow the seach param on any column

Vlookup must always return a

Submitted by Anonymous on 23 October, 2009 - 15:46.

Vlookup must always return a value to the right of the lookup column. Using the Index and Match functions together, you can return a value to the left of the lookup column.

VLOOKUP vs INDEX MATCH

Submitted by Anonymous on 30 March, 2011 - 02:47.

VLOOKUP works correct only with sorted values... and can lookup in the 1st column only..
Index + Match is best for getting values from any part of the array....

difference between using VLOOKUP vs INDEX + MATCH

Submitted by munendra on 17 February, 2012 - 12:46.

Vlookup have a limitation of searching, that it only search in right side, of the searching data,

Example

Emp table

Emp_Code Emp_name Emp_ID

011 abc 121

012 adef 131

Result

Emp_Name Emp_Code

abc vlookup(not able to do that search)

adef index&match function can do that type of searching

7. Volatile functions

Submitted by Nick on 12 January, 2009 - 15:10.

What's a Volatile function ?

Functions that will trigger

Submitted by Anonymous on 5 October, 2009 - 15:35.

Functions that will trigger recalculations every time a calculation is performed, e.g. F9.

8. Volatile functions part 2

Submitted by Nick on 12 January, 2009 - 15:11.

Name as many volatile functions as you can

They are

Submitted by Cyril Myshkin on 23 December, 2009 - 13:51.

RAND

RANDBETWEEN

TODAY

NOW

ROW

COLUMN

Volatile functions part 2

Submitted by Anonymous on 18 February, 2010 - 11:44.

OFFSET
INDIRECT
VLOOKUP
HLOOKUP

9. Pivot Tables

Submitted by Nick on 12 January, 2009 - 15:12.

Explain what a pivot table does

Pivot Tables enables us to

Submitted by Anonymous on 29 July, 2009 - 03:29.

Pivot Tables enables us to summarize & analyze data in Lists & Tables. They can automatically sort, count & total the data stored in one table or spreedsheet & creates a second table displaying the summarized data.
The user sets up & changes the structure by dragging a dropping fields graphically.
Saurabh Singh

10. Missing and repeated number

Submitted by Hakim on 23 March, 2009 - 14:11.

You have a set of consecutive numbers from 1 to N, we remove a number at random and duplicate another number from the remaining, we place these numbers in an array and shuffle it. Write a VBA function when passed that unsorted array prints the missing and the duplicated numbers.

Here is a hint: the sum of

Submitted by Hakim on 3 April, 2009 - 22:33.

Here is a hint: the sum of squares of numbers from 1 to N is N*(N+1)*(2*N+1)/6

Sub FindInArray(InputArray()

Submitted by Anonymous on 28 May, 2009 - 23:16.

Sub FindInArray(InputArray() As Integer)
Dim N As Long
Dim lNSum As Long
Dim lNSqSum As Long
Dim lSum As Long
Dim lSqSum As Long
Dim lSumDiff As Long
Dim lSqSumDiff As Long
Dim iLess As Integer
Dim iMore As Integer
Dim strResult As String

N = WorksheetFunction.Count(InputArray())

lNSum = N * (N + 1) / 2
lNSqSum = N * (N + 1) * (2 * N + 1) / 6

lSum = WorksheetFunction.Sum(InputArray())
lSqSum = WorksheetFunction.SumSq(InputArray())

lSumDiff = lSum - lNSum
lSqSumDiff = lSqSum - lNSqSum

iLess = (lSqSumDiff - lSumDiff ^ 2) / (2 * lSumDiff)
iMore = (lSqSumDiff + lSumDiff ^ 2) / (2 * lSumDiff)

strResult = "Less Value = " & iLess & vbCrLf & "More Value = " & iMore

MsgBox strResult
Debug.Print strResult

End Sub

Function miss(n) 'n is the

Submitted by Anonymous on 4 August, 2010 - 12:49.

Function miss(n) 'n is the total number

Dim sum As Integer
Dim sum1 As Integer
Dim num As Integer

num = 0
i = 1
sum = n * (n + 1) / 2 ' sum of first n integers
sum1 = 0
While i <= n
sum1 = sum1 + Sheet2.Cells(i, 1) ' sum of numbers given in the array

If (WorksheetFunction.CountIf(Sheet2.Range("A:A"), Sheet2.Cells(i, 1)) > 1) Then
num = Sheet2.Cells(i, 1) 'check which number is repeating and save it in num
End If

i = i + 1
Wend

diff = sum1 - sum

miss = num - diff
' the missing number will be the difference between the repeating number
' and in the difference of sum of two arrays(original and modified)

End Function

Define a public constant

Submitted by Anonymous on 29 June, 2009 - 02:01.

Define a public constant -
Const N As Integer = 20

Add two buttons on the WorkSheet -
*-Jumble - Associate following code to it -
Public Sub Jumble()
For Each myCell In Worksheets("MyCustomSheet").Range("MyList")
Do
i = CInt(Rnd * N)
Loop While Cells(i + 2, 5) <> "" And i < N + 1
Cells(i + 2, 5) = myCell.Value
Next
End Sub

*-Find Issues - Associate following code to it -
Public Sub Findissues()
For i = 1 To N
flag = 1
For Each myCell In Worksheets("MyCustomSheet").Range("RandomList")
If myCell.Value = i And flag = 0 Then
flag = 2
End If
If myCell.Value = i And flag <> 2 Then
flag = 0
End If
Next
If flag = 1 Then
Cells(i, 10) = "Missing - " & i
End If

If flag = 2 Then
Cells(i, 10) = "Repeated - " & i
End If
Next
End Sub

Now in the Sheet itself define 1 to N numbers in consecutive cells, and define this as a named range - "MyList"

Now in 2 columns away.. define the entire column as a named range -"RandomList"

Now when you press the button Jumble you will get the list of 1st N numbers jumbled up in Named range "RandomList"

Now click on the Find issues.... it will populate the results in 10th column,... as to which elements are missing and which are repeated.

Finding the missing number

Submitted by Anonymous on 4 August, 2010 - 12:48.

Whats the use of sum of squares of the number. We just need sum of the first n numbers i.e: n*(n+1)/2
Solution:-

Function miss(n) 'n is the total number

Dim sum As Integer
Dim sum1 As Integer
Dim num As Integer

num = 0
i = 1
sum = n * (n + 1) / 2 ' sum of first n integers
sum1 = 0
While i <= n
sum1 = sum1 + Sheet2.Cells(i, 1) ' sum of numbers given in the array

If (WorksheetFunction.CountIf(Sheet2.Range("A:A"), Sheet2.Cells(i, 1)) > 1) Then
num = Sheet2.Cells(i, 1) 'check which number is repeating and save it in num
End If

i = i + 1
Wend

diff = sum1 - sum

miss = num - diff
' the missing number will be the difference between the repeating number
' and in the difference of sum of two arrays(original and modified)

End Function

Ribbon

Submitted by Anonymous on 25 September, 2010 - 17:35.

How does one limit UI with the Excel 2007 Ribbon? Is it possible to keep a spreadsheet in full-screen mode all the time?

Show/Hide Excel 2007 Ribbon

Submitted by Vishesh on 25 September, 2010 - 20:01.

Sub HideRibbon()

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

End Sub

Sub ShowRibbon()

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"

End Sub

Thank you very much!!! I

Submitted by Anonymous on 25 September, 2010 - 22:07.

Thank you very much!!! I greatly appreciate your help!!!!

Not working in Excel 2007

Submitted by Anonymous on 26 October, 2011 - 14:11.

I tried entering this code at the end of my code both with and without the Sub ShowRibbon() part saving and reopenig it and the ribbon was still there. Below is the code I am using; where should I enter it and is there any modifications I need to make to get it to work?

Option Explicit
'custom code by:
'Jerry Latham, Microsoft MVP, Excel group 2005-2011
'email: HelpFrom@JLathamSite.com
'
Dim savingNowFlag As Boolean

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'this code implemented to set PreviousValue to the
'active cell on a non-Audit Trail sheet when that sheet is selected
If Sh.Name <> auditSheetName Then
PreviousValue = ActiveCell.Value
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
PreviousValue = Target.Cells(1, 1).Value
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Sh refers to the worksheet that the change took place on
'Target works just like it does in Worksheet_Change() event processing
'it is just specific to the Sh sheet that the change took place on
'
Dim NR As Long
'all of the following deal with monitoring the "Reviewed by..." entries.
Dim auditWS As Worksheet
Dim currentCol As Long
Dim currentRow As Long
Dim sectionFirstRow As Long
Dim examineRange As Range
Dim anyERangeEntry As Range
Dim tempCell As Range

'if the change took place on the Audit Trail worksheet, ignore!!
If Sh.Name = auditSheetName Then
Exit Sub
End If

If Intersect(Target, Sh.Range(watchRange)) Is Nothing Then Exit Sub
'
'this section determines if the change is being made to one of the
' "Interval Reviewed By (Initials)" rows on the sheet
' by using the phrase, we don't have to keep up with rows
' just be sure to use the phrase where needed
'
'while this looks like a lot of code, it is only executed when
'you attempt to make a change to the 'Reviewed by" entries.
If InStr(UCase(Sh.Range("A" & Target.Row)), UCase(reviewPhrase)) > 0 Then
'this IS one of the special rows, determine if the
'make sure that the current user is not the same user
'that made entries into this column previously
'if they are the same, we will erase the entry and
'refuse to accept it or record it.
currentCol = Target.Column
currentRow = Target.Row
'find where this section of entries begins based on finding the
' sectionStartPhrase (STABILITY STUDY:) entry immediately above
' this Reviewed by entry
'at this time they are 24 rows apart, but it could change
sectionFirstRow = currentRow - 1
With Sh
Do Until InStr(.Range("A" & sectionFirstRow), sectionStartPhrase) > 0
sectionFirstRow = sectionFirstRow - 1
Loop
End With
Set auditWS = ThisWorkbook.Worksheets(auditSheetName)
'set a reference to the list of worksheet names on the Audit Trail sheet
Set examineRange = auditWS. _
Range(awsWSNameCol & firstAuditEntryRow & ":" & _
auditWS.Range(awsWSNameCol & Rows.Count).End(xlUp).Address)
For Each anyERangeEntry In examineRange
'is the entry regarding this Sh?
If anyERangeEntry = Sh.Name Then
'yes, test if recorded range is in same column and within the proper section
Set tempCell = Range(auditWS.Range(awsCellCol & anyERangeEntry.Row).Value)
If tempCell.Column = currentCol And tempCell.Row > sectionFirstRow And _
tempCell.Row < currentRow Then
'entry is about the same sheet and same column, within the correct section
'test the user entry for name match/mismatch
If auditWS.Range(awsUserCol & anyERangeEntry.Row) = Environ("username") Then
'the names are the same!
'reject the attempted entry
'you could throw up this message box if you want
MsgBox "You may not Review entries that you made.", vbOKOnly, "Invalid Reviewer"
Application.EnableEvents = False
If Target.MergeCells Then
Target.MergeArea.ClearContents
Target.Cells(1, 1).Select
Else
Target.ClearContents
Target.Select
End If
'remain in the cell we just cleared
Target.Select
Application.EnableEvents = True
'some housecleaning before exiting
Set anyERangeEntry = Nothing
Set examineRange = Nothing
Set auditWS = Nothing
Set tempCell = Nothing
Exit Sub
Else
'different names, jump out of the test loop
'some housecleaning before continuing
Set anyERangeEntry = Nothing
Set examineRange = Nothing
Set auditWS = Nothing
Set tempCell = Nothing
Exit For
End If
End If
End If
Next
End If

'continue on to record the entry

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

With Sheets(auditSheetName)
.Unprotect Password:=auditWSPassword
NR = .Range(awsCellCol & Rows.Count).End(xlUp).Row + 1
.Range(awsCellCol & NR).Value = Target.Address(False, False)
.Range(awsWSNameCol & NR).Value = Sh.Name
.Range(awsDateCol & NR).Value = Now
.Range(awsUserCol & NR).Value = Environ("username")
.Range(awsPrevValCol & NR).Value = PreviousValue
.Range(awsNewValCol & NR).Value = Target.Value
.Protect Password:=auditWSPassword
End With

If PreviousValue = "" Then
With Sheets(auditSheetName)
.Unprotect Password:=auditWSPassword
.Range(awsReasonCol & NR).Value = "New Data"
.Protect Password:=auditWSPassword
End With
Else
'get reason for change from user via UserForm
UserForm1.Show
End If
Application.EnableEvents = True

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim auditWS As Worksheet
Dim CellListRange As Range
Dim anyCellListed As Range
Dim sName As String
Dim cAddress As String

If savingNowFlag Then
savingNowFlag = False ' reset
Exit Sub ' no re-entry while doing it already
End If

Set auditWS = ThisWorkbook.Worksheets(auditSheetName)
If auditWS.Range(awsCellCol & Rows.Count).End(xlUp).Row < firstAuditEntryRow Then
Set auditWS = Nothing
Exit Sub ' no entries on the Audit Trail sheet other than the headers.
End If

Set CellListRange = auditWS.Range(awsCellCol & firstAuditEntryRow & _
":" & auditWS.Range(awsCellCol & Rows.Count).End(xlUp).Address)

Application.ScreenUpdating = False
For Each anyCellListed In CellListRange
If IsEmpty(auditWS.Range(awsLockedCol & anyCellListed.Row)) Then
'need to lock the cell on the specified worksheet
'Exception to the rule:
' If reason for change was "Typographical Error" AND the
' cell in question is still empty, then don't lock it
' and change the error-maker's name so that it won't
' trigger as "cannot audit your own entries" later.
'
sName = auditWS.Range(awsWSNameCol & anyCellListed.Row)
cAddress = auditWS.Range(awsCellCol & anyCellListed.Row)
If IsEmpty(Worksheets(sName).Range(cAddress)) And _
auditWS.Range(awsReasonCol & anyCellListed.Row) = "Typographical Error" Then
' change the user id to have a "-" in front of it if it doesn't already
' so that username doesn't match for "can this user audit this data" later.
sName = auditWS.Range(awsUserCol & anyCellListed.Row)
If Left(sName, 1) <> "-" Then
auditWS.Unprotect Password:=auditWSPassword
auditWS.Range(awsUserCol & anyCellListed.Row) = _
"-" & auditWS.Range(awsUserCol & anyCellListed.Row)
auditWS.Protect Password:=auditWSPassword
End If
Else ' do need to lock it and mark it as locked
With Worksheets(auditWS.Range(awsWSNameCol & anyCellListed.Row).Value)
.Unprotect Password:=nonAuditWSPassword
If .Range(anyCellListed).MergeCells = True Then
.Range(anyCellListed).MergeArea.Locked = True
Else
.Range(anyCellListed.Value).Locked = True
End If
.Protect Password:=nonAuditWSPassword
End With
'record the locked status on the Audit Trail sheet
With auditWS
.Unprotect Password:=auditWSPassword
.Range(awsLockedCol & anyCellListed.Row) = "Locked"
.Protect Password:=auditWSPassword
End With
End If
End If
Next

Application.DisplayAlerts = False
savingNowFlag = True
ThisWorkbook.Save
savingNowFlag = False
Application.DisplayAlerts = True

'housekeeping - release objects back to the system for reuse
Set CellListRange = Nothing
Set anyCellListed = Nothing
Set auditWS = Nothing
End Sub

Private Sub Workbook_Activate()
'the _Activate event is triggered when:
' you open the workbook, or
' you switch back to this workbook after
' using another workbook that is also open.
'
With Application.CommandBars("Cell")
.Controls("Cut").Visible = False
.Controls("Copy").Visible = False
.Controls("Paste").Visible = False
.Controls("Paste Special...").Visible = False
.Controls("Insert...").Visible = False
.Controls("Delete...").Visible = False
.Controls("Clear Contents").Visible = False
.Controls("Filter").Visible = False
.Controls("Sort").Visible = False
.Controls("Insert Comment").Visible = False
.Controls("Format Cells...").Visible = False
.Controls("Pick from drop-down list...").Visible = False
.Controls("Name a Range...").Visible = False
.Controls("Hyperlink...").Visible = False
' .Controls("Look up...").Visible = False
End With
End Sub

Private Sub Workbook_Deactivate()
'the _Deactivate event is triggered when:
' you close the workbook, or
' you switch to use another workbook that is also open.
'
With Application.CommandBars("Cell")
.Controls("Cut").Visible = True
.Controls("Copy").Visible = True
.Controls("Paste").Visible = True
.Controls("Paste Special...").Visible = True
.Controls("Insert...").Visible = True
.Controls("Delete...").Visible = True
.Controls("Clear Contents").Visible = True
.Controls("Filter").Visible = True
.Controls("Sort").Visible = True
.Controls("Insert Comment").Visible = True
.Controls("Format Cells...").Visible = True
.Controls("Pick from drop-down list...").Visible = True
.Controls("Name a Range...").Visible = True
.Controls("Hyperlink...").Visible = True
' .Controls("Look up...").Visible = False
End With
End Sub

Copy the ShowRibbon() &

Submitted by Vishesh on 29 October, 2011 - 10:21.

Copy the ShowRibbon() & HideRibbon() procedures in a general module and call them from any where in your code. In your code above I don't see these two functions to show/hide the ribbon.

You can try these functions in a new workbook. Copy Paste the following code in a workbook module and save it as a .xlsm file. When you open the file the ribbon is hidden and while you close the file ribbon is back.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",true)"
End Sub

Private Sub Workbook_Open()
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",false)"
End Sub

Making a stop

Submitted by Anonymous on 11 September, 2011 - 22:14.

Option Explicit

Sub SigFigs()
Dim num As Single 'This is an input, the number we want to reformat
Dim sigs As Integer 'This is an input, the desired number of sig. figs.
Dim numround As Double 'This is the answer

Dim exponent As Integer

num = InputBox("Enter a number:")
sigs = InputBox("Enter the number of significant figures you wish to display:")
If sigs < 0 Then
MsgBox ("ERROR")

ElseIf num = 0 Then ' We don't have to do anything with zero. Zero is just zero.
numround = 0
Else
' Below helps us determine if we're going to have to round to the
' right or two the left of the decimal point. Don't sweat out understanding
' this expression if it confuses you.
exponent = 1 + Int(Log(Abs(num)) / Log(10))

' Below makes uses the Excel function to do the rounding.
' Using Excel functions in VBA is something we will
' talk about later in more detail.
numround = WorksheetFunction.Round(num, sigs - exponent)

End If

MsgBox (numround)

End Sub
This my program for significant figures. I need to create a line by where I put my first if statement to make the program not run if I put in a value for the number of significant figures as a negative value. What should I do?

Chk this out... Sub

Submitted by Vishesh on 29 October, 2011 - 10:54.

Chk this out...

Sub SigFigs()
Dim num As Single 'This is an input, the number we want to reformat
Dim sigs As Integer 'This is an input, the desired number of sig. figs.
Dim numround As Double 'This is the answer

Dim exponent As Integer

num = InputBox("Enter a number:")
sigs = InputBox("Enter the number of significant figures you wish to display:")

If sigs < 0 Then
MsgBox ("ERROR")
Exit Sub 'New Addition to the code
ElseIf num = 0 Then ' We don't have to do anything with zero. Zero is just zero.
numround = 0
Else
' Below helps us determine if we're going to have to round to the
' right or two the left of the decimal point. Don't sweat out understanding
' this expression if it confuses you.
exponent = 1 + Int(Log(Abs(num)) / Log(10))

Excel VBA tests

Submitted by Anonymous on 7 March, 2012 - 13:01.

Hi Nick

Don't know about comments, but you can test your VBA knowledge at http://www.skills-assessment.net/home/frmIndex.aspx?e=13.

Andy (Brown)

Excel / VBA Consultancy

Free Training Videos

Free Spreadsheets

Current Excel / VBA Jobs

Excel / VBA Jobs

Navigation

Who's online

New Excel Experts

Nick's guide to Excel / VBA Interview Questions

Last viewed:

Recent Blogs

Forums

Recent comments

User login