Problems Loading Large String Array into Array variable


E

ExcelMonkey

I am trying to load a very large string array into an array variable. I am
having problems as I have too many line continuations (i.e. lines which end
with "_"). Is there a bettter way to do this. I want to keep the list in my
VBA code as opposed to puttng it in a text file.

TempArray =
Array("A","ABLE","ABOUT","ABOVE","ACCORDING","ACCOUNT","ACROSS","ACT","ACTION","ACTIVITIES","ACTIVITY","ACTUALLY","ADDED","ADDITION","ADDITIONAL","ADMINISTRATION","AFTER","AGAIN","AGAINST","AGE","AGO","AHEAD","AID","AIR","ALL","ALMOST","ALONE","ALONG","ALREADY","ALSO","ALTHOUGH","ALWAYS","AM","AMERICA","AMERICAN","AMONG","AMOUNT","AN","ANALYSIS","AND","ANOTHER","ANSWER","ANTI","ANY","ANYONE","ANYTHING","APPARENTLY","APPEAR","APPEARED","APPROACH","ARE","AREA","AREAS","ARMS","ARMY","AROUND","ART","AS","ASK","ASKED","ASSOCIATION","AT","ATTACK","ATTENTION","AUDIENCE","AVAILABLE","AVERAGE","AWAY","B","BACK","BAD","BALL","BASED","BASIC","BASIS","BE","BEAUTIFUL","BECAME","BECAUSE","BECOME","BED","BEEN","BEFORE","BEGAN","BEGINNING","BEHIND","BEING","BELIEVE","BELOW","BEST","BETTER","BETWEEN","BEYOND","BIG","BILL","BLACK","BLOOD","BLUE","BOARD","BODY","BOOK","BORN","BOTH","BOY","BOYS","BRING","BRITISH","BROUGHT","BROWN","BUILDING","BUILT","BUSINESS","BUT","BY","C","CALL","CALLED","CAME","CAN","CANNOT","CANT","CAR","CARE","CARRIED","CARS","CASE","CASES","CAUSE","CENT","CENTER","CENTRAL","CENTURY","CERTAIN","CERTAINLY","CHANCE","CHANGE","CHANGES","CHARACTER","CHARGE","CHIEF","CHILD","CHILDREN","CHOICE","CHRISTIAN","CHURCH","CITY","CLASS","CLEAR","CLEARLY","CLOSE","CLOSED","CLUB","CO","COLD","COLLEGE","COLOR","COME","COMES","COMING","COMMITTEE","COMMON","COMMUNIST","COMMUNITY","COMPANY","COMPLETE","COMPLETELY","CONCERNED","CONDITIONS","CONGRESS","CONSIDER","CONSIDERED","CONTINUED","CONTROL","CORNER","CORPS","COST","COSTS","COULD","COULDNT","COUNTRIES","COUNTRY","COUNTY","COUPLE","COURSE","COURT","COVERED","CUT","D","DAILY","DARK","DATA","DAY","DAYS","DE","DEAD","DEAL","DEATH","DECIDED","DECISION","DEEP","DEFENSE","DEGREE","DEMOCRATIC","DEPARTMENT","DESCRIBED","DESIGN","DESIGNED","DETERMINED","DEVELOPED","DEVELOPMENT","DID","DIDNT","DIFFERENCE","DIFFERENT","DIFFICULT","DIRECT","DIRECTION","DIRECTLY","DISTANCE","DISTRICT","DO","DOES","DOING","DONE","DONT","DOOR","DOUBT","DOWN","DR","DRIVE","DUE","DURING","E","EACH","EARLIER","EARLY","EARTH","EAST","EASY","ECONOMIC","EDUCATION","EFFECT","EFFECTIVE","EFFECTS","EFFORT","EFFORTS","EIGHT","EITHER","ELEMENTS","ELSE","END","ENGLAND","ENGLISH","ENOUGH","ENTIRE","EQUIPMENT","ESPECIALLY","ESTABLISHED","EUROPE","EVEN","EVENING","EVER","EVERY","EVERYTHING","EVIDENCE","EXAMPLE","EXCEPT","EXISTENCE","EXPECT","EXPECTED","EXPERIENCE","EXTENT","EYE","EYES","F","FACE","FACT","FAITH","FALL","FAMILY","FAR","FARM","FATHER","FEAR","FEDERAL","FEED","FEEL","FEELING","FEET","FELT","FEW","FIELD","FIGURE","FIGURES","FILLED","FINAL","FINALLY","FIND","FINE","FIRE","FIRM","FIRST","FISCAL","FIVE","FLOOR","FOLLOWED","FOLLOWING","FOOD","FOOT","FOR","FORCE","FORCES","FOREIGN","FORM","FORMER","FORMS","FORWARD","FOUND","FOUR","FREE","FREEDOM","FRENCH","FRIEND","FRIENDS","FROM","FRONT","FULL","FUNCTION","FURTHER","FUTURE","G","GAME","GAVE","GENERAL","GENERALLY","GEORGE","GET","GETTING","GIRL","GIRLS","GIVE","GIVEN","GIVES","GLASS","GO","GOD","GOING","GONE","GOOD","GOT","GOVERNMENT","GREAT","GREATER","GREEN","GROUND","GROUP","GROUPS","GROWING","GROWTH","GUN","H","HAD","HAIR","HALF","HALL","HAND","HANDS","HAPPENED","HARD","HAS","HAVE","HAVING","HE","HEAD","HEAR","HEARD","HEART","HEAVY","HELD","HELL","HELP","HER","HERE","HERSELF","HES","HIGH","HIGHER","HIM","HIMSELF","HIS","HISTORY","HIT","HOLD","HOME","HOPE","HORSE","HOSPITAL","HOT","HOTEL","HOUR","HOURS","HOUSE","HOW","HOWEVER","HUMAN","HUNDRED","HUSBAND","I","IDEA","IDEAS","IF","ILL","IM","IMAGE","IMMEDIATELY","IMPORTANT","IN","INCLUDE","INCLUDING","INCOME","INCREASE","INCREASED","INDEED","INDIVIDUAL","INDUSTRIAL","INDUSTRY","INFLUENCE","INFORMATION","INSIDE","INSTEAD","INTEREST","INTERNATIONAL","INTO","INVOLVED","IS","ISLAND","ISSUE","IT","ITS","ITSELF","IVE","J","JOB","JOHN","JUST","JUSTICE","KEEP","KENNEDY","KEPT","KIND","KNEW","KNOW","KNOWLEDGE","KNOWN","L","LABOR","LACK","LAND","LANGUAGE","LARGE","LARGER","LAST","LATE","LATER","LATTER","LAW","LAY","LEAD","LEADERS","LEARNED","LEAST","LEAVE","LED","LEFT","LENGTH","LESS","LET","LETTER","LETTERS","LEVEL","LIFE","LIGHT""LIKE","LIKELY","LINE","LINES","LIST","LITERATURE","LITTLE","LIVE","LIVED","LIVING","LOCAL","LONG","LONGER","LOOK","LOOKED","LOOKING","LOST","LOT","LOVE","LOW","LOWER","M","MADE","MAIN","MAJOR","MAKE","MAKES","MAKING","MAN","MANNER","MANS","MANY","MARCH","MARKET","MARRIED","MASS","MATERIAL","MATTER","MAY","MAYBE","ME","MEAN","MEANING","MEANS","MEDICAL","MEET","MEETING","MEMBER","MEMBERS","MEN","MERELY","MET","METHOD","METHODS","MIDDLE","MIGHT","MILES","MILITARY","MILLION","MIND","MINUTES","MISS","MODERN","MOMENT","MONEY","MONTH","MONTHS","MORAL","MORE","MORNING","MOST","MOTHER","MOVE","MOVED","MOVEMENT","MOVING","MR","MRS","MUCH","MUSIC","MUST","MY","MYSELF","N","NAME","NATION","NATIONAL","NATIONS","NATURAL","NATURE","NEAR","NEARLY","NECESSARY","NEED","NEEDED","NEEDS","NEGRO","NEITHER","NEVER","NEW","NEXT","NIGHT","NO","NON","NOR","NORMAL","NORTH","NOT","NOTE","NOTHING","NOW","NUCLEAR","NUMBER","NUMBERS","OBTAINED","OBVIOUSLY","OF","OFF","OFFICE","OFTEN","OH","OLD","ON","ONCE","ONE","ONES","ONLY","OPEN","OPENED","OPERATION","OPPORTUNITY","OR","ORDER","ORGANIZATION","OTHER","OTHERS","OUR","OUT","OUTSIDE","OVER","OWN","P","PAID","PAPER","PART","PARTICULAR","PARTICULARLY","PARTS","PARTY","PASSED","PAST","PATTERN","PAY","PEACE","PEOPLE","PER","PERFORMANCE","PERHAPS","PERIOD","PERSON","PERSONAL","PERSONS","PHYSICAL","PICTURE","PIECE","PLACE","PLACED","PLAN","PLANE","PLANNING","PLANS","PLANT","PLAY","POINT","POINTS","POLICE","POLICY","POLITICAL","POOL","POOR","POPULATION","POSITION","POSSIBLE","POST","POWER","PRESENT","PRESIDENT","PRESS","PRESSURE","PRICE","PRINCIPLE","PRIVATE","PROBABLY","PROBLEM","PROBLEMS","PROCESS","PRODUCTION","PRODUCTS","PROGRAM","PROGRAMS","PROGRESS","PROPERTY","PROVIDE","PROVIDED","PUBLIC","PURPOSE","PUT","QUALITY","QUESTION","QUESTIONS","QUITE","R","RACE","RADIO","RAN","RANGE","RATE","RATHER","REACHED","REACTION","READ","READING","READY","REAL","REALLY","REASON","RECEIVED","RECENT","RECENTLY","RECORD","RED","RELIGION","RELIGIOUS","REMEMBER","REPORT","REPORTED","REQUIRED","RESEARCH","RESPECT","RESPONSIBILITY","REST","RESULT","RESULTS","RETURN","RETURNED","RIGHT","RIVER","ROAD","ROOM","RUN","RUNNING","S","SAID","SALES","SAME","SAT","SAW","SAY","SAYING","SAYS","SCHOOL","SCHOOLS","SCIENCE","SEASON","SECOND","SECRETARY","SECTION","SEE","SEEM","SEEMED","SEEMS","SEEN","SELF","SENSE","SENT","SERIES","SERIOUS","SERVED","SERVICE","SERVICES","SET","SEVEN","SEVERAL","SHALL","SHE","SHORT","SHOT","SHOULD","SHOW","SHOWED","SHOWN","SIDE","SIMILAR","SIMPLE","SIMPLY","SINCE","SINGLE","SITUATION","SIX","SIZE","SLOWLY","SMALL","SO","SOCIAL","SOCIETY","SOME","SOMETHING","SOMETIMES","SOMEWHAT","SON","SOON","SORT","SOUND","SOUTH","SOUTHERN","SOVIET","SPACE","SPEAK","SPECIAL","SPECIFIC","SPIRIT","SPRING","SQUARE","ST","STAFF","STAGE","STAND","STANDARD","START","STARTED","STATE","STATEMENTS","STATES","STAY","STEP","STEPS","STILL","STOCK","STOOD","STOP","STOPPED","STORY","STRAIGHT","STREET","STRENGTH","STRONG","STUDENT","STUDENTS","STUDY","SUBJECT","SUCH","SUDDENLY","SUMMER","SUN","SUPPORT","SURE","SURFACE","SYSTEM","SYSTEMS","T","TABLE","TAKE","TAKEN","TAKING","TALK","TAX","TECHNICAL","TELL","TEMPERATURE","TEN","TERM","TERMS","TEST","TH","THAN","THAT","THATS","THE","THEIR","THEM","THEMSELVES","THEN","THEORY","THERE","THEREFORE","THERES","THESE","THEY","THING","THINGS","THINK","THINKING","THIRD","THIRTY","THIS","THOSE","THOUGHT","THREE","THROUGH","THROUGH","THROUGHOUT","THUS","TIME","TIMES","TO","TODAY","TOGETHER","TOLD","TOO","TOOK","TOP","TOTAL","TOWARD","TOWN","TRADE","TRAINING","TREATMENT","TRIAL","TRIED","TROUBLE","True","TRUTH","TRY","TRYING","TURN","TURNED","TWENTY","TWO","TYPE","TYPES","U","UNDER","UNDERSTAND","UNDERSTANDING","UNION","UNITED","UNIVERSITY","UNTIL","UP","UPON","US","USE","USED","USING","USUALLY","VALUE","VALUES","VARIOUS","VERY","VIEW","VOICE","VOLUME","WAITING","WALKED","WALL","WANT","WANTED","WAR","WAS","WASHINGTON","WASNT","WATER","WAY","WAYS","WE","WEEK","WEEKS","WELL","WENT","WERE","WEST","WESTERN","WHAT","WHATEVER","WHEN","WHERE","WHETHER","WHICH","WHILE","WHITE","WHO","WHOLE","WHOM","WHOSE","WHY""WIDE","WIFE","WILL","WILLIAM","WINDOW","WISH","WITH","WITHIN","WITHOUT","WOMAN","WOMEN","WORD","WORDS","WORK","WORKED","WORKING","WORKS","WORLD","WOULD","WOULDNT","WRITING","WRITTEN","WRONG","WROTE","YEAR","YEARS","YES","YET","YORK","YOU","YOUNG","YOUR","YOURE")

