| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - global VB macros
Wondering where are global (non-local module) VB macros kept in Excel in
Microsoft Office once you have written them? Are they compressed within Excel? Are separate files (and extentions) created for global macros? Email response appreciated...Thx, |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel - global VB macros
"cs" wrote: Wondering where are global (non-local module) VB macros kept in Excel in Microsoft Office once you have written them? Are they compressed within Excel? Are separate files (and extentions) created for global macros? Email response appreciated...Thx, Could someone HELP TRANSLATE this simple psuedo code into MACRO please?! Could someone HELP TRANSLATE this simple psuedo code into MACRO please?! HTML Code:
for EACH row in WORK
{
for each row in DATA
{
if ((WORK.row.zip == DATA.row.zip) && (WORK.row.name == DATA.row.name))
Then
Highlight current WORK.row to YELLOW, break out of current for loop
ELSE
next DATA row++
}
next WORK row++
}
[size="3"](see below for explaination) I have a Excel workbook called FIRSTAM8.xls Inside I have two seperate worksheets: 1)'DATA' & 2)'WORK' There are 31886 rows in sheet 'WORK' & 5741 rows in sheet 'DATA' Column 'C' in 'WORK' contains the customer name, Column 'B' contains the zip code. In the 'DATA' sheet Column 'H' contains the customer name, & Column 'S' the zip code. I want to compare (individually, one-by-one) the zip && name Columns ('B' & 'C') of each row in sheet 'WORK' to/with/against the respective zip && name Columns ('S' & 'H') of EACH AND EVERY row in sheet 'DATA'. For example: if BOTH the zip &&and&& name columns of row1 of sheet 'WORK' matches with ANY (any of the 5741) rows of sheet 'DATA' then row1 IS a match/hit, and the entire row1 of sheet 'WORK' is highlighted yellow to indicate the match status. If row1 of 'WORK' matches to multiple rows of 'DATA' that is okay, it is still highlighted. However if row1 of 'WORK' does not match (by the criteria of zip && name) to ANY of the 5741 rows in "DATA" then it is not highlighted and the next row (row2) is processed. Note: When comparing names, I really mean comparing the first character of the customer name fields. I do this to avoid false negatives and so not to miss a potential match. This will entail using the substring manipulation functions.(?LEFT) -------------------- HTML Code:
for EACH row in WORK
{
for each row in DATA
{
if ((WORK.row.zip == DATA.row.zip) && (WORK.row.name == DATA.row.name))
Then
Highlight current WORK.row to YELLOW, break out of current for loop
ELSE
next DATA row++
}
next WORK row++
}
*OR* HTML Code:
for each row in WORK
{
for each row in DATA
{
if ((WORK.row.column(B) == DATA.row.column(S)) &&
(WORK.row.firstcharof(column(C) == DATA.row.firstcharof(column(H)))
Then
Highlight.interior.current.WORK.row = YELLOW, break out of current
for-loop
ELSE
next DATA row++
}
next WORK row++
}
CAN SOMEONE TRANSLATE THE ABOVE INTO REAL EXCEL CODE FOR ME?? Here is a link to the ENTIRE post I made (for clarity and reference): http://www.ozgrid.com/forum/showthread.php?t=49941 Here is an abridged IMAGE of the my Excel Workbook: Attachment 4703 Here is the original file I am working on (LARGE): FIRSTAM8.xls http://www4.sendthisfile.com/d.jsp?t...Oeb2puKBql1LmB Thanks, Bo Could someone HELP TRANSLATE this simple psuedo code into MACRO please?! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel - global VB macros
I assume that you are talking about when you record a macro you have the
option of making it available to all spreadsheets, not just the one it is recorded in, by putting it in your Personal Macro Workbook. This is essentailly just a hidden spreadsheet that opens any time Excel is opened. It sits in your xlStart directory C:\Documents and Settings\YourName\Application Data\Microsoft\Excel\XLSTART or some similar path. And is called Personal.xls -- HTH... Jim Thomlinson "cs" wrote: Wondering where are global (non-local module) VB macros kept in Excel in Microsoft Office once you have written them? Are they compressed within Excel? Are separate files (and extentions) created for global macros? Email response appreciated...Thx, |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel - global VB macros
Just to add,
All macros are stored in workbooks - unlike word, there is not normal.dot or template that is used to store common macros. As Jim has said, macros from one workbook can be run on any workbook. However, to call a macro in one workbook from another workbook, the calling workbook would either have to have a reference set to the other workbook or would have to use application run. -- Regards, Tom Ogilvy "cs" wrote: Wondering where are global (non-local module) VB macros kept in Excel in Microsoft Office once you have written them? Are they compressed within Excel? Are separate files (and extentions) created for global macros? Email response appreciated...Thx, |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel - global VB macros
Jim, Tom,
Good / helpful answers from each of you - thanks. --chris "Tom Ogilvy" wrote: Just to add, All macros are stored in workbooks - unlike word, there is not normal.dot or template that is used to store common macros. As Jim has said, macros from one workbook can be run on any workbook. However, to call a macro in one workbook from another workbook, the calling workbook would either have to have a reference set to the other workbook or would have to use application run. -- Regards, Tom Ogilvy "cs" wrote: Wondering where are global (non-local module) VB macros kept in Excel in Microsoft Office once you have written them? Are they compressed within Excel? Are separate files (and extentions) created for global macros? Email response appreciated...Thx, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Global macros | Excel Discussion (Misc queries) | |||
| global Macros | Excel Discussion (Misc queries) | |||
| storing global macros | Excel Programming | |||
| How to put excel macros in a 'global' place. | Excel Programming | |||
| Creating Global excel macros | Excel Programming | |||
Hybrid Mode
