Global variables, etc

[Guest]

Hi,

I have a rather general question due to lack of any programming background.

There is a database of 70 columns and I have various macros working with it.
The below this piece of code i have to repeat in every one of them and I
feel it could be done ones and for all. What's the best way to do it?
******************
'setup
shName = Application.InputBox("Enter the name of the data sheet:")
Set dataSh = Worksheets(shName)

Set HEADINGS = ActiveSheet.Range("a1").Resize(1, Range("a1").End
_(xlToRight).Column)

With HEADINGS
Set QUOTE_ID = Cells.Find("QUOTE_ID", MatchCase:=False)
Set QUOTEDATE = Cells.Find("QUOTEDATE", MatchCase:=False)
Set Name_ID = Cells.Find("Name_ID", MatchCase:=False)
Set POLICY_ID = Cells.Find("POLICY_ID", MatchCase:=False)
Set PREVPOLICY = Cells.Find("PREVPOLICY", MatchCase:=False)
'..... Set up other columns as needed
End With
*************

Thanks ,

 

[Dave Peterson]

Uncompiled--watch for typos!

dim myCols() as long
dim myStrings as variant
dim iCtr as long
dim dataSh as worksheet
dim FoundCell as Range
dim ErrFound as boolean

mystrings = array("QUOTE_ID", _
"QUOTEDATE", _
"Name_ID", _
"POLICY_ID", _
"PREVPOLICY")
'etc...

redim myCols(lbound(mystrings) to ubound(mystrings))

Set dataSh = Worksheets(shName)


with datash
errFound = false
with .rows(1)
for ictr = lbound(mystrings) to ubound(mystrings)
set foundcell = .cells.find(what:=mystrings(ictr), ....)
if foundcell is nothing then
msgbox "what should happen?
errfound = true
else
mycols(ictr) = foundcell.column
end if
next ictr
end with

if errfound = true then
exit sub '??
else
for ictr = lbound(mycols) to ubound(mycols)
with .columns(mycols(ictr))
'do something in that column
end with
next ictr
end if
end with

=======
Just some thoughts...

#1.

Set HEADINGS = ActiveSheet.Range("a1").Resize(1, Range("a1").End
_(xlToRight).Column)

Did you really mean the ActiveSheet--or the dataSh??

#2.

With HEADINGS
Set QUOTE_ID = Cells.Find("QUOTE_ID", MatchCase:=False)

You don't have a dot in front of the cells.find() line. This means that all of
the activesheet is used--not just the row that HEADINGS defines.

And if you don't specify all the parms in your .find statement, you'll be at the
mercy of the previous find. Excel will keep the same settings as the last Find
(manual or in code) that was used. This may not be what you want. It's better
to specify exactly what you want (xlwhole, xlpart, ...)

And depending on what you're doing, it might even be better to create a giant
range of those found cells and apply what you want--formatting???



dim myStrings as variant
dim iCtr as long
dim dataSh as worksheet
dim FoundCell as Range
dim ErrFound as boolean
dim AllFound as range

mystrings = array("QUOTE_ID", _
"QUOTEDATE", _
"Name_ID", _
"POLICY_ID", _
"PREVPOLICY")
'etc...

Set dataSh = Worksheets(shName)

with datash
errFound = false
with .rows(1)
for ictr = lbound(mystrings) to ubound(mystrings)
set foundcell = .cells.find(what:=mystrings(ictr), ....)
if foundcell is nothing then
msgbox "what should happen?
errfound = true
else
if allfound is nothing then
set allfound = foundcell
else
set allfound = union(foundcell, allfound)
end if
end if
next ictr
end with
end with

if errfound = true then
'still not sure
else
'do what you want
allfound.entirecolumn.font.bold = true
end if

 

[Guest]

Dave,

Thank you for your reply.

I did mean dataSh not ActiveSheet and I should have had a dot in front of
Cells.Find and more paramenters of course. You are correct in pointing out
all that.

But i guess my question was that "dataSh" variable and all "HEADINGS"
variables (which are unique and used to indentify columns) are used in
different subs. I wanted to find a way to make them public and retain value
once and for all subs indead of running this "set up" code in every
prosedure. All the subs could be ran in random order depending on user's
intent, e.g., get rid of duplicates, sort, or lookup.

I hope my explanation is making sense.

Thanks again,

 

[Dave Peterson]

Declare myCols outside the procedure:

Public myCols() as long

===
But there are things that can cause the variable to be reset.

You may want to put the finds in a separate subroutine and then check each time
before you need it:

Option Explicit
Public myCols() As Long
Public myVarsAreInitialized As Boolean
Sub testme01()

If myVarsAreInitialized = True Then
'do nothing, they should be ok.
Else
Call InitializeTheVariables
End If

End Sub

'do all the initialization
Sub InitializeTheVariables()
'do the .finds and whatever more you may need.
myVarsAreInitialized = True
End Sub

Dave,

Thank you for your reply.

I did mean dataSh not ActiveSheet and I should have had a dot in front of
Cells.Find and more paramenters of course. You are correct in pointing out
all that.

But i guess my question was that "dataSh" variable and all "HEADINGS"
variables (which are unique and used to indentify columns) are used in
different subs. I wanted to find a way to make them public and retain value
once and for all subs indead of running this "set up" code in every
prosedure. All the subs could be ran in random order depending on user's
intent, e.g., get rid of duplicates, sort, or lookup.

I hope my explanation is making sense.

Thanks again,