池州會計繼續(xù)教育網(wǎng)：Excel在XLL方面所做的更新

高頓網(wǎng)校友情提示，*7池州會計繼續(xù)教育網(wǎng)上總結(jié)信息Excel在XLL方面所做的更新等內(nèi)容公布如下：　　Quick detour #3: Updates we have made to XLLs

　　In the past few months, I have written a couple of articles (big grid, multi-threaded calculation) that prompted comments and questions along the lines of “what about XLLs?” Since the email questions have actually been picking up in frequency (I guess a lot of readers are also XLL authors), I thought I would write a quick article that outlines the changes we are making in this area to support new functionality.

　　在過去幾個月里，圍繞“XLL將會怎樣”的主題我有寫過兩篇文章（《更大的表格》、《多線程計算》），給出了一些相關(guān)的注解及問題。由于我收到的郵件中關(guān)于這方面的提問至今仍有相當(dāng)?shù)念l度（我估計這其中的許多讀者也是XLL加載宏的作者），我想我還是得再寫上一篇簡短的文章，來給大家描繪一下我們在新函數(shù)的支持方面所作的變更。

　　Stepping back for a minute, let me briefly explain XLLs. An XLL is a DLL that is written so that Excel can open it directly. XLLs can be used for a number of things, but (in my experience at least) the most common use of XLLs is creating user-defined worksheet functions (UDFs) to supplement Excel’s intrinsic functions. Developers who write XLLs do so for a number of reasons, the paramount being performance.

　　先耽擱您幾分鐘時間，讓我來簡要地介紹一下XLL為何物。

　　XLL文件實際上就是一個能夠讓Excel直接打開的DLL(動態(tài)鏈接庫)文件。雖說XLL文件可以用來做很多事情，不過，至少在我的經(jīng)驗中是這樣，其最通常的應(yīng)用是創(chuàng)建用戶自定義工作表函數(shù)，以擴充Excel的內(nèi)置函數(shù)。（譯者注：就這一點而言，用VBA也可以做，那為什么還要編譯成XLL文件呢？）開發(fā)人員這樣做的原因有很多，其中一個最重要的原因就是為了提高執(zhí)行效率。

　　We have made three changes to XLLs in Excel 12, primarily to give developers access to new functionality. With Excel 12, XLL authors will have support for

　　·The bigger grid

　　·More function arguments

　　·Multi-threaded calculation

　　在Excel 12中，我們對XLL的相關(guān)應(yīng)用作了三個方面的變更，主要目的是為了方便開發(fā)人員訪問新的函數(shù)。透過Excel 12，XLL的開發(fā)者們將獲得下列支持：

　　·更大的表格

　　·更多的函數(shù)參數(shù)

　　·多線程計算

　　When building an XLL today, developers can use a special Excel data type called an “XLOPER”. An XLOPER is essentially a structure that allows developers to pass data types like references, arrays, and error values to and from Excel (technically, XLOPERs can contain 12 possible data types – if you are curious, here is some documentation on MSDN). Currently, several of the data types (i.e. the one that can be used to communicate a reference to and from Excel) are not big enough to support the dimensions of the bigger grid size in Excel 12.

　　今天，當(dāng)我們在構(gòu)建XLL文件的時候，開發(fā)人員可以使用一種稱之為“XLOPER”的專用數(shù)據(jù)類型（譯者注：XLOPER是OPER 結(jié)構(gòu)的增強版本。在Microsoft Excel 4.0及更高版本中，可以使用這種數(shù)據(jù)類型來編寫調(diào)用Microsoft Excel函數(shù)的DLL和代碼資源，使用XLOPER 結(jié)構(gòu)，DLL函數(shù)除了可以傳遞數(shù)據(jù)外，還可以傳遞對工作表的引用并實現(xiàn)流控制）。這是一種基礎(chǔ)數(shù)據(jù)結(jié)構(gòu)，它允許開發(fā)人員在應(yīng)用程序和Excel之間傳遞或返回諸如引用、數(shù)組及錯誤信息等數(shù)據(jù)（從技術(shù)上來講，XLOPER能夠包含12種可能的數(shù)據(jù)類型，如果你還想知道更多的話，請點擊這里參閱MSDN上的相關(guān)資訊）。目前，這其中有一部分?jǐn)?shù)據(jù)類型（比如其中一個用來與工作表引用區(qū)域交互通信的數(shù)據(jù)類型）尚不夠大，不足以支持Excel 12增大的表格維度（譯者注：關(guān)于Excel 12工作表的大小及其它方面限制的變更，請參閱Kevin翻譯的《大片上映...》）。

　　To address this, Excel 12 will implement a new XLOPER – which will be called something like XLOPER12 – which will have a larger reference (xltypeRef) data type and a larger array (xltypeMulti) data type which will support the entire big grid. In addition, XLOPER12 will support Unicode data and therefore strings larger than 255 characters. One happy by-product of the support for more characters is that XLLs written using XLOPER12 will be able to support 255 arguments, the same limit supported by Excel 12.

　　有鑒于此，Excel 12將執(zhí)行一套全新的XLOPER方案 – 我們暫且稱之為XLOPER12。新方案將擁有一個更大的引用(xltypeRef)數(shù)據(jù)類型及一個更大的數(shù)組(xltypeMulti) 數(shù)據(jù)類型，以支持整個增大的工作表。另外，XLOPER12也將支持Unicode數(shù)據(jù)并因此能夠支持長度超過255個字符的字符串?dāng)?shù)據(jù)，這項改進還帶來另一個讓人高興的副產(chǎn)品，那就是使用XLOPER12開發(fā)的XLL能夠支持多達255個參數(shù)，這與Excel 12能夠支持的上限是一致的。
 

