精华内容
下载资源
问答
  • 需求:在个人构建的知识库中,有很多很专业的词条,读来生涩(有时一个词条中不认识的英语术语就有不少),如果能让电脑将这个词条朗读出来,可以让双眼稍微放松一下,也让耳朵获得活动机会,岂不美哉。...

    需求:在个人构建的知识库中,有很多很专业的词条,读来生涩(有时一个词条中不认识的英语术语就有不少),如果能让电脑将这个词条朗读出来,可以让双眼稍微放松一下,也让耳朵获得活动机会,岂不美哉。下面的代码则实现了此功能。

    代码:

    1. 定义“朗读”子程序

    Sub speak()
            ActiveCell.Select
            Selection.speak
    End Sub

    2. 向Excel程序中添加右键菜单命令,调用朗读子程序

    Sub 添加自定义菜单()
      On Error Resume Next
      Dim myBAR As CommandBarButton
      Application.CommandBars("CELL").Controls("朗读").Delete
      Set myBAR = Application.CommandBars("cell").Controls.Add(before:=1) '添加到最上的位置
       With myBAR
         .Caption = "朗读"
         .BeginGroup = True '添加分组线
         .FaceId = 186 '显示的图标
         .Style = msoButtonIconAndCaption '图标和文字的显示
         .OnAction = "speak" '指定要运行的宏
       End With
    End Sub
    

    效果图:

    在自己电脑上测试的结果是:中英文都可以流畅的朗读,再次给Excel点个赞!

    展开全文
  • Sub excel连接数据库()  Dim Con As New ADODB.Connection  Dim strCon As String  Dim rs As ADODB.Recordset '设置记录集  Dim i, t    t = Timer    strCon = " Provider=Microsoft.AC
    Option Explicit


    Sub excel连接数据库()




        Dim Con As New ADODB.Connection
        Dim strCon, strsql As String
        Dim rs As ADODB.Recordset  '设置记录集
        Dim i, t
        
        t = Timer
       
        strCon = " Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source= C:\Users\Administrator\Desktop\a1.xlsx" & _
                    ";Extended Properties=""Excel 12.0;HDR=True"";"
       
       


        strsql = "select a.id,a.firstname,b.lastname from [a1$] a left join [Excel 8.0;hdr=1;imex=1;Database=C:\Users\Administrator\Desktop\a2.xlsx].[a2$] b  on a.id=b.id"
       
        Con.Open strCon
        
        Set rs = Con.Execute(strsql)
        
        
        For i = 0 To rs.Fields.Count - 1 '逐个字段
            Cells(3, i + 3) = rs.Fields(i).Name '取字段名  字头放置在cell(3,3)
        Next i
        
        Cells(4, 3).CopyFromRecordset rs
         
        rs.Close
        Con.Close
        
        Set rs = Nothing
        Set Con = Nothing
        
        MsgBox "提取完毕" & "耗时" & Round(Timer - t, 4) & "秒"
        
    End Sub

    展开全文
  • 1).Value, p1, p2 - p1 + 1) tempstr = Right(Cells(i, 1).Value, length - p2 - 1) p1 = InStr(tempstr, “[”) p2 = InStr(tempstr, “]”) Cells(i + 3, 1).Value = Mid(tempstr, p1, p2 - p1 + 1) length = Len...

    Sub matclear()

    Dim matname(1000) As String
    Dim p1 As Integer
    Dim p2 As Integer
    Dim i As Integer
    Dim length As Integer
    Dim tempstr As String
    i = 1
    Do
    If i = 1 Then
    p1 = InStr(Cells(i, 1), “[”)
    p2 = InStr(Cells(i, 1), “]”)
    matname(i) = Mid(Cells(i, 1), p1 + 1, p2 - p1 - 1)
    'MsgBox matname(i)
    End If

    If InStr(Cells(i, 1).Value, “[”) <> 0 And i <> 1 Then

    Cells(i + 1, 1).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    length = Len(Cells(i, 1))
    p1 = InStr(Cells(i, 1), “[”)
    p2 = InStr(Cells(i, 1), “]”)
    Cells(i, 2).Value = Left(Cells(i, 1).Value, p1 - 1)
    Cells(i + 1, 1).Value = Mid(Cells(i, 1).Value, p1, p2 - p1 + 1)
    tempstr = Right(Cells(i, 1).Value, length - p2 - 1)
    p1 = InStr(tempstr, “[”)
    p2 = InStr(tempstr, “]”)
    Cells(i + 3, 1).Value = Mid(tempstr, p1, p2 - p1 + 1)
    length = Len(tempstr)
    Cells(i + 4, 1).Value = Right(tempstr, length - p2)
    Cells(i, 1).Value = Cells(i, 2).Value
    i = i + 3
    End If

    i = i + 1
    If i = 20000 Then
    MsgBox “complete!”
    Exit Do
    End If
    Loop

    End Sub

    展开全文
  • excel自动翻译vba Excel VBA:自动显示消息 (Excel VBA: Show Message Automatically)Keeping Excel users on the right path is a big job, but somebody's got to do it! Last week we created an Excel message ...

    excel自动翻译vba

    Keeping Excel users on the right path is a big job, but somebody's got to do it! Last week we created an Excel message box in an order form, to remind users to select a customer name.

    让Excel用户走在正确的道路上是一项艰巨的任务,但是必须由别人来做! 上周,我们在订单表创建了一个Excel消息框 ,以提醒用户选择客户名称。

    Excel Message 00

    However, we don't want the message to appear in every order form – it should only show if the customer name cell is empty. We'll modify the code so it checks for a customer name.

    但是,我们不希望该消息出现在每个订单中-仅当客户名称单元为空时才显示。 我们将修改代码,以便检查客户名称。

    Sample File: You can download the Excel order form with last week's message macro. Enable macros when the file opens.

    示例文件 :您可以下载带有上周消息宏的Excel订单 。 文件打开时启用宏。

    清除客户名称单元 (Clear the Customer Name Cell)

    To see how to refer to the customer name cell, we'll record a test macro.

    要查看如何引用客户名称单元格,我们将记录一个测试宏。

    1. Select any sheet in the workbook, other than the OrderForm sheet.

      选择工作簿中除OrderForm工作表之外的任何工作表。
    2. Start the Macro Recorder

      启动宏记录器
    3. Name the macro NameTest, and store it in This Workbook

      命名宏NameTest,并将其存储在此工作簿中
    4. Click on the OrderForm sheet tab, to activate that sheet

      单击OrderForm工作表选项卡,以激活该工作表
    5. Select cell B5, where the customer name is entered

      选择单元格B5,在其中输入客户名称
    6. Stop the Macro Recorder

      停止宏记录器
    Excel Message 01

    To see the recorded macro:

    要查看录制的宏:

    1. On the Ribbon, click the View tab

      在功能区上,单击“视图”选项卡
    2. Click Macros, then click View Macros

      单击宏,然后单击查看宏
    3. In the list of macros, click NameTest, then click Edit.

      在宏列表中,单击NameTest,然后单击“编辑”。

    Here's the recorded code, with the comment lines and blank lines removed. It shows you how to refer to a sheet and cell in Excel VBA code.

    这是记录的代码,其中删除了注释行和空白行。 它显示了如何在Excel VBA代码中引用工作表和单元格。

    Excel Message 02

    We'll remove the Select in each line, and combine the two lines into one. At the end of the line, add: .Value = ""

    我们将删除每行中的“选择”,并将两行合并为一个。 在该行的末尾,添加:.Value =“”

    The revised line of code will set the value of the customer name cell to an empty string.

    修改后的代码行会将客户名称单元格的值设置为空字符串。

    Excel Message 03

    Run the NameTest macro, and it should clear the customer name cell.

    运行NameTest宏,它将清除客户名称单元格。

    检查客户名称 (Check For a Customer Name)

    We'll add the new code to the old code, that shows the message. To check for a customer name, we'll create an IF...THEN section in the macro, similar to what you'd do in an IF formula on the worksheet.

    我们将新代码添加到旧代码中,以显示消息。 要检查客户名称,我们将在宏中创建IF ... THEN部分,类似于您在工作表上的IF公式中所做的。

    In English, the instructions would be: IF the customer name cell is empty, THEN show the message. In the Excel VBA code, we'll use these three lines:

    用英语来说,说明将是:如果客户名称单元为空,则显示消息。 在Excel VBA代码中,我们将使用以下三行:

    ExcelMessage04

    Now, when you run the CustomerMessage macro, you should see the message box only if the customer name cell is empty.

    现在,当您运行CustomerMessage宏时,仅当客户名称单元为空时,您才应该看到消息框。

    下一步 (The Next Step)

    Next week, we'll add code to make the macro run automatically, when someone tries to print the order form. _________________ Previous Excel VBA articles:

    下周,当有人尝试打印订单时,我们将添加代码以使宏自动运行。 _________________以前的Excel VBA文章:

    翻译自: https://contexturesblog.com/archives/2009/12/18/excel-vba-show-message-automatically/

    excel自动翻译vba

    展开全文
  • VBA标红

    2010-12-01 19:49:00
    上级发来一个EXcel表格很长,要求把数据不连续的地方标注,本准备加工一下搞成数组然后用java来处理,悲剧的是溢出了, 知道有一个对excel处理的东东叫vba,查了一下,就用它了,这是我第一个VBA程序,...
  • double speed vba

    2020-12-07 10:38:38
    <div><p>when I use VBA, it plays games at double the speed of normal which sounds ok and lets people get through stuff quicker BUT it makes timing things a lot harder, and I cant find any throttle of ...
  • This Excel user's guide to VSTOthe new Excel Macro programming language being promoted by Microsoftshows how to perform the equivalent VBA actions with VSTO. The differences between the VSTO and VBA ...
  • 大家好,我们今日继续讲解VBA代码解决方案的第58讲内容:在VBA中查找指定工作表的方法。在上一个例子中,我们通过一个自定义函数解决了删除工作表的方法。其实实现目的的方法有很多种,不必要必须有某种办法,但是写...
  • VBA的一些问题

    2021-02-01 15:52:26
    比如说,字符串处理,函数是left、right,不支持字符串截取、定位功能。 然后是:现在用VB的少,社区基本上没有人。 然后,由于之前VBA宏可以传播病毒,所以默认是禁用宏的……如果你给电脑小白装了一个宏,会给你一...
  • This comprehensive guide starts at the beginning to get you acquainted with VBA so you can start recording macros right away. You'll then build upon that foundation to utilize the full capabilities ...
  • Excel——VBA

    2019-03-01 15:16:57
    Function 文本(rng As Range) As String Dim str As String For Each c In rng str = str &amp;amp;amp;amp;amp;amp;amp; Chr(10) &... 文本 = Right(str, Len(str) - 1) End Function ...
  • VBA与数据库

    2015-11-19 18:07:38
    •实例3-36 多表查询(右外连接 RIGHT OUTER JOINT) 102 •实例3-37 多表查询(子查询WHERE,ANY,SOME) 103 •实例3-38 多表查询(子查询EXISTS,NOT EXISTS) 105 •实例3-39 从两个数据表中查询出都存在的记录 ...
  • Excel 2013 Power Programming with VBA.pdf. Welcome to Excel 2013 Power ...workbooks that others will use — or if you simply want to get the most out of Excel — you’ve picked up the right book.
  • VBA 字符串操作

    2019-05-13 17:22:15
    'left,right,mid,Len Sub z1() Dim sr sr = "Excel精英培训网" MsgBox Left(sr, 5) MsgBox Right(sr, 5) MsgBox Mid(sr, 3, 5) MsgBox Left(sr, Len(sr) - 1) End Sub 'split Sub z2() Dim sr, ar...
  • VBA字符串函数

    2013-03-30 17:01:00
    VBA字符串函数 Trim(string) 去掉string左右两端空白 Ltrim(string) 去掉string左端空白 Rtrim(string) 去掉string右端空白 Len(string) 计算string长度 Left(string, x) 取string左段x个字符组成的字符串 ...
  • VBA常用函数

    2014-01-08 10:28:00
    从字符串右侧开始:Right(string,length) 从字符串中间开始:mid(string,start,[length]) length:可选参数 ②大小写变换: 小写变大写:Ucase(string) 大写变小写:Lcase(string) 字符串类型变换:StrConv(stri....
  • 我正在尝试在Excel中使用VBA来自动将.txt文件(制表符分隔)转换为.xlsx文件 . 这就是我所拥有的:Set WB = Workbooks.Open(folder + file, , , 1)If Right(file, 3) = "txt" Or Right(file, 3) = "xls" ...
  • VBA关键字总结

    2011-07-11 17:51:00
    字符串处理关键字总结请参阅 作用 关键字 比较两个字符串。 StrComp 变换字符串。 StrConv 大小写变换。 Format, LCase, UCase 建立重复字符的字符串。 Space, String 计算... InStr, Left, LTrim, Mid, Right, ...
  • VBA 字符串函数

    千次阅读 2019-05-11 16:02:31
    len(s) 求长度 ...right(s,a) 从右边取若干字符 mid(s,a,b) 从中间取若干字符 instr(s1,s2) s1中查找字符串s2,返回位置 instr(a,s1,s2) 从a位置在s1中查找字符串s2,返回位置 ---------------...
  • 大家好,我们今日继续讲解VBA代码解决方案的第58讲内容:在VBA中查找指定工作表的方法。在上一个例子中,我们通过一个自定义函数解决了删除工作表的方法。其实实现目的的方法有很多种,不必要必须有某种办法,但是写...
  • vba-其他程序

    2018-04-02 21:37:00
    '1、单元格中最后一个为 . 则删除 否则不作任何操作 Sub m() x = Cells(Rows.Count, 1).End(xlUp).Row + 1 Dim s, str For i = 1 To x ...s = Right(str, 1) If s = "." Then Range("B" &...
  • vba 跨文件统计

    2017-01-24 17:01:21
    Sub test() Dim path As String, name As String Dim sht As Worksheet Dim i As Integer, arr() Dim rng As Range i = 1 path = "D:\vbatest\checked" path = path & IIf(Right(path, 1) = "\", "", "\")
  • vba字符串处理

    2017-05-15 11:31:02
    mid(源字串,起始位号x,提取长度m);返回第x位起m个字符。...right(源字串,右侧截取长度y);返回右侧y个字符 instr(源字串,待查找子字串u);返回子字串首字符在源字串中的起始位号 chr(65);返回字母A
  • 第9章 模块和VBA程序设计_习题 一选择题 1模块是存放代码容器其中窗体就是一个 A A类模块 B标准模块 C子过程 D函数过程 2使用Dim申明变量若省略As类型则所创建变量默认为 C AInteger BString CVariant D不正当变量 3...
  • path = "F:\vba" '当不确定路径的最后一位是不是"\"时,可以用如下代码判断: If Right(path , 1) <> "\" Then path = path & "\" End If '遍历文件夹,获取以".xlsx"结尾的文件 Dim count As ...
  • VBA字符串函数列表 Trim(string) 去掉string左右两端空白 Ltrim(string) 去掉string左端空白 Rtrim(string) 去掉string右端空白 Len(string) 计算string长度 Left(string, x) 取string左段x个字符组成的字符串 ...
  • VBA-M 2.1.4 does not open

    2020-12-07 10:39:46
    <p>Operating System (Windows, Mac, Linux (state distribution), etc.): Windows 8.1 x64, up to date as of 01/10/2019 (I am actualising it right now). I tried in my other computer with Windows 7 x64 and ...
  • vba 实现红黑树符号表

    2020-12-31 00:21:22
    红黑树RedNodeRedBlackTreeTable RedNode Enum NodeColor ' 红树 RED = 0 ...Private right As RedNode Private key As Variant Private value As Variant Private treeSize As Long Priv
  • Private right As Node Private key As Variant Private value As Variant Private treeSize As Long Property Let letLeft(ByRef l As Node) assign left, l End Property Property Let letRight(ByRef r As...

空空如也

空空如也

1 2 3 4 5 ... 13
收藏数 252
精华内容 100
关键字:

rightvba