WPS表格宏实现定时任务逐时间展示照片

金豆呀

已于 2024-08-08 17:26:02 修改

阅读量795

点赞数 11

文章标签： wps

于 2024-08-08 17:13:52 首次发布

本文链接：https://blog.csdn.net/weixin_38943098/article/details/141031791

版权

这个是对WPS表格宏的定时任务的一个功能展示，重复代码比较多，要直接复制使用的话在AG3:AO10区域的单元格要嵌上照片才行（一共72张照片，我是把一张照片分了72份，做成一圈一圈逐时间显示的效果），这里面主要一个是定时调用方法的使用和对单元格背景、字体的一些设置。

//1、这个方法是表格页面布局的一些设置
function JING()
{
// 定位到Sheet1
     Application.Worksheets.Item('Sheet1').Activate();
// 设置表格背景色
     Range("A1:XFD1048576").Interior.ThemeColor=3;
// 指定单元格赋值
    Range("M1").Value2="WPS";
    Range("N1").Value2="表格";
    Range("O1").Value2="动态";
    Range("P1").Value2="展示";
    Range("Q1").Value2="照片";
//Font属性设置字体、大小、加粗等
     Range("A1:S10").Font.Name="宋体";
    Range("A1:S10").Font.Size=26;
     Range("A1:S10").Font.Bold=true;
//设置控制照片展示字体颜色，与表格背景色一致
     Range("A1:I10").Font.ThemeColor=3;
//设置字体居中
     Range("A1:S10").HorizontalAlignment = xlHAlignCenter;
//给照片展示区域写入公式(这里是直接写入的方式，通过遍历选中区域动态赋值在另一篇文章里面，嫌麻烦就不在这用了)
     Range("K3").Formula='=IF(A3<>"",AG3,"")';
     Range("K4").Formula='=IF(A4<>"",AG4,"")';
     Range("K5").Formula='=IF(A5<>"",AG5,"")';
     Range("K6").Formula='=IF(A6<>"",AG6,"")';
     Range("K7").Formula='=IF(A7<>"",AG7,"")';
     Range("K8").Formula='=IF(A8<>"",AG8,"")';
     Range("K9").Formula='=IF(A9<>"",AG9,"")';
     Range("K10").Formula='=IF(A10<>"",AG10,"")';
     Range("L3").Formula='=IF(B3<>"",AH3,"")';
     Range("L4").Formula='=IF(B4<>"",AH4,"")';
     Range("L5").Formula='=IF(B5<>"",AH5,"")';
     Range("L6").Formula='=IF(B6<>"",AH6,"")';
     Range("L7").Formula='=IF(B7<>"",AH7,"")';
     Range("L8").Formula='=IF(B8<>"",AH8,"")';
     Range("L9").Formula='=IF(B9<>"",AH9,"")';
     Range("L10").Formula='=IF(B10<>"",AH10,"")';
     Range("M3").Formula='=IF(C3<>"",AI3,"")';
     Range("M4").Formula='=IF(C4<>"",AI4,"")';
     Range("M5").Formula='=IF(C5<>"",AI5,"")';
     Range("M6").Formula='=IF(C6<>"",AI6,"")';
     Range("M7").Formula='=IF(C7<>"",AI7,"")';
     Range("M8").Formula='=IF(C8<>"",AI8,"")';
     Range("M9").Formula='=IF(C9<>"",AI9,"")';
     Range("M10").Formula='=IF(C10<>"",AI10,"")';
     Range("N3").Formula='=IF(D3<>"",AJ3,"")';
     Range("N4").Formula='=IF(D4<>"",AJ4,"")';
     Range("N5").Formula='=IF(D5<>"",AJ5,"")';
     Range("N6").Formula='=IF(D6<>"",AJ6,"")';
     Range("N7").Formula='=IF(D7<>"",AJ7,"")';
     Range("N8").Formula='=IF(D8<>"",AJ8,"")';
     Range("N9").Formula='=IF(D9<>"",AJ9,"")';
     Range("N10").Formula='=IF(D10<>"",AJ10,"")';
     Range("O3").Formula='=IF(E3<>"",AK3,"")';
     Range("O4").Formula='=IF(E4<>"",AK4,"")';
     Range("O5").Formula='=IF(E5<>"",AK5,"")';
     Range("O6").Formula='=IF(E6<>"",AK6,"")';
     Range("O7").Formula='=IF(E7<>"",AK7,"")';
     Range("O8").Formula='=IF(E8<>"",AK8,"")';
     Range("O9").Formula='=IF(E9<>"",AK9,"")';
     Range("O10").Formula='=IF(E10<>"",AK10,"")';
     Range("P3").Formula='=IF(F3<>"",AL3,"")';
     Range("P4").Formula='=IF(F4<>"",AL4,"")';
     Range("P5").Formula='=IF(F5<>"",AL5,"")';
     Range("P6").Formula='=IF(F6<>"",AL6,"")';
     Range("P7").Formula='=IF(F7<>"",AL7,"")';
     Range("P8").Formula='=IF(F8<>"",AL8,"")';
     Range("P9").Formula='=IF(F9<>"",AL9,"")';
     Range("P10").Formula='=IF(F10<>"",AL10,"")';
     Range("Q3").Formula='=IF(G3<>"",AM3,"")';
     Range("Q4").Formula='=IF(G4<>"",AM4,"")';
     Range("Q5").Formula='=IF(G5<>"",AM5,"")';
     Range("Q6").Formula='=IF(G6<>"",AM6,"")';
     Range("Q7").Formula='=IF(G7<>"",AM7,"")';
     Range("Q8").Formula='=IF(G8<>"",AM8,"")';
     Range("Q9").Formula='=IF(G9<>"",AM9,"")';
     Range("Q10").Formula='=IF(G10<>"",AM10,"")';
     Range("R3").Formula='=IF(H3<>"",AN3,"")';
     Range("R4").Formula='=IF(H4<>"",AN4,"")';
     Range("R5").Formula='=IF(H5<>"",AN5,"")';
     Range("R6").Formula='=IF(H6<>"",AN6,"")';
     Range("R7").Formula='=IF(H7<>"",AN7,"")';
     Range("R8").Formula='=IF(H8<>"",AN8,"")';
     Range("R9").Formula='=IF(H9<>"",AN9,"")';
     Range("R10").Formula='=IF(H10<>"",AN10,"")';
     Range("S3").Formula='=IF(I3<>"",AO3,"")';
     Range("S4").Formula='=IF(I4<>"",AO4,"")';
     Range("S5").Formula='=IF(I5<>"",AO5,"")';
     Range("S6").Formula='=IF(I6<>"",AO6,"")';
     Range("S7").Formula='=IF(I7<>"",AO7,"")';
     Range("S8").Formula='=IF(I8<>"",AO8,"")';
     Range("S9").Formula='=IF(I9<>"",AO9,"")';
     Range("S10").Formula='=IF(I10<>"",AO10,"")';
}
//2、下面72个单元格赋值，这里每个单元格单独赋值是因为要定时任务调用，作逐时间显示的一个效果
//第一排9个字
function JING1()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("A3").Value2="吒";
}
function JING2()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("B3").Value2="吒";
}
function JING3()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("C3").Value2="吒";
}
function JING4()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("D3").Value2="吒";
}
function JING5()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("E3").Value2="吒";
}
function JING6()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("F3").Value2="吒";
}
function JING7()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("G3").Value2="吒";
}
function JING8()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("H3").Value2="吒";
}
function JING9()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("I3").Value2="吒";
}
//最后一列7字
function JING10()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("I4").Value2="吒";
}
function JING11()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("I5").Value2="吒";
}
function JING12()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("I6").Value2="吒";
}
function JING13()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("I7").Value2="吒";
}
function JING14()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("I8").Value2="吒";
}
function JING15()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("I9").Value2="吒";
}
function JING16()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("I10").Value2="吒";
}

