Solved

Excel VBA code explanation

Posted on 2013-10-30
3
565 Views
Last Modified: 2013-10-30
I have inherited this function from someone else. Could I get some assistance as to what this is doing?  Especially the lines…
SumOrCat = WorksheetFunction.VLookup(QuestCode, Sheets("Data").Range("$A$1:$H$496"), 4, False)
    s1stWorstCase = WorksheetFunction.VLookup(QuestCode, Sheets("Data").Range("$A$1:$H$496"), 5, False)
    s2ndWorstCase = WorksheetFunction.VLookup(QuestCode, Sheets("Data").Range("$A$1:$H$496"), 6, False)
    s3rdWorstCase = WorksheetFunction.VLookup(QuestCode, Sheets("Data").Range("$A$1:$H$496"), 7, False)
    s4thWorstCase = WorksheetFunction.VLookup(QuestCode, Sheets("Data").Range("$A$1:$H$496"), 8, False)
May be a parameter break down.

Here is the function…
Public Function GetWorstCase(ByVal QuestCode As String, ByVal LatestValue As String, ByRef CurrWorstCase) As String
    Dim s1stWorstCase As String
    Dim s2ndWorstCase As String
    Dim s3rdWorstCase As String
    Dim s4thWorstCase As String
    Dim SumOrCat As String
   
    On Error GoTo ErrorHandler
   
    SumOrCat = WorksheetFunction.VLookup(QuestCode, Sheets("Data").Range("$A$1:$H$496"), 4, False)
    s1stWorstCase = WorksheetFunction.VLookup(QuestCode, Sheets("Data").Range("$A$1:$H$496"), 5, False)
    s2ndWorstCase = WorksheetFunction.VLookup(QuestCode, Sheets("Data").Range("$A$1:$H$496"), 6, False)
    s3rdWorstCase = WorksheetFunction.VLookup(QuestCode, Sheets("Data").Range("$A$1:$H$496"), 7, False)
    s4thWorstCase = WorksheetFunction.VLookup(QuestCode, Sheets("Data").Range("$A$1:$H$496"), 8, False)
   
    GetWorstCase = LatestValue
Exit Function

ErrorHandler:
   MsgBox "Error: " & Err.Description

End Function

Sub test()
    Debug.Print GetWorstCase("GQ-001", "Yes", "No")
End Sub

Thanks in advance.

I have attached this problem statement in a Word document so this can be seen better.
Thanks.
Get-Worse-Case-function.docx
0
Comment
Question by:tesla764
3 Comments
 
LVL 15

Accepted Solution

by:
Ess Kay earned 250 total points
ID: 39611831
There are 5 variables being filled here, highlighted in BOLD
They all call a function WorksheetFunction.VLookup
Everything in the function is the same except the 3rd parameter, which is 4,5,6,7 or 8

They all select CELLS from A1 to H496

You need to look up the VLookup function to see what it does.



SumOrCat = WorksheetFunction.VLookup(QuestCode, Sheets("Data").Range("$A$1:$H$496"), 4, False)

    s1stWorstCase = WorksheetFunction.VLookup(QuestCode, Sheets("Data").Range("$A$1:$H$496"), 5, False)

    s2ndWorstCase = WorksheetFunction.VLookup(QuestCode, Sheets("Data").Range("$A$1:$H$496"), 6, False)

    s3rdWorstCase = WorksheetFunction.VLookup(QuestCode, Sheets("Data").Range("$A$1:$H$496"), 7, False)

    s4thWorstCase = WorksheetFunction.VLookup(QuestCode, Sheets("Data").Range("$A$1:$H$496"), 8, False)
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 250 total points
ID: 39611841
That section is using the worksheet function VLOOKUP to find QuestCode in column A of the range $A$1:$H$496 on the 'Data' worksheet.

If the QuestCode is found in a row corresponding values from other columns are returned to various variables.

The value from column D of the row QuestCode is found is returned to SumOrCat.

The values from columns E, F, G and H are returned to s1stWorstCase, s2ndWorstCase, s3rdWorstCase, and s4thWorstCase respectively.

This is the general syntax fo VLOOKUP.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
0
 

Author Closing Comment

by:tesla764
ID: 39611903
Thanks.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This collection of functions covers all the normal rounding methods of just about any numeric value.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now