Thanks

EM
 
Ad

Advertisements

D

Dave Peterson

Have you thought about putting all those values in a single column in a
(hidden???) worksheet?

I find that it makes it easier to update, too.

dim TempArray as variant
with worksheets("somehiddensheethere")
temparray = .range("A1"), .cells(.rows.count,"A").end(xlup))
end with

for iCtr = lbound(temparray, 1) to ubound(temparray,1)
msgbox temparray(ictr,1)
next ictr

========
When you pick up values from a worksheet range like this, you'll end up with a
#rows by #columns array. In this case, it's just #rows by 1 column, but it's
still got that column dimension.
 
G

GS

Could you store it in a comma delimited string and access individual elements
using the
Split() function rather than iterating the array with a For..Next loop?

HTH
Garry
 
E

ExcelMonkey

Yes but I am running into the same problem with line continuation. I think I
have to do the following:


temparray = "A,ABLE,ABOUT,ABOVE,ACCORDING,ACCOUNT,ACROSS,ACT,ACTION"
temparray = temparray & "ACTIVITIES,ACTIVITY,ACTUALLY,ADDED,ADDITION"
etc
etc

Thanks

EM
 
G

GS

Yes, that exactly what I would do. Sorry I did not give an example.

Dim sTemp As String
sTemp = "" 'initialize
sTemp = sTemp & "my,values,here"
sTemp = sTemp & "continue,myvalues,fromhere"
sTemp = sTemp ...and so on

Kind regards,
Garry
 
Ad

Advertisements

G

GS

Oops! I missed the leading comma after the first string. Sorry about that!
The sample should read:

Dim sTemp As String
sTemp = "" 'initialize
sTemp = sTemp & "my,values,here"
sTemp = sTemp & ",continue,myvalues,fromhere"
sTemp = sTemp ...and so on

This makes it easier to update and ensures the final string doesn't end with
a comma.

Garry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top