4 quick questions (including basic vlookup)

[venomatic]

Hello gentlemen, hopefully I can draw on your expertise again
Thank you sincerely in advance! Starting with the easiest:


1) I know we can get the size of an external file, but could we also open up an external file from VBE, e.g. a movie? From what I've Googled so far, the Shell function seems to be appropriate for the task.


2) Recall that ActiveWindow.DisplayGridlines = False can turn off gridlines in a worksheet. I was just curious, is ActiveWindow an object or a property (that returns an object, i.e. DisplayGridLines)?


3) So with some common sense, I notice you can name a specified range (i.e. right click). So, is there a formal way to undo it, or in other words, is there a list of named ranges, just like how we can access our macros from alt+F8?


4) And finally, my real question
So with some common sense again (and F1), one can easily use VLOOKUP from the worksheet itself (no VBE). But I'm having trouble using VLOOKUP from VBE due to getting a run-time error; for example:

Suppose Sheet1 has two columns, "Item#" and its corresponding "Price". Recall that if the Item# column is sorted (ascending) and we specify a range, we can easily use VLOOKUP to determine the corresponding Price for the desired Item. I believe this specified range/table_array can include the identifying top labels if you like. For example:

Item# Price
03_____ 2
18_____ 3.99
56_____ 1.5
66_____ 5
75_____ 5.2
94_____ 4
(Item# is @ A1)

My flawed attempt:

Sub DeterminePrice()
 
'comments denote optional code
 
Dim PartNum 'As Variant
 
Dim Price As Double
 
PartNum = InputBox("Enter the Item #")
 
'WorkSheets("Sheet1").Activate
 
Price = WorksheetFunction.VLookup(PartNum, Range("A1:B7"), 2, False)
 
MsgBox PartNum & " costs " & Price
 
End Sub

Maybe PartNum shouldn't have replaced Item, but I think there are some other issues that I have yet to uncover after playing around with the variables. Anyhow, any help with a clean fix or alternative will be appreciated! Cheers!

 

[Ralajer]

1. The shell function is a way to open files with specific programs. I would recommend not calling directly in you code but rather to use a wrapper function that does some cleanup and error checking. Like the simple one below

   Function ShellWrapper(programToRun As String, Optional programSwitches As String, _
                           Optional FileToOpen As String) As Double
   
       Dim executeStr As String
       '// Add quotes to path as if a space is string it will not open correctly
       '// Doesn't effect if quotes are there and not needed
       executeStr = """" & programToRun & """"
       
       '// If programSwitches is not passed just open program normally
       If Not IsMissing(programSwitches) And programSwitches <> "" Then
           executeStr = executeStr & " " & programSwitches
       End If
       
       '// If FileToOpen is not passed just open program
       If Not IsMissing(FileToOpen) And FileToOpen <> "" Then
           executeStr = executeStr & " " & """" & FileToOpen & """"
       End If
       On Error Resume Next
       ShellWrapper = Shell(executeStr, vbNormalFocus)
       On Error GoTo 0
       
       '// Returns 0 if the shell command failed otherwise the process id
   End Function

2. Using the Object Browser (F2 in VBA Editor) and Help the ActiveWindow is listed as a property and will return a Window object. Although, I have never really used the window object to do anything interesting.

   Sub AWTest()
       Dim AW_Test As Window
       Set AW_Test = ActiveWindow
       '// Hide ActiveWindow for 5 seconds
       AW_Test.Visible = False
       Application.Wait Time + TimeSerial(0, 0, 5)
       AW_Test.Visible = True
   End Sub

3. A list of named ranges can be found in the Name Manager (ctrl+F3) pretty sure the the short cut works before 2007 but haven't used 2003 in a few years.
4. The inputbox Function was returning a string but the lookup values are numbers so it wasn't matching. The below fixes it for this case Using the totally different application.inputbox method.

   Sub DeterminePrice()
    
   'comments denote optional code
        
       Dim PartNum 'As Variant
       Dim LookUpRange As Range
       Dim Price As Double
       
       '// InputBox Function VS Application.InputBox Method
       '// The first will always return a value of type string
       '// The second can specify the input type in this case type 1 = number
       '//   and return as type specified
       PartNum = Application.InputBox(Prompt:="Enter the Item #", _
                                       Title:="Price LookUp", _
                                       Type:=1)
       '// Used a range variables as is easier to find and change sheet and cell references
       '// when references are not in functions
       Set LookUpRange = Worksheets("Sheet1").Range("A1:B7")
        
       '//Vlookup was erroring because you were looking for a string in a list of numbers
       Price = WorksheetFunction.VLookup(PartNum, LookUpRange, 2, False)
       
       MsgBox PartNum & " costs " & Price
    
   End Sub

 

[venomatic]

3) sweet!
4) Ah, no wonder! As long as the left-hand-side Items were strings, the program could've ran, regardless of any variable replacements or whether the range included the labels or not. Furthermore, I imagine one could've converted the integers to strings somehow: http://www.mrexcel.com/forum/showthread.php?t=72759).


Regarding the alternative (and better) code, I didn't know "Range" can be a data type, and how the Application.InputBox Method is a better substitute for the InputBox function (although I think its funny how the former is still a part of the Application object). I am curious what the other types are; e.g., if type 1 is for numbers and 2 is for strings (I believe), what are the others?

The Set and the := method notation are also new to me, although they seem self-explanatory. Regarding the former, it seems that Set is an alternative to naming the range from the worksheet (i.e. right click), but is required for assignment. I'm not too sure why, although my guess is that Set is required for assigning a variable to an object, but not for methods (at least it seems that way to me from my past modules).

 

[Ralajer]

There a couple really useful objects that I tend to use all the time, Range, Worksheet, Workbook

Dim ws As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")

They are very useful when you working across multiple sheets or workbooks. Plus any range reference without a worksheet will default to the ActiveSheet which can be a problem if you have to copy from one sheet to another or check the values on another sheet and can't control what sheet the macro get called from.

Range("A1") == Worksheets("Sheet1").Range("A1") if the active sheet is "Sheet1" otherwise it will be Range("A1") for whatever sheet has focus.

From VBA Help Application.InputBox Method. The types are

0 - A formula
1 - A number
2 - Text (a string)
4 - A logical value (True or False)
8 - A cell reference, as a Range object
16 - An error value, such as #N/A
64 - An array of values​

You are correct about the Set statement it is required when assigning an object to a variable.

The := is not required but does make thing easier to follow when a function or method has a lot of arguments. Especially when you don't need to use all of them. The below is the same call to the inputbox as above but with out the (argument name):=

Application.InputBox("Enter the Item #", "Price LookUp", , , , , , 1)

 

[venomatic]

this is very practical material that I wished my guides were clearer on, although I probably should've hit F1 more myself;


anyhow, very nice work as usual this clears up alot of things!