//最后一排8个字
function JING17()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("H10").Value2="吒";
}
function JING18()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("G10").Value2="吒";
}
function JING19()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("F10").Value2="吒";
}
function JING20()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("E10").Value2="吒";
}
function JING21()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("D10").Value2="吒";
}
function JING22()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("C10").Value2="吒";
}
function JING23()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("B10").Value2="吒";
}
function JING24()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("A10").Value2="吒";
}
//第一列6个字
function JING25()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("A9").Value2="吒";
}
function JING26()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("A8").Value2="吒";
}
function JING27()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("A7").Value2="吒";
}
function JING28()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("A6").Value2="吒";
}
function JING29()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("A5").Value2="吒";
}
function JING30()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("A4").Value2="吒";
}
//第二行7个字
function JING31()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("B4").Value2="吒";
}
function JING32()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("C4").Value2="吒";
}
function JING33()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("D4").Value2="吒";
}
function JING34()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("E4").Value2="吒";
}
function JING35()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("F4").Value2="吒";
}
function JING36()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("G4").Value2="吒";
}
function JING37()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("H4").Value2="吒";
}
//倒数2列5个字
function JING38()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("H5").Value2="吒";
}
function JING39()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("H6").Value2="吒";
}
function JING40()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("H7").Value2="吒";
}
function JING41()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("H8").Value2="吒";
}
function JING42()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("H9").Value2="吒";
}

