VBA编程学习与实践 6天前以下文章来源于Excel星球 ，作者EH看见星光  Script Lab是嵌入在Office中的Web应用程序，它面向Web，可以创建和编辑包含 JavaScript，HTML，CSS 等的代码段。它可以通过构建Office 加载项，嵌入对话框，地图、图表等交互式可视化的形式，实现数据交互处理与展示。 它最大的优势之一是可以跨平台使用，在Web、Windows、Mac和iPad上均可使用；而且无需安装任何其他软件或配置环境，只要拥有Office（2013版及以上）即可。因此一直有朋友认为Script Lab是VBA的超级替代者之一。 在Excel中，Script Lab需要在应用商店加载后才能使用。 首先打开一个Excel工作簿，在【插入】选项卡下点击【获取加载项】打开【Office加载项】对话框，并切换到【应用商店】。 作为官家的孩子，正常而言，Script Lab会出现在首页醒目的位置，选中它，点击添加。  完事后，系统就多出一个名为Script Lab的选项卡，稀稀疏疏的包含了几个命令按钮。 点击上图所示最左侧的【代码】命令，工作表右侧会出现一个代码窗格，我们可以将这个窗格拖动到屏幕中央，成为一个独立的代码窗口。然后点击Script，就可以在代码框中编写JS代码。 代码编写完成后，点击上图所示第1个命令按钮，可以给代码命名并保存；点击Run命令可以运行代码，另外的两个键，Delete键是删除，Share键是分享。 什么是JS以及JS语法和VBA的异同，我们在前面那篇推文[当WPS开始像支持VBA一样支持JS语言时，微软又该何去何从]中已经讲过了，像我这么懒的人，这里就不再重复说明。 主要给大家讲解一下Office.js独特的语法结构和异步运行机制，这和WPS.JS不同，和VBA也完全不同。深呼吸，牵牵手，跟我走。 ❶ 基础代码结构 先举个简单代码的例子。 代码看不全可以左右拖动...▼ Excel.run(function(context){ var sheet = context.workbook.worksheets.add("看见星光");//新建一张工作表 var range=sheet.getRange("a1:b2")//取a1:b1区域 range.values=[['姓名','公众号'], ['看见星光','Excel星球']]; sheet.activate();//选中工作表 return context.sync();//发送命令队列})以上代码运行后可以创建一张名称为"看见星光"的工作表，并在A1:B2区域输入以下数据： 如果扣除第1行和最后两行代码，代码整体看起来和VBA并无太大差异。 第1行代码，Excel.run()是一个函数，它接受一个函数作为参数。这个内部函数被称为批处理函数(batch function)。批处理函数的参数是一个请求上下文，说明与系统通信交流的方式，你可以先把它简单理解为当前Excel。 批处理函数内部是各种语句，设置或者调用代理对象（比如Excel）的各种方法和属性，但和VBA所不同的是，这里的相关语句并不会被立刻执行，而是组成一个命令队列。 context.sync()也是一个函数，它将命令队列发送给系统，并按语句顺序依次执行。 因此一个最简单的代码结构如下： Excel.run(function(context){ ...//你的命令队列 return context.sync();//发送命令队列})而作为一个完整的代码，通常我们会处理错误信息。Excel.run返回的是一个结果对象，我们可以通过catch方法获取结果信息。 比如以上代码，增加catch方法后，如果当前工作簿已存在名为"看见星光"的工作表，会返回相关错误信息；如果不增加，则不会显示错误信息。 Excel.run(function(context){ var sheet = context.workbook.worksheets.add("看见星光");//新建一张工作表 var range=sheet.getRange("a1:b2")//取a1:b1区域 range.values=[['姓名','公众号'], ['看见星光','Excel星球']]; sheet.activate();//选中工作表 return context.sync();//发送命令队列})//catch获取结果信息.catch(function (error) { console.log(error); if (error instanceof OfficeExtension.Error) { console.log(error.debugInfo); }});…… 虽然说Office.js是命令队列的性质，但如果我们只是设置Excel的属性或者调用Excel的方法，代码看起来是正常的，完全符合VBA的线性思维逻辑。 不过…… 如果我们需要从Excel读回数据，事情就变了。 举个例子。 将当前工作表A2单元格的值，赋值给B2单元格。 使用VBA代码解决这个问题很简单，一句代码即可： Range("b2").Value = Range("a2").Value但Office.js则是麻烦她妈带麻烦去找麻烦他爷爷还有麻烦他奶奶麻烦他二舅麻烦他三姨真是麻烦太多了。 示例代码如下： Excel.run(async function(context){ var sheet = context.workbook.worksheets.getActiveWorksheet();//获取当前工作表对象 var a_range=sheet.getRange("a2"); a_range.load("values");//加载a2的值属性 await context.sync();//发送请求 sheet.getRange("b2").values=a_range.values; return context.sync();}).catch(function (error) { console.log(error); if (error instanceof OfficeExtension.Error) { console.log(error.debugInfo); }});第2行代码获取当前工作表对象。第3行代码获取当前工作表的A2单元格对象。 第4行代码使用load语句加载A2单元格对象的指定属性，本例为values，也就是值。 第5行代码发送命令队列，加载A2单元格的值数据。 await context.sync() 第6行代码将当前工作表的B2单元格的内容设置为A2单元格的值。 …… 为啥要这么绕呢？像VBA那么直来直去不好吗？饱暖思淫欲，幸福的日期已经过不下去了吗？？ 问题在于，在Office.JS的运行机制里，语句组成了命令队列，load语句也不例外，它像其它语句一样老老实实排队。但是—— 在context.sync()发送命令请求后，系统并不会按照排队顺序执行它，而是在执行完设置属性或调用方法的语句后，才执行load语句——这就乱套了不是？ 排队排了个寂寞，资本主义的产物果然没有社会主义核心价值观的公平正义平等o(*￣︶￣*)o 为了解决这个问题，我们可以将第1行的批处理函数，增加关键字async。 Excel.run(async function(context) 在第5行使用await context.sync();语句发送一次命令请求，系统执行命令队列，并返回代理对象，此时的a_range对象就拥有了values属性。 然后我们才能正常读取该属性，并使用它。 …… 整个Office.js的基本语句结构和运算机制，差不多就是这样的。很显然，对于VBA朋友而言，确实不大友好；对于没有任何编程基础的朋友而言，那就更不友好了。 如果微软打算用它来代替VBA……我个人觉得可能性并不大。Office.js自身的定位，是跨平台制作Office外部插件，完全可以独自美丽。 不过话说回来，世上再没有比VBA更傻却又更全面的脚步语言了——不管是WPS还是微软，考虑的更多的都是跨平台性，而不是在功能上全面替代麻雀虽小五脏俱全的VBA。 打个响指，最后给大家举两个实际案例。 1丨条件判断语句 以下代码获取当前工作表A2:A10区域的成绩，并按照是否小于60分为标准，判断成绩是否及格。 Excel.run(async function(context) {//异步 var sheet = context.workbook.worksheets.getActiveWorksheet();//当前工作表 var range = sheet.getRange("B:B");//B列对象 range.clear();//清空B列 sheet.getRange("b1").values=[["结果"]];//B1单元格赋值 var val_range=sheet.getRange("a2:a10");//A2:A10区域对象 val_range.load("values");//获取值 await context.sync();//发送请求，获取属性 var r=val_range.values;//获取值集合 //console.log("Debug: " + JSON.stringify(r)) var rowCount=r.length;//行数 for(var i=1;i<=rowCount;i++){//循环判断 var v=r[i-1]; var range=sheet.getCell(i,1); v<60 ? range.values=[["不及格"]]:range.values=[["及格"]];//判断是否及格 } return context.sync();}).catch(function (error) { console.log(error); if (error instanceof OfficeExtension.Error) { console.log(error.debugInfo); }});Excel.run(async function(context) {//异步 var wb = context.workbook;//当前工作簿 var rng_data=wb.worksheets.getItem("数据源").getUsedRange();//数据源 var sht_res=wb.worksheets.getItem("结果表"); var rng_res=sht_res.getUsedRange();//查询区域 rng_data.load("values"); rng_res.load("values"); await context.sync();//发送请求，获取值属性 var d={};//对象 rng_data.values.forEach((v)=>{d[v[0]]=v[1]});//数组转对象结构 var r= rng_res.values for(var i=1;i<=r.length-1;i++){ var t=d[r[i][0]]; if(!t){r[i][1]="查无"} else {r[i][1]=t} }; sht_res.activate(); rng_res.values=r; //console.log("Debug: " +JSON.stringify(r)); return context.sync();}).catch(function (error) { console.log(error); if (error instanceof OfficeExtension.Error) { console.log(error.debugInfo); }}); |
|
|
