EXCEL的漂亮小技巧 , VBA学习中

tingnishuo

JunJun2013 发表于 2-6-2014 12:14

                               
登录/注册后可看大图

我用最傻的公式办法可以实现,在右边加上三列。
A                     B                       C                          ...

谢谢谢谢！真是太好了！

JunJun2013

通过系统录制宏后，点编辑, 可以参看宏的VBA语言，可以复制应用到新的程序中，这个是最实用的上手办法了。
毕竟那么多功能和定义，一下子都掌握难。

一个最基本的程序是这样的：

- 用撇号描述程序的内容,比如 ‘this is for testing
- 定义变量，比如dim total as long(或者integer)
- 功能或任务，最基本的： +，-，*，/，=，<=,>=,<>, &, msgbox, msgbox(msg, vbYesNo) ，" " 给予文本数值，# # 给予日期值，比如#03/06/2014#
                       还有这个^,表示求幂，计算优先顺序同常理，用（）来改变默认优先顺序
- 结构: 比如，If-Then-Else 或者 loop

不用管大小写和空格，回车后自动修正。

例1：--运行这个可以得到101，这里的结构就是loop
Sub testing()
'testing
Dim total As Long, i As Long
total = 0
For i = 0 To 100
total = total + 1
Next i
MsgBox total
End Sub


例2：--询问对话框，这个例子中有关于信息框和如何分配单位格数值的表达（入门必备）。
Sub msgtesting()
Msg = "is your name " & Application.Workbooks("test.xlsm").Worksheets("sheet1").Range("A1") & "?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then
MsgBox "oh, never mind"
Else: MsgBox "I must be clairvoyang"
End If
End Sub

windwing00

Excel的宏功能类似于Visual C++的MFC（可视化编程），比较简单也很好用，需要有点编程基础的，我以前编过从一个文件夹下面的400-500个excel表格里面提取数据到一个excel表里面。

JunJun2013

windwing00 发表于 2-6-2014 17:44

                               
登录/注册后可看大图

Excel的宏功能类似于Visual C++的MFC（可视化编程），比较简单也很好用，需要有点编程基础的，我以前编过从 ...

