Yes you're right (as usual).
I guess I don't even consider it because when using a Subroutine that REQUIRES
values to be passed either by reference or by value
it essentially makes it so that the sub cannot be executed "stand alone".
That is: when you select "Macros" from the Developer menu,
you don't want it to be "selectable" because you're not able to pass the
required
parameters.
Personally, I'd rather not have it there to add to the list.
So, I prefer to use Functions.
thanks,
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: David Smart <smartware.consulting@gmail.com>
To: ExcelVBA@yahoogroups.com
Sent: Fri, July 13, 2012 10:19:14 AM
Subject: Re: [ExcelVBA] Re: Need help on a Macro to compare two spreadsheets
VBA subs can have "by value" and "by reference" parameters.
"By value" is what should be used normally, as it means that the subroutine
can't change something back in the calling code by accident.
"By reference" passes a reference to the item being passed as a parameter.
This allows the subroutine to change things back in the calling code.
"By reference" is not the same as returning a value from a function, and it
can strictly be stated that subroutines cannot "return" values, but the end
effect of a "by reference" is pretty much the same.
E.g.
WeekValue = Pad4(WeekValue)
can be done just as easily with a subroutine with a "by reference" parameter
Call Pad4(WeekValue)
and IMHO is better and slightly shorter code. However, this one
PaddedWeekValue = Pad4(WeekValue)
is easier with a function, and IMHO the function solution is better code for
this type of action.
Regards, Dave S
----- Original Message -----
From: "Derek Turner" <g4swy@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Friday, July 13, 2012 10:56 AM
Subject: Re: [ExcelVBA] Re: Need help on a Macro to compare two spreadsheets
Dear Paul
You said :-
" you cannot "pass" a value to a Subroutine, nor can you "return" a value
from a Sub"
Are you sure ?
Derek +++
>________________________________
> From: Paul Schreiner <schreiner_paul@att.net>
>To: ExcelVBA@yahoogroups.com
>Sent: Thursday, 12 July 2012, 19:14
>Subject: Re: [ExcelVBA] Re: Need help on a Macro to compare two
>spreadsheets
>
>
>
>There are LOTS of ways to determine how many rows
>are in any given worksheet.
>Which is "best" is different for any given situation.
>I'll try to describe how these two techniques work.
>
>UsedRange
>----------------
>As you add data to a worksheet, this "Range" increases in size.
>In earlier versions of Excel, when you delete rows, this range didn't
>decrease until you exit the worksheet and re-open it.
>(actually, I think it reserved the rows until the "redo" option was no
>longer
>available)
>
>However, it looks like in Excel2010, it seems to update appropriately.
>
>WorksheetFunction.Counta
>----------
>for this technique to work, your data has to be without blanks in the
>column
>you're counting.
>If you have header rows that do not have data, then this technique must be
>used
>with caution.
>
>I think for your situation, either technique may be used.
>
>Paul
>-----------------------------------------
>"Do all the good you can,
>By all the means you can,
>In all the ways you can,
>In all the places you can,
>At all the times you can,
>To all the people you can,
>As long as ever you can." - John Wesley
>-----------------------------------------
>
>________________________________
>From: danielrose02 <dan.rose@parker.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Thu, July 12, 2012 1:48:43 PM
>Subject: [ExcelVBA] Re: Need help on a Macro to compare two spreadsheets
>
>
>Paul,
>
>Thank you for your quick reply. I am looking over what you sent and be
>doing
>some learning (and experimenting) with this material. So, it might be a
>little
>while before I ask more questions. There is one item I do have a question
>about:
>
>I use this line of code to determine how many rows of data present:
>
>totalrows = ActiveSheet.UsedRange.Rows.Count
>
>However, in your example you have:
>
>nRows =
>Application.WorksheetFunction.CountA(Sheets(MasterSheetName).Range("A1:A1000000"\))
>
>
>What is the difference and which is better to use?
>
>--- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@...> wrote:
>>
>> Perhaps the problem was with the previous message..
>> I'll try it without and see how it goes:
>>
>>
>> OK...
>> I think I'm going to answer your questions in reverse order....
>> --------------------
>> Option Explicit
>> --------------------
>> Using this AS THE FIRST LINE OF EACH MODULE tells the compiler that all
>> variables have to be "declared" (using Dim, Public, Global, etc.)
>>
>> Without this, a variable doesn't HAVE to appear in a Dim statement.
>> The compiler will "automatically" declare the variable the first time it
>> is
>> used.
>> This can be dangerous because if you're using a variable called
>> "YearValue"
>> and in one place, you accidentally call it "YearValeu", the compiler will
>> not
>> recognize that as a problem. It will simply create a new variable.
>> "Option Explicit" tells the compiler that all variables have to be
>> "Explicitly"
>>
>> declared.
>> ----------------------------------------------------------
>>
>> "The DIM statements do not have DATA TYPES "
>> ----------------------------------------------------------
>>
>> Data Types are not REQUIRED.
>> If not specified, the compiler defaults to type "Variant".
>> This only becomes a problem if the value being stored in the variable
>> is sometimes a string, and other times numeric.
>> Once a value is stored in the variable, the data type is set to the type
>> of
>> data.
>> That is: YearValue = 2012 will define it as an Integer,
>> while YearValue = "2012" will define it as a String.
>> again, this isn't a problem unless you're NEEDING it as a number in
>> order to add/subtract from it.
>> ----------------------------------------------------------
>>
>> "The variable LOAD_DICT_DATA is not in any DIM statements"
>> ----------------------------------------------------------
>>
>> Load_Dict_Data is not a variable, it is a FUNCTION.
>> ----------------------------------------------------------
>>
>> "I see that there is a FUNCTION used. "
>> "Is that new MACRO?"
>> "Is that coded inside a MACRO?"
>> ----------------------------------------------------------
>>
>> The term "Macro" is actually inappropriate for VBA programming (mostly)
>> It refers to:
>> 1. A single, user-defined command that is part of an application and
>> executes a
>>
>> series of commands.
>>
>> 2. A shorthand representation for a number of lines of code.
>>
>> Yet, you'll notice that the work "Macro" doesn't actually appear in the
>> VBA
>> Editor.
>> If you choose to "record a macro", VBA calls the it a "Sub" (short for
>> "Subroutine")
>> Another type of "macro" is called a "Function".
>> The main difference between the two is that you cannot "pass" a value to
>> a
>> Subroutine,
>> nor can you "return" a value from a Sub. But you can from a Function.
>> For instance... throughout a lengthy subroutine, you periodically have
>> to modify a variable to ensure that it always is (4) characters long
>> (prefixed
>
>> with "0").
>> Rather than repeat this code whenever you need to use it, you can create
>> a
>> Function:
>>
>> Function Pad4(tValue as string)
>> while len(tValue) < 4
>> tValue ="0"&tValue
>> wend
>> Pad4 = tValue
>> End Function
>>
>> you can then use something like:
>> WeekValue = Pad4(WeekValue)
>>
>> your result is that WeekValue will now be 4 characters long (i.e. "0025")
>>
>> When writing Macros (subroutines) of significant length, it is common
>> practice
>> to place separate "activities" in their own subs of functions.
>> Like: one function for reading existing data from a spreadsheet, a
>> separate
>> function for reading new data from a CSV file.
>>
>> Another benefit is that some of these "subs" cannot be run independently.
>> They MUST be called from within another sub.
>> (it doesn't make sense to read the NEW data until after you're read the
>>EXISTING
>>
>> data)
>> When you select the list of "Macros" to run from the Excel icon,
>> functions do not appear in the list.
>> so, in one of my applications that have 50 FUNCTIONS, there is only ONE
>> that is
>>
>> available to be executed!
>> ----------------------------------------------------------
>>
>> Can these macros be stored anywhere?
>> ----------------------------------------------------------
>>
>> Where macros are stored depends on how they are to be used.
>> macros stored in a Sheet module only apply to the specific sheet.
>> These should be stored in a "Standard" module.
>> ----------------------------------------------------------
>>
>> "I receive the new data as an CSV file. "
>> "How do I set "yearvalue" and "weekvalue"?"
>> ----------------------------------------------------------
>>
>> Since a CSV file is a Comma Separated file,
>> then I would open the file,
>> read each line,
>> "split" the line using the "," delimiter
>> Set the YearValue and WeekValue based on the position in the string
>> Like:
>> ----------------------------------------------------------
>>
>> "What happens if the search does not find any thing? "
>> "How do I check for that?"
>> ----------------------------------------------------------
>>
>> I went ahead and wrote the "Add/Update" function (see below)
>> The only things you should have to change is:
>> MasterSheetName should be set to the sheet name that contains your
>> "Master
>>Data"
>> NewDataFile should be set to the name of the CSV file.
>> ----------------------------------------------------------
>>
>> ----------------------------------------------------------
>>
>> Option Explicit
>> Public Dict_Data
>> Public MasterSheetName
>> Sub Auto_Open()
>> Dim stat, nRow
>> Dim NewDataFile
>> '--------------------------------------
>> MasterSheetName = "Master_Data"
>> NewDataFile = "C:\temp\NewData.csv"
>> '--------------------------------------
>> stat = Load_Dict_Data
>> stat = Read_New(NewDataFile)
>> End Sub
>>
>> Function Load_Dict_Data()
>> Dim nRows, R, wRecord, stat
>> Set Dict_Data = CreateObject("Scripting.Dictionary")
>> stat = Dict_Data.RemoveAll
>> nRows =
>>Application.WorksheetFunction.CountA(Sheets(MasterSheetName).Range("A1:A1000000"))
>>
>>)
>>
>> For R = 2 To nRows
>> wRecord = Sheets(MasterSheetName).Cells(R, "A").Value _
>> & "_" & Sheets(MasterSheetName).Cells(R, "B").Value
>> If (Not Dict_Data.exists(wRecord)) Then
>> Dict_Data.Add wRecord, R
>> End If
>> Next R
>> Load_Dict_Data = Dict_Data.Count
>> End Function
>> Function Read_New(tDataFile)
>> Dim nRows, TextLine, TxtArray
>> Dim nRecord, DataRow
>> nRows =
>>Application.WorksheetFunction.CountA(Sheets(MasterSheetName).Range("A1:A1000000"))
>>
>>)
>>
>> '---- Open file -------------
>> Open tDataFile For Input As #1
>> '---- Loop until end of file ----------
>> Do While Not EOF(1)
>> '---- Read line into variable----
>> Line Input #1, TextLine
>> '---split line using "," delimeter------
>> TxtArray = Split(TextLine, ",")
>> '---Check to ensure there are at least (3) fields (Array index begins
>> with "0")
>> If (UBound(TxtArray) >= 2) Then
>> ' --- Check to see if value already exists ----
>> If (Not Dict_Data.exists(TxtArray(0) & "_" & TxtArray(1))) Then
>> '-- Add Record ---
>> nRows = nRows + 1
>> Sheets(MasterSheetName).Cells(nRows, "A").Value = TxtArray(0)
>> Sheets(MasterSheetName).Cells(nRows, "B").Value = TxtArray(1)
>> Sheets(MasterSheetName).Cells(nRows, "C").Value = TxtArray(2)
>> '----- Add to Dictionary Object --------------
>> Dict_Data.Add TxtArray(0) & "_" & TxtArray(1), TxtArray(2)
>> '----------------------------------------------
>> Else ' Update Record
>> DataRow = Dict_Data.Item(TxtArray(0) & "_" & TxtArray(1))
>> Sheets(MasterSheetName).Cells(DataRow, "C").Value =
>TxtArray(2)
>> End If
>> End If
>> Loop
>> Close #1 ' Close file.
>> End Function
>> ----------------------------------------------------------
>>
>> ----------------------------------------------------------
>>
>> ----------------------------------------------------------
>>
>>
>> ==============================================================
>>
>>
>> Paul
>> -----------------------------------------
>> "Do all the good you can,
>> By all the means you can,
>> In all the ways you can,
>> In all the places you can,
>> At all the times you can,
>> To all the people you can,
>> As long as ever you can." - John Wesley
>> -----------------------------------------
>>
>> [Non-text portions of this message have been removed]
>>
>
>[Non-text portions of this message have been removed]
>
>
>
>
>
[Non-text portions of this message have been removed]
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1424 / Virus Database: 2437/5129 - Release Date: 07/13/12
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar