I found references on the losing scope issue. I think I understand
what I am up agains there. I think unhandled errors the most likely
cause if I hit it.
I will probably only use one module for what I am doing with
appropriate hierarchy of procedures so really public is not needed,
but from what I read can still lose scope on variables just global to
all procedures?
Fuzzy on another point still. If the . on rows is what says that is a
child of sheets, then why is the . on worksheetfunction wrong? How
does the with sheets work for the worksheetfunctions if they are not
designated an children of the with? Is that just implied for
worksheetfunction? Even if so it seems like either way should be ok.
On Wed, Mar 28, 2012 at 8:53 AM, Domain Admin <domainqu...@gmail.com> wrote:
> You should write a book. I agree with what you say where I understand
> it. In fact I am playing with references to learn but for final could
> would probably use fully qualified paths in places like initialization
> areas called once. Also not a fan of global vars usually but to save
> a lot of repitition will use them but certainly must understand the
> ramifications.
>
> Because of the types of operations I will be doing repeatedly on
> reading cell values, calculating, writing cell values I thought cell
> functions in loops better than trying to play with ranges. Could be
> just my limited understanding of the best way to use ranges and
> references together.
>
> I can not store calculations until the end in many cases. Some
> columns can be calculated after all other data present, but some
> require a cell to be calculated, get more data, calculate cell I
> think. I may be wrong about that as I have not seen the full data set
> yet. I would still not be sure about the best way to use ranges if
> what I want to do is loop quickly through a column of cells, but that
> answer may lie is stuff you have already sent me.
>
> The concept of losing state is staggering to me. I have never
> experienced such a thing. A program may crash, but it does not lose
> set values unless they get clobbered by a bug. Are you saying that
> even if the program will start running and go to completion without
> any interaction with it there can still be loss of state?
>
> And finally I do not understand what you mean by this:
> " Also, if you are initializing variables that will be used later multiple
> times, you should initialize them to the greatest extent possible."
>
> On Wed, Mar 28, 2012 at 12:42 AM, Asa Rossoff <a...@lovetour.info> wrote:
>> Hi Howard,
>>
>>> So for cross referencing can I create a reference
>>> Set pointer = sheets(chartvalue) then use pointer.cells(row,col) for
>>> access since what I will be doing is reading cell values from one
>>> sheet, performing functions on the values then setting cell values in
>>> the other sheet. Or is there more to it than that?
>>
>> You are correct. As simple as that.
>>
>>
>> Just to comment on the syntax a bit -
>>
>> A leading "." in an object reference is shorthand to say that the object is
>> a child of the object specified in the With clause. So, to indicate Rows(1)
>> goes with Sheets(ChartData), it needed a leading ".". Conversely, if you
>> don't specify a leading "." the object is not associated with the With
>> clause and is handled as if there were no With clause.
>>
>>> Also like getting rid of worksheetfunction. I went the other way and
>>> tried to get rid of application and that failed.
>> Ah.. that's because you have a leading "." before your Application
>> reference. It is entirely unneeded. If you got rid of Application and
>> still left that unneeded leading ".", then VBA would have interpreted your
>> line of code as Sheets(ChartData).WorkSheetFunction.Match (etc.) which is
>> invalid because WorksheetFunction is not a property of the Sheet class.
>>
>> Your current line is interpreted as Sheets(ChartData).Application.Match
>> which is valid, but only because most objects have an Application property
>> that refers circularly to the Application object.
>>
>> Also, it is not always desirable to use the shortest line of code possible.
>> Especially be careful about using unqualified object references, such as
>> your Rows(1) without the leading ".", unless you are sure you know how it is
>> interpreted under all circumstances. There are quite a few objects that
>> Excel usues complicated logic to determine what to apply them to when you
>> don't specify (and the logic isn't always documented). Similarly, be
>> careful about using assuming you know what the active workbook or worksheet
>> is. The best practice, unless your code is specifically relevant to
>> WHATEVER workbook or worksheet is active, is to specify what the workbook
>> and worksheet.
>>
>> If you remove the Application from Application.WorksheetFunction.Match,
>> that's fine (I would), and many people -- even reknowned experts -- refer to
>> worksheet functions by eliminating WorksheetFunction, as you did:
>> Application.Match, but I prefer not to: (1) it is undocumented. Worksheet
>> functions are not documented as methods of the Application object. Anything
>> that's undocumented is not guarnateed to do what you think it does, and not
>> guaranteed to work in future versions of Excel. (2) I've verified (though
>> not the discoverer) that at least one worksheetfunction method -- RoundDown
>> -- gives incorrect results in some circumstances when referred to as
>> Application.RoundDown (3) Error handling is different when you abbreviate
>> worksheetfunction methods to Application.(function). Specifically, an error
>> is not generated, although an error code is in the return value from the
>> function. (4) between reason number 2 and 3, my theory is that the
>> Application version is the version used from worksheet formulas, and the
>> WorksheetFunction version is the only version intended to be used from VBA,
>> and behaves differently as required by an environment that has different
>> error handling and many more datatypes than a worksheet does.
>>
>> So... my reccommended revision to the key lines of code is:
>> fv = WorkSheetFunction.Match("Stopval", .Rows(1), 0)
>> lv = WorkSheetFunction.Match("ReverseDate", .Rows(1), 0)
>>
>> Most significantly, notice the absence of a leading ".".
>>
>> . . .
>>
>> It looks like you were declaring your variables outside the procedure so
>> that they would be available to multiple procedures during the time that
>> your workbook is open? If I am correct in that guess, you should be aware
>> that various events outside of your control can cause VBA to lose "state"
>> and your variables could be reset at anytime. So, doing what I think you're
>> doing can complicate things, because if you do that, you should check that
>> your variables are initialized at the start of entry procedures (such as
>> event and key-assigned macro procedures) that might refer to them. Perhaps
>> by calling the initialization routine at the beginning of those procedures,
>> and in the initialization routine itself, check perhaps that just one value
>> has been initialized, and if so, skip the rest of the routine, assuming that
>> they all have... but in most cases this is unneccessarily complex. There
>> are other methods to avoid recalculation, such as waiting to calculate
>> values until needed and passing the calculated values as parameters to
>> routines that require them, although that doesn't help a lot in rapid-fire
>> events. But for that case, don't worry unless you find that performing your
>> calculation is time consuming enough to cause a performance problem. If it
>> is, you can consider declaring those variables locally in that procedure
>> instead of globally, using the Static keyword so that they retain values
>> between calls, and reinitializing them if they are not initialized yet.
>> Global variables are usually used sparingly in VBA.
>>
>> Also, if you are initializing variables that will be used later multiple
>> times, you should initialize them to the greatest extent possible.
>>
>> If your purpose here is to get references to specific cells to access their
>> values or properties, you should ordinarily use a Range object and create a
>> reference to the range instead of just saving the index.
>>
>> I'd recommend a specific code design, but without knowing the overall intent
>> of your code, I can't give good advice.
>>
>> Asa
>>
>> -----Original Message-----
>> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
>> On Behalf Of Domain Admin
>> Sent: Tuesday, March 27, 2012 4:54 PM
>> To: excel-macros@googlegroups.com
>> Subject: Re: $$Excel-Macros$$ Difficulty referencing non active sheet but
>> this must be possible
>>
>> Thanks. Believe me I am a firm believer in KISS. I did not realize
>> the . made that distinction though.
>> Also like getting rid of worksheetfunction. I went the other way and
>> tried to get rid of application and that failed. I thought it was the
>> redundant one not worksheetfunction.
>>
>> So for cross referencing can I create a reference
>> Set pointer = sheets(chartvalue) then use pointer.cells(row,col) for
>> access since what I will be doing is reading cell values from one
>> sheet, performing functions on the values then setting cell values in
>> the other sheet. Or is there more to it than that?
>>
>> On Tue, Mar 27, 2012 at 4:42 PM, dguillett1 <dguille...@gmail.com> wrote:
>>>
>>> Sub InitRefs()
>>> dim fv As long
>>> dim lv As long
>>> With Sheets(“ChartData”)
>>> fv= .Application.Match("Stopval", .Rows(1), 0) ‘notice the dot (.)
>> before
>>> rows
>>> End With
>>>
>>> With Sheets(“Results”)
>>> lv = .Application..Match("ReverseDate", .Rows(1), 0) ‘dot NOT needed on
>>> ACTIVE sheet but needed here.
>>> End With
>>> End Sub
>>>
>>> Try to adhere to the KISS principle
>>>
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguille...@gmail.com
>>>
>>> From: tangledweb
>>> Sent: Tuesday, March 27, 2012 5:50 PM
>>> To: excel-macros@googlegroups.com
>>> Subject: $$Excel-Macros$$ Difficulty referencing non active sheet but this
>>> must be possible
>>>
>>> I have one sheet of raw data with several columns and thousands of rows.
>> I
>>> need to process this and I wanted to put the results on a separate sheet
>>> with several columns and dozens of rows when done. I saw some example
>> code
>>> where it seemed you could do this as long as you qualified the sheet path
>>> but doing what I think is the same thing is not working. Also I would
>> like
>>> to minimize the full paths I have to use or minimize activations when
>> going
>>> back and forth from one sheet to the other to get data, process, then
>> store
>>> data which will have to be done thousands and thousands of times. So part
>>> of the question is can you set path references that can be used to
>> minimize
>>> the typing volume and readability?
>>>
>>> But first I need to know why I can not get this most basic initialization
>> to
>>> work If I interactively select sheet ChartData (it is not an excel chart
>>> just a name) then the first initialization works but not the one for
>> Results
>>> and visa versa. I have stripped out unnecessary columns for simplicity.
>> I
>>> tried both with and without activating sheet Results but get the error
>>> "Unable to get results of the match property class" for the ReverseDate
>>> assignment line no matter what I do. Also tried the names with and
>> without
>>> quotes to no difference.
>>> If in the interface I select sheet Results then the error occurs on the
>>> Stopval assignment line.
>>>
>>>
>>> ' Column reference objects
>>> Public Stopval As Double
>>> '
>>> Public ReverseDate As Double
>>>
>>> ' Sheet names
>>> Const ChartData As String = "ChartData"
>>> Const Results As String = "Results"
>>>
>>>
>>> Sub InitRefs()
>>> '
>>> ' Macro which sets the data column names and misc values for the sheets
>>> '
>>> With Sheets(ChartData)
>>> Stopval = .Application.WorksheetFunction.Match("Stopval",
>>> Rows("1:1"), 0)
>>> End With
>>>
>>> ' Sheets("Results").Activate
>>> ' With Sheets(Results)
>>> ' ReverseDate = .Application.WorksheetFunction.Match("ReverseDate",
>>> Rows("1:1"), 0)
>>> ' End With
>>> End Sub
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will
>>> not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>>> owners and members are not responsible for any loss.
>>>
>>>
>> ----------------------------------------------------------------------------
>> --------------------------
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will
>>> not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>>> owners and members are not responsible for any loss.
>>>
>>>
>> ----------------------------------------------------------------------------
>> --------------------------
>>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
>> not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>> ----------------------------------------------------------------------------
>> --------------------------
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
>> not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
