Solved

Store vlookup data in macro code?

Posted on 2007-03-28
6
234 Views
Last Modified: 2012-06-27
I've got a macro that references a static spreadsheet for a vlookup in my Personal.XLS macro folder.  Now whenever I start or shut down Excel, it asks me if I want to update or save the changes to my Personal Macro Workbook.  Very annoying.  Is there some way to move a simple two column, 60 row dataset into the macro itself?
0
Comment
Question by:Rossamino
  • 4
  • 2
6 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 18810920
There are ways but it might be easier to set the IsAddin property of your Personal workbook to True - that will eliminate that prompt. To do so, open Personal.xls, press ALT+F11, open the ThisWorkbook code module, press F4 to open the Properties window, find the IsAddin property and set it to True.

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 18810943
If you want to create a two dimensional array of constants in your macro then use this syntax:

   Dim X As Variant
   X = [{1,2;3,4;5,6}]

where commas separate values in a row and the semicolon separates rows.

Kevin
0
 

Author Comment

by:Rossamino
ID: 18811715
zorvek, Assuming I do this:

Dim arStates As Variant
arStates = [{Alabama,AL;Alaska,AK;...Wyoming,WY}]

how would a guy go about referencing arStates in a vlookup?

the current code looks like this (its all on one line):
    Range("M2").Value = "=if(iserror(vlookup(L2,'C:\[States.xls]States'!B$1:B$59,1,0)), if(iserror(vlookup(L2,'C:\[States.xls]States'!A$1:B$59,2,0)), concatenate(""zz"" & L2), vlookup(L2,'C:\[States.xls]States'!A$1:B$59,2,0)), vlookup(L2,'C:\[States.xls]States'!B$1:B$59,1,0))"

It seems to me that I'd have to rewrite the vlookup function in VBA and apply the result directly instead of pasting the function.  Yes?
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 18811774
You can't use most Excel functions in VBA with arrays. I suggest using a collection:

   Dim StateNames As Collection
   Dim StateAbreviation As String
   
   Set StateNames = New Collection
   StateNames.Add "CA", "California"
   StateNames.Add "TX", "Texas"
   StateNames.Add "FL", "Florida"
   
   StateAbreviation = StateNames("Texas")

Kevin
0
 

Author Comment

by:Rossamino
ID: 18819378
So when I build this collection, how do I go about using it if I can't use vlookup on the thing?
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 400 total points
ID: 18819457
Whenever you need to convert a state name to a state abbreviation use the collection like this:

   StateAbreviation = StateNames("Texas")

StateAbreviation will now be "TX".

Kevin
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 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