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

高頓網(wǎng)校友情提示，*7池州會(huì)計(jì)繼續(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.

　　在過(guò)去幾個(gè)月里，圍繞“XLL將會(huì)怎樣”的主題我有寫過(guò)兩篇文章（《更大的表格》、《多線程計(jì)算》），給出了一些相關(guān)的注解及問(wèn)題。由于我收到的郵件中關(guān)于這方面的提問(wèn)至今仍有相當(dāng)?shù)念l度（我估計(jì)這其中的許多讀者也是XLL加載宏的作者），我想我還是得再寫上一篇簡(jiǎn)短的文章，來(lái)給大家描繪一下我們?cè)谛潞瘮?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.

　　先耽擱您幾分鐘時(shí)間，讓我來(lái)簡(jiǎn)要地介紹一下XLL為何物。

　　XLL文件實(shí)際上就是一個(gè)能夠讓Excel直接打開(kāi)的DLL(動(dòng)態(tài)鏈接庫(kù))文件。雖說(shuō)XLL文件可以用來(lái)做很多事情，不過(guò)，至少在我的經(jīng)驗(yàn)中是這樣，其最通常的應(yīng)用是創(chuàng)建用戶自定義工作表函數(shù)，以擴(kuò)充Excel的內(nèi)置函數(shù)。（譯者注：就這一點(diǎn)而言，用VBA也可以做，那為什么還要編譯成XLL文件呢？）開(kāi)發(fā)人員這樣做的原因有很多，其中一個(gè)最重要的原因就是為了提高執(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中，我們對(duì)XLL的相關(guān)應(yīng)用作了三個(gè)方面的變更，主要目的是為了方便開(kāi)發(fā)人員訪問(wèn)新的函數(shù)。透過(guò)Excel 12，XLL的開(kāi)發(fā)者們將獲得下列支持：

　　·更大的表格

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

　　·多線程計(jì)算

　　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)我們?cè)跇?gòu)建XLL文件的時(shí)候，開(kāi)發(fā)人員可以使用一種稱之為“XLOPER”的專用數(shù)據(jù)類型（譯者注：XLOPER是OPER 結(jié)構(gòu)的增強(qiáng)版本。在Microsoft Excel 4.0及更高版本中，可以使用這種數(shù)據(jù)類型來(lái)編寫調(diào)用Microsoft Excel函數(shù)的DLL和代碼資源，使用XLOPER 結(jié)構(gòu)，DLL函數(shù)除了可以傳遞數(shù)據(jù)外，還可以傳遞對(duì)工作表的引用并實(shí)現(xiàn)流控制）。這是一種基礎(chǔ)數(shù)據(jù)結(jié)構(gòu)，它允許開(kāi)發(fā)人員在應(yīng)用程序和Excel之間傳遞或返回諸如引用、數(shù)組及錯(cuò)誤信息等數(shù)據(jù)（從技術(shù)上來(lái)講，XLOPER能夠包含12種可能的數(shù)據(jù)類型，如果你還想知道更多的話，請(qǐng)點(diǎn)擊這里參閱MSDN上的相關(guān)資訊）。目前，這其中有一部分?jǐn)?shù)據(jù)類型（比如其中一個(gè)用來(lái)與工作表引用區(qū)域交互通信的數(shù)據(jù)類型）尚不夠大，不足以支持Excel 12增大的表格維度（譯者注：關(guān)于Excel 12工作表的大小及其它方面限制的變更，請(qǐng)參閱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。新方案將擁有一個(gè)更大的引用(xltypeRef)數(shù)據(jù)類型及一個(gè)更大的數(shù)組(xltypeMulti) 數(shù)據(jù)類型，以支持整個(gè)增大的工作表。另外，XLOPER12也將支持Unicode數(shù)據(jù)并因此能夠支持長(zhǎng)度超過(guò)255個(gè)字符的字符串?dāng)?shù)據(jù)，這項(xiàng)改進(jìn)還帶來(lái)另一個(gè)讓人高興的副產(chǎn)品，那就是使用XLOPER12開(kāi)發(fā)的XLL能夠支持多達(dá)255個(gè)參數(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)，開(kāi)發(fā)人員必須使用一個(gè)新的C API函數(shù)－Excel12－來(lái)代替現(xiàn)有的Excel4。Excel12函數(shù)本質(zhì)上講與Excel4是一樣的，升級(jí)的目的是為了操作XLOPER12，因此，開(kāi)發(fā)人員如果想要獲得上述任何變更所帶來(lái)的優(yōu)勢(shì)，就必須更新其代碼以調(diào)用新的API函數(shù)。當(dāng)然，現(xiàn)有代碼能夠一如繼往地運(yùn)行，開(kāi)發(fā)人員不需要面對(duì)向后兼容的問(wèn)題。

　　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).

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

　　要達(dá)成這一目標(biāo)，XLL的開(kāi)發(fā)人員需要做兩件事情：*9件事，也是最困難的一件，開(kāi)發(fā)人員必須確保他們的代碼是線程安全的，也就是說(shuō)，代碼不得造成任何導(dǎo)致Excel發(fā)生非線程安全的回調(diào)行為。第二件事，開(kāi)發(fā)人員必須對(duì)他們的XLL代碼作一些小的更新，以便告訴Excel 12這些代碼是線程安全的。這一點(diǎn)，與XLL的開(kāi)發(fā)人員通過(guò)為XLL函數(shù)表中的 “類型說(shuō)明”入口點(diǎn)（用于定義函數(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.

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

 　　　　

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

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