　　In order to use XLOPER12, developers will need to use a new C API function – Excel12 – instead of the existing Excel4 function. Excel12 is essentially the same function updated to handle the new XLOPER12, so if developers want to take advantage of any of the changes described above, they will need to update their code to call the new API. Note that existing code will continue to run as it always has, so developers will not face no backwards compatibility problems.

　　要使用XLOPER12的數(shù)據(jù)結(jié)構(gòu)，開發(fā)人員必須使用一個新的C API函數(shù)－Excel12－來代替現(xiàn)有的Excel4。Excel12函數(shù)本質(zhì)上講與Excel4是一樣的，升級的目的是為了操作XLOPER12，因此，開發(fā)人員如果想要獲得上述任何變更所帶來的優(yōu)勢，就必須更新其代碼以調(diào)用新的API函數(shù)。當(dāng)然，現(xiàn)有代碼能夠一如繼往地運行，開發(fā)人員不需要面對向后兼容的問題。

　　Updating XLL’s to take advantage of multi-threaded calculation

　　As discussed in a previous post, Excel 12 will support multi-threaded calculation. Since performance is very important for the bulk of XLL authors, we wanted to give developers who write UDFs using XLLs a way to allow their XLLs to participate in multi-threaded calculation (meaning Excel would calc multiple UDFs at once thereby improving calculation times).

　　就像上一個帖子中討論的那樣，Excel12將會支持多線程計算。由于對大多數(shù)XLL開發(fā)者而言，產(chǎn)品性能是至關(guān)重要的，因此，我們應(yīng)該給那些使用XLL編寫用戶自定義函數(shù)（UDFs）的開發(fā)人員們一個參與多線程計算的途徑，這就意味著Excel將能夠同時計算多個用戶自定義函數(shù)（UDFs），亦即提高了計算次數(shù)。

　　To do so, XLL authors need to do two things. First, (the harder part) they need to make sure their XLL UDF (the code they wrote) is threadsafe (i.e. it must not make any non-thread-safe callbacks into Excel). Second, they need to make a minor update to their XLL to tell Excel 12 it is threadsafe. This is done in pretty much the same way that an XLL author would tell Excel that their function is volatile – by adding a special character to the “type text” entry in an XLL’s function table (the type text entry defines the data types for the functions’ return value and arguments).

　　要達成這一目標(biāo)，XLL的開發(fā)人員需要做兩件事情：*9件事，也是最困難的一件，開發(fā)人員必須確保他們的代碼是線程安全的，也就是說，代碼不得造成任何導(dǎo)致Excel發(fā)生非線程安全的回調(diào)行為。第二件事，開發(fā)人員必須對他們的XLL代碼作一些小的更新，以便告訴Excel 12這些代碼是線程安全的。這一點，與XLL的開發(fā)人員通過為XLL函數(shù)表中的 “類型說明”入口點（用于定義函數(shù)返回值及參數(shù)的數(shù)據(jù)類型）添加某些特殊的字符，以此告訴Excel程序這些是易失性函數(shù)的作法，是非常相似的。

　　Specifically, to indicate that an XLL function is safe for multi-threaded calculation, an XLL author needs to add a "$" to the type text entry in an XLL’s function table ... so the type text entry for a UDF that had one argument would look like this “ RR$”. If this character is present, Excel will allow the UDF to participate in muti-threaded calc, just like Excel’s intrinsic functions; if the flag is absent, the reverse is true – all calls to that UDF will be run on a single thread. Note, the Excel4 API will also respond appropriately to the “$” character, so if an XLL author wants to take advantage of multi-threading, but doesn't care about the big grid, they do not have to use the Excel12 API.

　　更確切地說，要明示一個XLL函數(shù)對于多線程計算是安全的話，XLL的開發(fā)人員必須為XLL函數(shù)表中的類型說明入口點添加一個“$”符號...如此，帶有一個參數(shù)的用戶自定義函數(shù)的類型說明入口點看起來就象這樣“ RR$”。如果這個字符有提交，Excel將允許用戶自定義函數(shù)參與多線程計算，就象是Excel的內(nèi)置函數(shù)一樣；如果這個標(biāo)志缺失的話，那么情況就相反－所有對用戶自定義函數(shù)的調(diào)用都將只能進行單線程計算。值得關(guān)注的是，Excel4 API函數(shù)同樣會對這個“$”符號作出適當(dāng)?shù)捻憫?yīng),因此,XLL的開發(fā)人員如果不想顧及Excel 12增大的表格，而又想利用多線程計算的話，他們不一定非得使用Excel12 API。

 　　　　

　　掃一掃微信，學(xué)習(xí)實務(wù)技巧

　　高頓網(wǎng)校特別提醒：已經(jīng)報名2014年財會考試的考生可按照復(fù)習(xí)計劃有效進行！另外，高頓網(wǎng)校2014年財會考試高清課程已經(jīng)開通，通過針對性地講解、訓(xùn)練、答疑、?？?，對學(xué)習(xí)過程進行全程跟蹤、分析、指導(dǎo)，可以幫助考生全面提升備考效果。