//倒数2行6个字
function JING43()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("G9").Value2="吒";
}
function JING44()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("F9").Value2="吒";
}
function JING45()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("E9").Value2="吒";
}
function JING46()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("D9").Value2="吒";
}
function JING47()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("C9").Value2="吒";
}
function JING48()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("B9").Value2="吒";
}
//第二列4个字
function JING49()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("B8").Value2="吒";
}
function JING50()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("B7").Value2="吒";
}
function JING51()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("B6").Value2="吒";
}
function JING52()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("B5").Value2="吒";
}
//第三行5个字 C5-G5
function JING53()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("C5").Value2="吒";
}
function JING54()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("D5").Value2="吒";
}
function JING55()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("E5").Value2="吒";
}
function JING56()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("F5").Value2="吒";
}
function JING57()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("G5").Value2="吒";
}
//倒数第三列3个字 G6-G8
function JING58()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("G6").Value2="吒";
}
function JING59()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("G7").Value2="吒";
}
function JING60()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("G8").Value2="吒";
}
//倒数第三行4个字 F8-C8
function JING61()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("F8").Value2="吒";
}
function JING62()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("E8").Value2="吒";
}
function JING63()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("D8").Value2="吒";
}
function JING64()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("C8").Value2="吒";
}
//第三列2个字个字 C7-C6
function JING65()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("C7").Value2="吒";
}
function JING66()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("C6").Value2="吒";
}
//第四行3个字个字 D6-F6
function JING67()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("D6").Value2="吒";
}
function JING68()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("E6").Value2="吒";
}
function JING69()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("F6").Value2="吒";
}
//第五行3个字个字 F7-D7
function JING70()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("F7").Value2="吒";
}
function JING71()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("E7").Value2="吒";
}
function JING72()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("D7").Value2="吒";
}

//3、清除表格内容的方法
function QINGCHU()
{
Application.Worksheets.Item('Sheet1').Activate();
Range("A1:S10").ClearContents();
}

