Save the current invoice's data fields in the "InvoiceSummaries" sheet Set currentInvoiceTotal to string value of cell 20 of row currentTotalRowNum Set currentTotalRowNum to first row index of foundInvoiceTotal Look for this invoice's "INVOICE TOTALS:" line Set currentCustName to (text item 2 of currentCustName) as text Set currentCustName to value of cell 12 of row currentRowNum
Set currentCustNum to (text item 1 of currentCustNum) as text
Set AppleScript's text item delimiters to " - " Set currentCustNum to value of cell 12 of row currentRowNum Set currentInvoiceNum to value of cell 2 of row currentRowNum Set AppleScript's text item delimiters to tid Set currentInvoiceDate to (text item 2 of currentInvoiceDate) as text Set AppleScript's text item delimiters to " " Set tid to AppleScript's text item delimiters Set currentInvoiceDate to value of foundInvoiceDate Set numInvoicesProcessed to numInvoicesProcessed + 1 Set firstFoundInvoiceDate to foundInvoiceDate Remember the location of the first invoice date found Remember the location of the date of the first invoice processed Set currentRowNum to first row index of foundInvoiceDate Set foundInvoiceTotal to (find (range "B:B" of worksheet "Sheet1") what "INVOICE TOTALS:") Find the first occurrence of the row containing the invoice total Set foundInvoiceDate to (find (range "G:G" of worksheet "Sheet1") what "Date:") Find the first occurrence of the row containing the invoice number, date, customer number and customer name Keep track of how many invoices have been processed Start the timer to time the execution of this script
#Excel vba windows vs mac code#
Any suggestions on improving the code would be much appreciated: It provides the correct output, but it's just slow. It's designed to parse a file of invoice data, skipping the line item rows and constructing a new sheet containing just the pertinent invoice data and total for each invoice. At this point, I don't want to get into any particulars of what methods I called in the script, but I'm looking for general feedback regarding the relative speeds of each technology, and what I might be able to do to speed up the AppleScripts. I tried running the AppleScript with the Excel file "hidden" (I clicked the yellow button on the window), and I also tried saving the script as an "Application", which I thought would speed things up, but didn't. I don't want to start any "religious" wars, but I'm curious as to whether AppleScript code is really that much slower than VBA code.
#Excel vba windows vs mac mac#
The Mac that I ran the AppleScript on is a 867 Mhz PowerBook with 1.125 Gb of RAM running Excel 2008 under OS X 10.5.6, and the Windows machine I used is a 350 Mhz machine with 384 Mb of RAM running Excel 2002. I then wrote essentially the same code in VBA using Excel on a Windows machine, and this code ran in about 1 hour. I first attempted this in AppleScript, but it took a very long time to run for each file, maybe 3-5 hours. In VBA7 the ObjPtr, StrPtr, and VarPtr functions return LongPtr whereas in VBA6 they return Long.Ĭonditional compilation must be used when declaring LongLong or LongPtr to make code compatible with VBA6 and VBA7.I recently had to process a few large (>30,000 rows) Excel files, parsing through them using the Find command, extracting selected pieces of data, and constructing a new worksheet with the extracted data. VBA7 also added the PtrSafe keyword for DLL declarations which must be used on 64-bit platforms. VBA7 added two new data types, LongLong and LongPtr, with accompanying conversion functions, DefType statements, a type declaration character for the LongLong type, and the vbVarType.vbLongLong constant.
#Excel vba windows vs mac update#
VBA7 was essentially an update to make VBA more compatible with 64-bit operating systems. Be sure to explicitly compare conditional compiler constants to 1 or 0 to return True or False.Ĭonditional compilation is used to write code that is compatible on different operating systems and different versions of VBA. When using Logical/Bitwise operators be aware that numbers are compared using bitwise comparison and boolean expressions are compared using logical comparison. It is important to note that conditional compiler constants do not evaluate to True and False. Available constants on 16-bit platforms ConstantĪvailable constants on 32-bit and 64-bit platforms Constant Compiler Constants are set to 1 or 0 depending on the platform the code is running on. Compiler Constants are used to test compatibility with different operating systems and different versions of VBA.
0 kommentar(er)
