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