是的，有点基础会有基本的概念和兴趣。其实任何人有足够耐心也是可以的，就是普遍没有兴趣。
我现在面临的是逐一了解众多的理论（目前思路还挺混乱的，只会最简单的程序），然后如何应用到工作中，的确挺费时间的。对于文件数目多，数据复杂，宏的优势就显出来了。
你介意分享一下你曾经编过的吗？贴出部分你认为漂亮的procedure/function(隐去敏感信息）。谢谢。

TJLADY

支持楼主！
我们公司里的一些数据库和Datasheet都需要VBA开发，公司里有一些业余高手，我最近也开始感兴趣

windwing00

这个功能看起来好像很复杂高效率，实际上也很简单的，就是懂得读取数据的命令和每个函数的参数是什么，VBA和MFC都是趋向于模块化的编程手段，搞清楚输入、输出和衔接好各个模块之间关系的话就好多了。我现在也好久没做过编程了，已经脱离这块很久了，转行做了通信施工设计，发现到这里工作很难找，不如做软件工程师好找工作。

JunJun2013

Not   Performs a logical negation on an expression.
And   Performs a logical conjunction on two expressions.
Or     Performs a logical disjunction on two expressions.
Xor    Performs a logical exclusion on two expressions.
Eqv   Performs a logical equivalence on two expressions.
Imp   Performs a logical implication on two expressions.
Is       Compares two object variables
Like    Compares two strings by using wildcard characters

JunJun2013

Statement Action
AppActivate Activates an application window
Beep Sounds a tone via the computer’s speaker
Call Transfers control to another procedure
ChDir Changes the current directory
ChDrive Changes the current drive
Close Closes a text file
Const Declares a constant value
Date Sets the current system date
Declare Declares a reference to an external procedure in a Dynamic Link Library (DLL)
DefBool Sets the default data type to Boolean for variables that begin with specified letters
DefByte Sets the default data type to Byte for variables that begin with specified letters
DefCur Sets the default data type to Currency for variables that begin with specified
letters
DefDate Sets the default data type to Date for variables that begin with specified letters
DefDec Sets the default data type to Decimal for variables that begin with specified letters
DefDbl Sets the default data type to Double for variables that begin with specified letters
DefInt Sets the default data type to Integer for variables that begin with specified letters
DefLng Sets the default data type to Long for variables that begin with specified letters
DefObj Sets the default data type to Object for variables that begin with specified letters
DefSng Sets the default data type to Single for variables that begin with specified letters
DefStr Sets the default data type to String for variables that begin with specified letters
DefVar Sets the default data type to Variant for variables that begin with specified letters
DeleteSetting Deletes a section or key setting from an application’s entry in the Windows Registry
Dim Declares variables and (optionally) their data types
Do-Loop Loops through a set of instructions
End Used by itself, exits the program; also used to end a block of statements that
begin with If, With, Sub, Function, Property, Type, or Select
Enum Declares a type for enumeration
Erase Re-initializes an array
Error Simulates a specific error condition
Event Declares a user-defined event
Exit Do Exits a block of Do-Loop code
Exit For Exits a block of For-Next code
Exit Function Exits a Function procedure
Exit Property Exits a property procedure
Exit Sub Exits a subroutine procedure
FileCopy Copies a file
For Each-Next Loops through a set of instructions for each member of a series
For-Next Loops through a set of instructions a specific number of times
Function Declares the name and arguments for a Function procedure
Get Reads data from a text file
GoSub...Return Branches to and returns from a procedure
GoTo Branches to a specified statement within a procedure
If-Then-Else Processes statements conditionally
Implements Specifies an interface or class that will be implemented in a class module
Input # Reads data from a sequential text file
Kill Deletes a file from a disk
Let Assigns the value of an expression to a variable or property
Line Input # Reads a line of data from a sequential text file
Load Loads an object but doesn’t show it
Lock...Unlock Controls access to a text file
Lset Left-aligns a string within a string variable
Mid Replaces characters in a string with other characters
MkDir Creates a new directory
Name Renames a file or directory
On Error Gives specific instructions for what to do in the case of an error
On...GoSub Branches, based on a condition
On...GoTo Branches, based on a condition
Open Opens a text file
Option Base Changes the default lower limit for arrays
Option Compare Declares the default comparison mode when comparing strings
Option Explicit Forces declaration of all variables in a module
Option Private Indicates that an entire module is Private
Print # Writes data to a sequential file
Private Declares a local array or variable
Property Get Declares the name and arguments of a Property Get procedure
Property Let Declares the name and arguments of a Property Let procedure
Property Set Declares the name and arguments of a Property Set procedure
Public Declares a public array or variable
Put Writes a variable to a text file
RaiseEvent Fires a user-defined event
Randomize Initializes the random number generator
ReDim Changes the dimensions of an array
Rem Specifies a line of comments (same as an apostrophe [‘])
Reset Closes all open text files
Resume Resumes execution when an error-handling routine finishes
RmDir Removes an empty directory
RSet Right-aligns a string within a string variable
SaveSetting Saves or creates an application entry in the Windows Registry
Seek Sets the position for the next access in a text file
Select Case Processes statements conditionally
SendKeys Sends keystrokes to the active window
Set Assigns an object reference to a variable or property
SetAttr Changes attribute information for a file
Static Declares variables at the procedure level so that the variables retain their values as long as the code is running
Stop Pauses the program
Sub Declares the name and arguments of a Sub procedure
Time Sets the system time
Type Defines a custom data type
Unload Removes an object from memory
While...Wend Loops through a set of instructions as long as a certain condition remains true
Width # Sets the output line width of a text file
With Sets a series of properties for an object
Write # Writes data to a sequential text file

JunJun2013

EXCEL中fx函数可能不包括在这个清单里，可以直接引用，比如：
Deg = Application.WorksheetFunction.Degrees(3.14)
或者简写为：Deg = Application.Degrees(3.14)

Abs Returns the absolute value of a number
Array Returns a variant containing an array
Asc Converts the first character of a string to its ASCII value
Atn Returns the arctangent of a number
CallByName Executes a method, or sets or returns a property of an object
CBool Converts an expression to a Boolean data type
CByte Converts an expression to a Byte data type
CCur Converts an expression to a Currency data type
CDate Converts an expression to a Date data type
CDbl Converts an expression to a Double data type
CDec Converts an expression to a Decimal data type
Choose Selects and returns a value from a list of arguments
Chr Converts a character code to a string
CInt Converts an expression to an Integer data type
CLng Converts an expression to a Long data type
Cos Returns the cosine of a number
CreateObject Creates an Object Linking and Embedding (OLE) Automation object
CSng Converts an expression to a Single data type
CStr Converts an expression to a String data type
CurDir Returns the current path
CVar Converts an expression to a variant data type
CVDate Converts an expression to a Date data type (for compatibility, not recommended)
CVErr Returns a user-defined error value that corresponds to an error number
Date Returns the current system date
DateAdd Adds a time interval to a date
DateDiff Returns the time interval between two dates
DatePart Returns a specified part of a date
DateSerial Converts a date to a serial number
DateValue Converts a string to a date
Day Returns the day of the month of a date
DDB Returns the depreciation of an asset
Dir Returns the name of a file or directory that matches a pattern
DoEvents Yields execution so the operating system can process other events
Environ Returns an operating environment string
EOF Returns True if the end of a text file has been reached
Error Returns the error message that corresponds to an error number
Exp Returns the base of natural logarithms (e) raised to a power
FileAttr Returns the file mode for a text file
FileDateTime Returns the date and time when a file was last modified
FileLen Returns the number of bytes in a file
Filter Returns a subset of a string array, filtered
Fix Returns the integer portion of a number
Format Displays an expression in a particular format
FormatCurrency Returns an expression formatted with the system currency symbol
FormatDateTime Returns an expression formatted as a date or time
FormatNumber Returns an expression formatted as a number
FormatPercent Returns an expression formatted as a percentage
FreeFile Returns the next available file number when working with text files
FV Returns the future value of an annuity
GetAllSettings Returns a list of settings and values from the Windows Registry
GetAttr Returns a code representing a file attribute
GetObject Retrieves an OLE Automation object from a file
GetSetting Returns a specific setting from the application’s entry in the Windows Registry
Hex Converts from decimal to hexadecimal
Hour Returns the hour of a time
IIf Evaluates an expression and returns one of two parts
Input Returns characters from a sequential text file
InputBox Displays a box to prompt a user for input
InStr Returns the position of a string within another string
InStrRev Returns the position of a string within another string from the end of the string
Int Returns the integer portion of a number
IPmt Returns the interest payment for a given period of an annuity
IRR Returns the internal rate of return for a series of cash flows
IsArray Returns True if a variable is an array
IsDate Returns True if a variable is a date
IsEmpty Returns True if a variable has not been initialized
IsError Returns True if an expression is an error value
IsMissing Returns True if an optional argument was not passed to a procedure
IsNull Returns True if an expression contains a Null value
IsNumeric Returns True if an expression can be evaluated as a number
IsObject Returns True if an expression references an OLE Automation object
Join Combines strings contained in an array
LBound Returns the smallest subscript for a dimension of an array
LCase Returns a string converted to lowercase
Left Returns a specified number of characters from the left of a string
Len Returns the number of characters in a string
Loc Returns the current read or write position of a text file
LOF Returns the number of bytes in an open text file
Log Returns the natural logarithm of a number
LTrim Returns a copy of a string with no leading spaces
Mid Returns a specified number of characters from a string
Minute Returns the minute of a time
MIRR Returns the modified internal rate of return for a series of periodic cash flows
Month Returns the month of a date as a number
MonthName Returns the month of a date as a string
MsgBox Displays a modal message box
Now Returns the current system date and time
NPer Returns the number of periods for an annuity
NPV Returns the net present value of an investment
Oct Converts from decimal to octal
Partition Returns a string representing a range in which a value falls
Pmt Returns a payment amount for an annuity
Ppmt Returns the principal payment amount for an annuity
PV Returns the present value of an annuity
QBColor Returns a red/green/blue (RGB) color code
Rate Returns the interest rate per period for an annuity
Replace Returns a string in which a substring is replaced with another string
RGB Returns a number representing an RGB color value
Right Returns a specified number of characters from the right of a string
Rnd Returns a random number between 0 and 1
Round Returns a rounded number
RTrim Returns a copy of a string with no trailing spaces
Second Returns the seconds portion of a specified time
Seek Returns the current position in a text file
Sgn Returns an integer that indicates the sign of a number
Shell Runs an executable program
Sin Returns the sine of a number
SLN Returns the straight-line depreciation for an asset for a period
Space Returns a string with a specified number of spaces
Spc Positions output when printing to a file
Split Returns a one-dimensional array containing a number of substrings
Sqr Returns the square root of a number
Str Returns a string representation of a number
StrComp Returns a value indicating the result of a string comparison
StrConv Returns a converted string
String Returns a repeating character or string
StrReverse Returns a string, reversed
Switch Evaluates a list of Boolean expressions and returns a value associated with the first
True expression
SYD Returns the sum-of-years’ digits depreciation of an asset for a period
Tab Positions output when printing to a file
Tan Returns the tangent of a number
Time Returns the current system time
Timer Returns the number of seconds since midnight
TimeSerial Returns the time for a specified hour, minute, and second
TimeValue Converts a string to a time serial number
Trim Returns a string without leading spaces and/or trailing spaces
TypeName Returns a string that describes the data type of a variable
UBound Returns the largest available subscript for a dimension of an array
UCase Converts a string to uppercase
Val Returns the number formed from any initial numeric characters of a string
VarType Returns a value indicating the subtype of a variable
Weekday Returns a number indicating a day of the week
WeekdayName Returns a string indicating a day of the week
Year Returns the year of a date

JunJun2013

对比较复杂的任务，又还不知道如何编写程序，可以通过系统录制数个宏，然后把这些宏合并到一个（在宏里引用另一个宏）
Application.Run "filename.xlsm!macroname"

也可以参考这些宏的语句，在编辑功能下复制黏贴或做相应修改，达到目的。

所有的基本功能，都可以通过录制宏来参考学习，这样，就开始编程了。

接下来会把最常规表达（命令）提炼出来一个清单，再学习一些代表性的综合程序实例。

最重要的还是要结合实践，要有具体的任务（必不可少），才有学习和应用的动力。我已经成功地减少了好多重复工作了。

另外上传一份VBA command 的full list供参考，对word, access也适用。

Complete List Of Visual Basic Commands.pdf (153.17 KB, 下载次数: 5)

4-6-2014 15:39 上传

点击文件名下载附件

JunJun2013

一些常规code:

定义变量，dim var as 空格后有很多选择
变量可是是整数，货币金额，日期，窗口，工作表/簿，一片单元格范围，等

打开文件：
ChDir "C:\foldername\foldername"
Workbooks.Open Filename:="C:\foldername\foldername\\filename.xls"

已经打开的文件，选中或激活(即光标进入，出于待编辑状态）
Windows("filename.xlsm").Activate

关闭开着的文件
Windows("filename.xls").Activate
ActiveWindow.Close
如果已经在active的状态，就直接 ActiveWindow.Close
ActiveWorkbook.close

移动上下或左右滚动条，改变表格在屏幕上的显示位置
这个是表示移动列，或行
ActiveWindow.ScrollColumn = 2 （这个数字表示几列，若是2，则C在最左边）
ActiveWindow.ScrollRow = 2 （这个数字表示几行，若是2，则row3在最上边）

用这个也可以，表示向右或下移，相当于移动滚动条旁的小箭头
ActiveWindow.SmallScroll ToRight:=8
ActiveWindow.SmallScroll Down:=4

选择某个或某些单元格
Range("C2:F16").Select
这个表示实际的位置

ActiveCell.Range("A1:F16").Select
这个表示从某个选中单元格起，相对位置

或者需要移动光标，括号第一个数表示行的移动，第二个数表示列的移动，负数表示反向移动
ActiveCell.Offset(-1, 0).Range("A1:C11").Select

ActiveCell.Offset(0, 7).Columns("A:C").EntireColumn.Select
ActiveCell.Offset(-10, 0).Rows("1:4").EntireRow.Select

这个表示某个单元格的相对位置，第一个表示从相对向右第8列起全选3列，第2个表示相对退回（向上，列则是向左）10列开始全选4行。

如果是实际位置（绝对值），就Columns("A:C").Select 或 Rows("3:4").Select， EntireColumn.加不加都可以

选定位置后，复制和粘贴
Selection.Copy
ActiveSheet.Paste

JunJun2013

Application Visual Basic/View/Object Brower 可以看所有应用功能

简单应用例子:

1. 取值
var = Application.Workbooks("filename.xlsm").Worksheets("sheetname").Range("A1")

2. 执行函数
Range("A3").Value = Application.WorksheetFunction.Average(Range("A1:A2"))

3. 在执行关闭或者替换等动作时，系统会警示，需要手工点是或否，可以关闭警示，直接执行（在ACCESS中也一样）。记得要再打开。

Application.DisplayAlerts = False
ActiveWorkbook.Close （例）
Application.DisplayAlerts = True

4. 循环操作时，可关闭显示更新，使得运行速度更快，如：
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To 10000
Range("A1").Value = i
Next i
Application.ScreenUpdating = True


5.With 的用法举例--就是省去重复的语句
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

6.Set, 设定某个值
比如 Dim InputArea As Range
Set InputArea = Range(“C16:E16”)

7. 单元格工作表工作薄的性质，可以给值，用 = 来表示
当面的：activecell.value, activesheet.name, activecell.font, activeworkbook.name, ActiveCell.FormulaR1C1（一行一列）等等，也包括上面例子的填充色等
公式的用法：ActiveCell.FormulaR1C1 = "='[test.xlsm]Sheet5'!R6C2" （第6行第2列），这个[ ]和 ! 是被取值的单元格的表达
也可以用selection（就是前面执行过选取区域功能了）或者直接输入目标名字来表达
比如，Application.Workbooks("test.xlsm").Worksheets("sheet5").Range("A1").Value = 100 这里application加不加都可以

8. _下划线分段

9.  这个例子就是手动输入某个值的对话框，Username = InputBox("Enter your name")

JunJun2013

For Next Loop 的用法, 32楼还有一个For Next的用法，同时简单If Then的用法也在32楼，不再举例
Dim Item As Worksheet
For Each Item In ActiveWorkbook.Worksheets
MsgBox Item.Name
Next Item

GoTo 的用法
Sub atest()
Username = InputBox("Enter your name")
If Username <> "Jack" Then GoTo WrongName
MsgBox "Welcome"
Exit Sub
WrongName:
MsgBox "Sorry"
End Sub

If Then Else的用法，如果多重条件，也可以用ElseIf, 在条件符合的时候直接退出程序。多重条件建议用Select Case结构
Sub GreetMe6()
If Time < 0.5 Then
MsgBox “Good Morning”
Else
If Time >= 0.5 And Time < 0.75 Then
MsgBox “Good Afternoon”
Else
If Time >= 0.75 Then
MsgBox “Good Evening”
End If
End If
End If
End Sub

Select Case的用法--测试了一下，非常好用！
Sub Discount3()
Dim Quantity As Variant
Dim Discount As Double
Quantity = InputBox(“Enter Quantity: “)
Select Case Quantity
Case “”
Exit Sub
Case 0 To 24
Discount = 0.1
Case 25 To 49
Discount = 0.15
Case 50 To 74
Discount = 0.2
Case Is >= 75
Discount = 0.25
End Select
MsgBox “Discount: “ & Discount
End Sub
简洁Select Case的例子：
Sub GreetUser2()
Select Case Weekday(Now)
Case 2, 3, 4, 5, 6
MsgBox “This is not the weekend”
Case Else
MsgBox “This is the weekend”
End Select
End Sub

Page 231

JunJun2013

Sub OpenFile

Dim enterdate As Date
Dim MyFile As String
Dim fname As String

enterdate = InputBox("Enter Date")
fname = Day(enterdate) & Month(enterdate) & Year(enterdate)
Const MyFolder = "K:\General Office\Costings\Weekly Costing\"
MyFile = MyFolder & fname & "*.xls"

Workbooks.Open FileName:=MyFile

End Sub

输入日期后，不能打开，事实上在指定目录下是有按日期命名的文件，比如1262014，msgbox测试显示fname的时候也是对的，不知道哪里出错？头晕~~
@windwing00 麻烦百忙之中看一下，谢谢！这个没技术含量，就是不知道哪个细节不对。

@TJLADY 能否也看一下？还有哪位大师？在GOOGLE上看半天，真是头晕呀。。。

尝试了简洁版本：
Sub test
sPath = "\General Office\Costings\Weekly Costing\"
sfile = InputBox("Please Enter Date", "Enter Date", Date)
sfile = Format(sfile, "ddmmyy") & ".xlsx"
Workbooks.Open Filename:=sPath & sfile
End Sub

成功！

愚夫

Excel功能确实很强大，但感觉很复杂；如果要做数据处理，我还是喜欢导入Access或者Database, 用SQL去实现， 然后用Excel来实现报表输出。

JunJun2013

doreadme 发表于 12-6-2014 16:07

                               
登录/注册后可看大图

Excel功能确实很强大，但感觉很复杂；如果要做数据处理，我还是喜欢导入Access或者Database, 用SQL去实现， ...

主要是针对每个同事都在用EXCEL的现状
不能要求他们都会使用ACCESS, 而要处理他们共享出来的信息，就得处理EXCEL的数据
ACCESS的SQL类似于EXCEL里的公式，是微观操作，VBA的优势是宏操作，针对跨文件
不过我才刚入门，欢迎你多提意见

愚夫

JunJun2013 发表于 12-6-2014 16:12

                               
登录/注册后可看大图

主要是针对每个同事都在用EXCEL的现状
不能要求他们都会使用ACCESS, 而要处理他们共享出来的信息，就得处 ...

你这个帖子很好，我大力支持。

JunJun2013

doreadme 发表于 12-6-2014 16:13

                               
登录/注册后可看大图

你这个帖子很好，我大力支持。

EXCEL的VBA学好后，打算再深造一下ACCESS（原来只会最简单的程序），WORD也可以应用宏。用熟了就觉得蛮好玩的，可以玩文字数字游戏

阿斯巴田

留下名，以后工作有难题，来这里请教。
别说我懒，实在是有些功能没用到，真没动力和兴趣。

小会计，用用VLOOKUP，IF，SORTING，FILTER差不多就够用了，有时也导入ACCESS，QUERY一下，再导出

JunJun2013

阿斯巴田 发表于 13-6-2014 17:04

                               
登录/注册后可看大图

留下名，以后工作有难题，来这里请教。
别说我懒，实在是有些功能没用到，真没动力和兴趣。

会计是最需要EXCEL/ACCESS的职业之一
理论上用公式和查询就能满足数据需求了，有些快速的功能能带来有意义的效果，比如有人等在旁边看你取数，或者在电话中，有些功能减少出错和重复厌烦感，增加一些乐趣
我强烈建议多应用宏功能
欢迎你来提建议，分享好点子，探讨难题，一起学习

JunJun2013

关于控件：
很多有灵气的用法，建议多多尝试

且说option control的用法，Form Controls 和ActiveX Controls,后者直接对应写CODE，前面也可以分配宏（右键操作），后者需要转换设计模式，但没有本质区别。

好处是前者对应Form Controls里面的group,可以后者把选择归为不同的组，操作的时候直接画框就行，然后可以做多个单项选择题
对于多选，似乎只有用CHECK BOX,还需要多研究，目前还没碰到用户需求。

关于日期公式

44楼的巧妙用法之外，还有：
DateAdd ( interval, number, date )
Value        Explanation
yyyy        Year
q        Quarter
m        Month
y        Day of the year
d        Day
w        Weekday
ww        Week
h        Hour
n        Minute
s        Second

http://www.techonthenet.com/excel/formulas/dateadd.php

非常实用！

另外，再次表示用录制宏可以完成各种基本操作的CODE, 完全不需要去记，最关键是理解需求，然后巧妙组合！

JunJun2013

左手食指点"Tab"键，拇指点"Alt"键，快速切换窗口。

还有很多键盘操作，包括Ctrl 和 F 键，（比如妇孺皆知的Ctrl +C Ctrl + V）, 熟悉了的确比鼠标快！！

mason00

MICHELLE07

有点担心微软的OFFICE的将来发展趋势
居然很多人都不用的，除了财务人员，其他人估计都不需要那些强大的功能

MICHELLE07

直接链接到网站摘取数据的code，非常实用，可以用于汇率，天气预报等。


Private Sub CommandButton1_Click()

Dim WS As Worksheet: Set WS = ActiveSheet
   
    Dim Req As New XMLHTTP

    Req.Open "GET", "http://themoneyconverter.com/rss-feed/AUD/rss.xml", False
   
    Req.send
   
    Dim Resp As New DOMDocument

    Resp.LoadXML Req.responseText
   
    Dim Item As IXMLDOMNode
    For Each Item In Resp.getElementsByTagName("item")
    If Item.SelectNodes("title")(0).Text = "USD/AUD" Then
        WS.Range("b1") = Item.SelectNodes("pubDate")(0).Text
        WS.Range("b2") = Item.SelectNodes("description")(0).Text
        
    End If
    Next Item
End Sub

huluhzl

进来学习下EXCEL表的使用方法。。。

JunJun2013

修复链接

注意利用name range（在formulas/define name) 的功能，然后可以管理  Formulas / Name Manager

http://www.itbdigital.com/tools- ... mp;Segment=The+Rest

语之玫瑰

Excel功能强大啊，不过在工作上用效率略低，家庭用特别好

JunJun2013

语之玫瑰 发表于 18-11-2014 12:03
Excel功能强大啊，不过在工作上用效率略低，家庭用特别好

你的工作性质不同，可能感觉不一样，对从事分析工作的人，EXCEL作为系统软件的补充，非常好用，既直观灵活又潜能十足。

家庭用？我家里没有OFFICE....一般家里就在云端写写文章，邮件，和数据有关的私事，都是在公司里完成，嘿嘿。。。

语之玫瑰

JunJun2013 发表于 18-11-2014 12:10
你的工作性质不同，可能感觉不一样，对从事分析工作的人，EXCEL非常适合，既直观灵活又潜能十足。

家 ...

我以前公司就用Excel的，神器啊！不过数据量一大速度就有点跟不上了