Dear All
This is how we hide Subs from the macro list and functions from the Insert Function dropdown :-
Put 'Option Private Module' at the top of each module just after 'Option Explicit' except in the module that contains the Macros/Subs/Functions you want to see..
This does not work for worksheets so forces you to place your routines in modules. This is good.
Other methods :-
You can hide Subs from the macro list by giving them dummy parameters..
Converting Subs to Functions is not good because they appear instead as User Defined Functions in the Insert Function dropdown.
You can make Subs or Functions Private but then they are not visible outside of their own modules.
'Option Private Module' is not a personal preference, it is required in my software house in order to protect our systems.
Regards
Derek +++
>________________________________
> From: David Smart <smartware.consulting@gmail.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Saturday, 14 July 2012, 0:18
>Subject: Re: [ExcelVBA] Re: Need help on a Macro to compare two spreadsheets
>
>
>
>> ... Personally, I'd rather not have it there to add to the list. ...
>
>It's funny, I almost never call macros from the macro list, so that side of
>it doesn't occur to me.
>
>If I want to run a sub stand-alone (and that's not very often, I must say),
>I go to it in the VBE and run it there. Normally, I create a button or
>other link, on the basis that something I wanted to run once, I'll want to
>run again.
>
>I did an awful lot of programming with Pascal and Ada and like the
>capability of by-reference parameters.
>
>These days, I program in C++ and Java more, and miss the simple by-reference
>parameters (although objects are still effectively by-reference, so it's not
>all bad).
>
>I suppose the "structured code" programmer in me has got me used to using
>subroutines as the building blocks with which the logic of a program is
>structured, so I'm used to having them around.
>
>(Note for the object-oriented people ... yes, I'm object-oriented, and also
>still use normal structured code techniques to further break down the
>functionality inside the objects. Structured programming is alive and well,
>and OO can really be thought of as a higher level of structured
>programming.)
>
>Regards, Dave S
>
>----- Original Message -----
>From: "Paul Schreiner" <schreiner_paul@att.net>
>To: <ExcelVBA@yahoogroups.com>
>Sent: Saturday, July 14, 2012 1:09 AM
>Subject: Re: [ExcelVBA] Re: Need help on a Macro to compare two spreadsheets
>
>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
>
>----------------------------------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