//4、这个是要运行的定时方法，在表格页面搞个按钮，连接宏，点击就能展示照片了
function DINGSHI()
{
Application.Worksheets.Item('Sheet1').Activate();
var now = new Date();
var now_time = now.getTime();
now.setTime(now_time+800*1);
Application.OnTime(now.toLocaleString(),'JING');

//..........................................................分割线之第一排...............................................................
    now.setTime(now_time+800*10);
Application.OnTime(now.toLocaleString(),'JING1');
    now.setTime(now_time+800*11);
Application.OnTime(now.toLocaleString(),'JING2');
    now.setTime(now_time+800*12);
Application.OnTime(now.toLocaleString(),'JING3');
    now.setTime(now_time+800*13);
Application.OnTime(now.toLocaleString(),'JING4');
    now.setTime(now_time+800*14);
Application.OnTime(now.toLocaleString(),'JING5');
    now.setTime(now_time+800*15);
Application.OnTime(now.toLocaleString(),'JING6');
    now.setTime(now_time+800*16);
Application.OnTime(now.toLocaleString(),'JING7');
    now.setTime(now_time+800*17);
Application.OnTime(now.toLocaleString(),'JING8');
    now.setTime(now_time+800*18);
Application.OnTime(now.toLocaleString(),'JING9');
   //..........................................................分割线之最后一列...............................................................
    now.setTime(now_time+800*10);
Application.OnTime(now.toLocaleString(),'JING10');
    now.setTime(now_time+800*11);
Application.OnTime(now.toLocaleString(),'JING11');
    now.setTime(now_time+800*12);
Application.OnTime(now.toLocaleString(),'JING12');
    now.setTime(now_time+800*13);
Application.OnTime(now.toLocaleString(),'JING13');
    now.setTime(now_time+800*14);
Application.OnTime(now.toLocaleString(),'JING14');
    now.setTime(now_time+800*15);
Application.OnTime(now.toLocaleString(),'JING15');
    now.setTime(now_time+800*16);
Application.OnTime(now.toLocaleString(),'JING16');
//..........................................................分割线之最后一行...............................................................
    now.setTime(now_time+800*10);
Application.OnTime(now.toLocaleString(),'JING17');
    now.setTime(now_time+800*11);
Application.OnTime(now.toLocaleString(),'JING18');
    now.setTime(now_time+800*12);
Application.OnTime(now.toLocaleString(),'JING19');
    now.setTime(now_time+800*13);
Application.OnTime(now.toLocaleString(),'JING20');
    now.setTime(now_time+800*14);
Application.OnTime(now.toLocaleString(),'JING21');
    now.setTime(now_time+800*15);
Application.OnTime(now.toLocaleString(),'JING22');
    now.setTime(now_time+800*16);
Application.OnTime(now.toLocaleString(),'JING23');
    now.setTime(now_time+800*17);
Application.OnTime(now.toLocaleString(),'JING24');
   //..........................................................分割线之第一列...............................................................
    now.setTime(now_time+800*10);
Application.OnTime(now.toLocaleString(),'JING25');
    now.setTime(now_time+800*11);
Application.OnTime(now.toLocaleString(),'JING26');
    now.setTime(now_time+800*12);
Application.OnTime(now.toLocaleString(),'JING27');
    now.setTime(now_time+800*13);
Application.OnTime(now.toLocaleString(),'JING28');
    now.setTime(now_time+800*14);
Application.OnTime(now.toLocaleString(),'JING29');
    now.setTime(now_time+800*15);
Application.OnTime(now.toLocaleString(),'JING30');
   //..........................................................分割线之第二行...............................................................
    now.setTime(now_time+800*19);
Application.OnTime(now.toLocaleString(),'JING31');
    now.setTime(now_time+800*20);
Application.OnTime(now.toLocaleString(),'JING32');
    now.setTime(now_time+800*21);
Application.OnTime(now.toLocaleString(),'JING33');
    now.setTime(now_time+800*22);
Application.OnTime(now.toLocaleString(),'JING34');
    now.setTime(now_time+800*23);
Application.OnTime(now.toLocaleString(),'JING35');
    now.setTime(now_time+800*24);
Application.OnTime(now.toLocaleString(),'JING36');
    now.setTime(now_time+800*25);
Application.OnTime(now.toLocaleString(),'JING37');
//..........................................................分割线之倒数2列...............................................................
    now.setTime(now_time+800*19);
Application.OnTime(now.toLocaleString(),'JING38');
    now.setTime(now_time+800*20);
Application.OnTime(now.toLocaleString(),'JING39');
    now.setTime(now_time+800*21);
Application.OnTime(now.toLocaleString(),'JING40');
    now.setTime(now_time+800*22);
Application.OnTime(now.toLocaleString(),'JING41');
    now.setTime(now_time+800*23);
Application.OnTime(now.toLocaleString(),'JING42');
   //..........................................................分割线之倒数2行...............................................................
    now.setTime(now_time+800*19);
Application.OnTime(now.toLocaleString(),'JING43');
    now.setTime(now_time+800*20);
Application.OnTime(now.toLocaleString(),'JING44');
    now.setTime(now_time+800*21);
Application.OnTime(now.toLocaleString(),'JING45');
    now.setTime(now_time+800*22);
Application.OnTime(now.toLocaleString(),'JING46');
    now.setTime(now_time+800*23);
Application.OnTime(now.toLocaleString(),'JING47');
    now.setTime(now_time+800*24);
Application.OnTime(now.toLocaleString(),'JING48');
//..........................................................分割线之第二列...............................................................
    now.setTime(now_time+800*19);
Application.OnTime(now.toLocaleString(),'JING49');
    now.setTime(now_time+800*20);
Application.OnTime(now.toLocaleString(),'JING50');
    now.setTime(now_time+800*21);
Application.OnTime(now.toLocaleString(),'JING51');
    now.setTime(now_time+800*22);
Application.OnTime(now.toLocaleString(),'JING52');
//..........................................................分割线之第三行...............................................................
    now.setTime(now_time+800*26);
Application.OnTime(now.toLocaleString(),'JING53');
    now.setTime(now_time+800*27);
Application.OnTime(now.toLocaleString(),'JING54');
    now.setTime(now_time+800*28);
Application.OnTime(now.toLocaleString(),'JING55');
    now.setTime(now_time+800*29);
Application.OnTime(now.toLocaleString(),'JING56');
    now.setTime(now_time+800*30);
Application.OnTime(now.toLocaleString(),'JING57');
    //..........................................................分割线之倒数第三列........................................................
    now.setTime(now_time+800*26);
Application.OnTime(now.toLocaleString(),'JING58');
    now.setTime(now_time+800*27);
Application.OnTime(now.toLocaleString(),'JING59');
    now.setTime(now_time+800*28);
Application.OnTime(now.toLocaleString(),'JING60');
    //..........................................................分割线之倒数第三行..........................................................
Application.OnTime(now.toLocaleString(),'JING61');
    now.setTime(now_time+800*27);
Application.OnTime(now.toLocaleString(),'JING62');
    now.setTime(now_time+800*28);
Application.OnTime(now.toLocaleString(),'JING63');
      now.setTime(now_time+800*29);
Application.OnTime(now.toLocaleString(),'JING64');
//..........................................................分割线之第三列...............................................................
    now.setTime(now_time+800*26);
Application.OnTime(now.toLocaleString(),'JING65');
    now.setTime(now_time+800*27);
Application.OnTime(now.toLocaleString(),'JING66');
//..........................................................分割线之第四行...............................................................
    now.setTime(now_time+800*31);
Application.OnTime(now.toLocaleString(),'JING67');
    now.setTime(now_time+800*32);
Application.OnTime(now.toLocaleString(),'JING68');
    now.setTime(now_time+800*33);
Application.OnTime(now.toLocaleString(),'JING69');
//..........................................................分割线之第五行...............................................................
    now.setTime(now_time+800*34);
Application.OnTime(now.toLocaleString(),'JING70');
    now.setTime(now_time+800*35);
Application.OnTime(now.toLocaleString(),'JING71');
    now.setTime(now_time+800*36);
Application.OnTime(now.toLocaleString(),'JING72');
//.....................................................分割线之清除A1:S10表格容..................................................
   now.setTime(now_time+800*40);
   Application.OnTime(now.toLocaleString(),'QINGCHU');
}

金豆呀

关注

11
点赞
踩
19

收藏

觉得还不错? 一键收藏
0
评论
WPS表格宏实现定时任务逐时间展示照片

//Font属性设置字体、大小、加粗等 Range("A1:S10").Font.Name="宋体"; Range("A1:S10").Font.Size=26; Range("A1:S10").Font.Bold=true; //设置控制照片展示字体颜色，与表格背景色一致 Range("A1:I10").Font.ThemeColor=3; //设置字体居中 Range("A1:S10").HorizontalAlignment = xlHAlignC
复制链接

扫一扫