Scripting.Dictionary is an Object TYPE, not an object itself.
Kind of like (not not exactly like) saying:
Dim tString as String
tString it the Object that was created, and takes on all of the properties (and
methods) available to all Strings.
In this case:
Set Dict_Data = CreateObject("Scripting.Dictionary")
Does exactly what the line says,
It creates an Object (Dict_Data) of type "Scripting.Dictionary".
In the program, you never "refer" to "Scripting.Dictionary", but instead use
the Dictionary Object that was created:
(i.e. Dict_Data.Count)
===========================
Now, as to your other questions.
Perhaps if I explain variable "scope" you'll understand (and be able to apply it
in future programs!)
Variables have "scope".
There, I said it... boy do I feel better...
Now that it's out in the open, we can talk about it (notice I'm feeling 'snarky'
this morning?)
WHERE you declare a variable determines when/where it can be used.
For instance,
If you declare (Dimension) a variable WITHIN a subroutine or function, it only
exists within that subroutine/function.
(these are sometimes referred to as "local" variables)
If you declare (Dimension) a variable at the TOP of a module, it only exists for
the subroutines/functions WITHIN THAT MODULE.
UNLESS, at the top of a module (not within a subroutine/function), you use the
keyword "Public" or "Global".
THEN the variable is accessible to all subroutines/functions in all modules
WITHIN THAT WORKBOOK
As an example, try this simple set of macros:
'-----------------
Option Explicit
Dim ttl
Sub Chk_Var()
Dim inx, cnt
For inx = 1 To 5
cnt = cnt + 1
ttl = ttl + 1
MsgBox "Chk_Var: " & Chr(13) & "cnt = " & cnt & Chr(13) & " ttl = " &
ttl
Chk_Var2
Next inx
End Sub
'-------------
Function Chk_Var2()
Dim inx, cnt
For inx = 1 To 5
cnt = cnt + 1
ttl = ttl + 1
MsgBox "Chk_Var: " & Chr(13) & "cnt = " & cnt & Chr(13) & " ttl = " &
ttl
Next inx
End Function
'----------------------------------------------------------
You'll notice that, since the "cnt" variable is declared in both the sub and the
function,
then it DOESN'T "carry over" when the function exits.
But since "ttl" is declared OUTSIDE the subs, then it DOES "carry over".
'----------------------------------------------------------
So..
the longer version of the answer is:
Variables declared "locally" within a Subroutine or Function are only
available while the Sub or Function is running.
Variables declared OUTSIDE the function are still defined and have data when the
Sub or Function exits.
But only variables declared as "Global" or "Public" are available to other
modules (within the same workbook).
None of these are available outside the current workbook.
If you're wanting this Dictionary Object to carry over into another workbook,
I think you'll have to define it and reload it in the second workbook.
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 5:19:57 PM
Subject: [ExcelVBA] Re: Need help on a Macro to compare two spreadsheets
The reason for asking about the "Scripting.Dictionary" text value is several.
What if I want to create a Dictionary Data for several workbooks? How do I
distinguish one Dictionary Data of one workbook from another? Does that name
qualify it somehow? Is the name {workbook}.Scripting.Dictionary? Does this
Dictionary only "live" as long as the Macro runs?
--- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@...> wrote:
>
>
> ________________________________
> From: danielrose02 <dan.rose@...>
> To: ExcelVBA@yahoogroups.com
> Sent: Thu, July 12, 2012 3:39:08 PM
> Subject: [ExcelVBA] Re: Need help on a Macro to compare two spreadsheets
>
>
> Thank you. More questions:
>
> 1. The "Master_Data" workbook actually is made up of 3 worksheets:
> 1.a Chart of Summary Pivot Table.
> 1.b Summary Pivot Table
> 1.c LTD Weekly KPI
> 2. I want to perform all of this updating on the last worksheet (LTD Weekly
> KPI). So, for the line of code:
>
> nRows =
>Application.WorksheetFunction.CountA(Sheets(MasterSheetName).Range("A1:A1000000"\))
>)
>
>
> How do I stipulate to use that particular worksheet?
> I anticipated this need. Therefore, I made the sheet name a variable.
> Find the line that says:
> MasterSheetName = "Master_Data"
> and change it to:
> MasterSheetName = "LTD Weekly KPI"
>
> 3. In the line of code:
>
> Set Dict_Data = CreateObject("Scripting.Dictionary")
>
> Is the text "Scripting.Dictionary" a reserved word or phrase?
> Yeeessss... It is the definition of the Dictionary Object..
> Why do you ask?
>
>
> --- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@> wrote:
> >
> > 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@>
> > 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]
>
[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