Kamis, 12 Juli 2012

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]

__._,_.___
Recent Activity:
----------------------------------
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