Re: Where to place Dim Satements
From: Dave Peterson (ec35720_at_msn.com)
Date: 02/28/04
- Next message: Dave Peterson: "Re: Stopping other wkbks sharing code"
- Previous message: Frank Kabel: "Re: Need to automatically input date one time"
- In reply to: rob nobel: "Re: Where to place Dim Satements"
- Next in thread: Chip Pearson: "Re: Where to place Dim Satements"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 28 Feb 2004 06:23:34 -0600
I'm not sure how he response stuff would fit in--if you want to quit, you can
exit right away.
But if you wanted to pass it that parm, you could do something like:
Dim Response as long
....
response = msgbox(prompt:="whatever",buttons:=vbyesno)
if doSomethingElse(response) = true then
'something completely different
else
'something else completely
end if
....
function dosomethingelse(whattheyanswered as long) as boolean
if whattheyanswered = vbyes then
'do a bunch of things
dosomethingelse = true
else
'do a bunch of other things
dosomethingelse = false
end if
end function
I don't like this example, but you may be able to see how you can pass parms to
subroutines or functions.
A better example that may actually do something useful is a function posted by
Myrna Larson:
http://google.com/groups?threadm=oh7ffv004p0ri44qqpm7iasdjrameice0q%404ax.com
This is a snippet:
Private Function GetFileList(Pattern As String, FileNames() As String) As Long
Dim f As String
Dim n As Integer
n = 0
Erase FileNames()
f = Dir$(Pattern)
Do While Len(f)
n = n + 1
ReDim Preserve FileNames(1 To n) As String
FileNames(n) = f
f = Dir$()
Loop
GetFileList = n
End Function 'GetFileList
You pass it the pattern ("c:\my documents\excel\*.xls") and reserve room for
returning filenames and the function returns the number of files that match.
(It also gives the names back in that second passed parameter.)
Dim filelist() as string
dim iCtr as long
'...do lots of stuff
if getfilelist("c:\my documents\excel\*.xls", filelist()) > 0 then
for ictr = lbound(filelist) to ubound(filelist)
'do something to each file
next ictr
end if
===========
What variable you should use depends on what you're working with:
If it's a whole number, you can use Long
if it's a decimal number, Double
If it's a string, String
You may want to invest in a book:
A lot of people like John Walkenbach's:
Excel 2003 Power Programming with VBA
You can find a whole list at Debra Dalgleish's site:
http://www.contextures.com/xlbooks.html
Maybe you could print it and visit your local bookstore to see if you can find
one you like.
rob nobel wrote:
>
> Thanks Dave.
> I'm constantly in the process of learning how to process VBA code better and
> your comments are appreciated.
> I am always trying to make the code smaller, but in the past, and still
> today, I seem to have some trouble with how variables should be defined,
> etc. In the course of transferring parts of code to modules or separate
> subs, I find sometimes that the value in a variable doesn't work any more.
> For instance ...
> "If Response = vbNo Then Exit Sub"
> with some code below that is common to a number of subs....
> I've tried to following code in a separate sub or module but find that the
> procedure "loses" the answer to the response.
> Another trouble I have is how to declare a variable, as sometimes what I
> think should be an integer needs to be a string, etc. What variable should
> you use in what circumstances?
>
> Rob
>
> "Dave Peterson" <ec35720@msn.com> wrote in message
> news:403FEDBC.5F432C66@msn.com...
> > I'm not sure how much you can reuse, but someday you'll want to make your
> > routines more modular (smaller). Instead of a giant routine (single
> proc), you
> > can have functions and subs that get passed info that they need.
> >
> > Then these little procedures would be complete and easily transportable to
> other
> > projects.
> >
> > For instance, Chip Pearson's function to test if a worksheet exists:
> >
> > Function WorksheetExists(SheetName As String, _
> > Optional WhichBook As Workbook) As Boolean
> > Dim WB As Workbook
> > Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
> > On Error Resume Next
> > WorksheetExists = Len(WB.Worksheets(SheetName).Name) > 0
> > End Function
> >
> > Can be dropped into any project and you just have to pass it the correct
> parms.
> >
> > rob nobel wrote:
> > >
> > > I somehow knew you were going to say that Dave, and it does make sense
> to do
> > > that.
> > > The reason I asked is that quite often parts of code are quite often
> used in
> > > other projects and if the "complete" code is kept together than I would
> have
> > > thought it would make it easy to transfer. I guess one could always
> save
> > > the "complete" code separately somewhere and import when needed.
> > >
> > > Rob
> > >
> > > "Dave Peterson" <ec35720@msn.com> wrote in message
> > > news:403EB867.331EF90A@msn.com...
> > > > I like to put my Dim's at the top of the procedure. It makes it
> easier to
> > > find.
> > > >
> > > > If I inherited a workbook that had dim's sprinkled through each
> procedure,
> > > then
> > > > I think one of the first things I'd do is move them up.
> > > >
> > > > rob nobel wrote:
> > > > >
> > > > > Some code I have seems to be pages long and the dim statements are
> at
> > > the
> > > > > beginning of the Sub.
> > > > > Most of the Dim statements are relative only to a small part of the
> > > code.
> > > > > Is it bad practice to have such dim statements immediately above the
> > > code
> > > > > they refer to?
> > > > > Rob
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > > ec35720@msn.com
> >
> > --
> >
> > Dave Peterson
> > ec35720@msn.com
-- Dave Peterson ec35720@msn.com
- Next message: Dave Peterson: "Re: Stopping other wkbks sharing code"
- Previous message: Frank Kabel: "Re: Need to automatically input date one time"
- In reply to: rob nobel: "Re: Where to place Dim Satements"
- Next in thread: Chip Pearson: "Re: Where to place Dim Satements"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
