马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有账号？立即注册
×
本帖最后由 /db_自贡黄明儒_ 于 2016-12-28 21:34 编辑
把最近发的函数整理一下，欢迎大家测试其它函数是否可用
;;将十进制数 9 转换为 4 个字符的二进制数 (1001)
;;(Dec2Bin 9 4)=>"1001"
(defun Dec2Bin (Dec Bin / WorksheetFunction)
(or *excel*
(setq *excel* (vlax-get-or-create-object "excel.application"))
)
(setq WorksheetFunction (vlax-get *excel* 'WorksheetFunction))
(vlax-invoke WorksheetFunction 'Dec2Bin Dec Bin)
)
;;反cos
;;(Acos 0.86602540378443864676372317075294)=>0.523599弧度(30度)
(defun Acos (Num / WorksheetFunction)
(or *excel*
(setq *excel* (vlax-get-or-create-object "excel.application"))
)
(setq WorksheetFunction (vlax-get *excel* 'WorksheetFunction))
(vlax-invoke WorksheetFunction 'Acos Num)
)
;;反sin
;;(Asin 0.5)=>0.523599弧度(30度)
(defun Asin (Num / WorksheetFunction)
(or *excel*
(setq *excel* (vlax-get-or-create-object "excel.application"))
)
(setq WorksheetFunction (vlax-get *excel* 'WorksheetFunction))
(vlax-invoke WorksheetFunction 'Asin Num)
)
;;(WeekNum "2008年3月9日" 1)一年中的周数，一周开始于星期日，返回“11”
;;(WeekNum "2008年3月9日" 2)一年中的周数，一周开始于星期一，返回“10”
(defun WeekNum (express Num / WorksheetFunction)
(or *excel*
(setq *excel* (vlax-get-or-create-object "excel.application"))
)
(setq WorksheetFunction (vlax-get *excel* 'WorksheetFunction))
(vlax-invoke WorksheetFunction 'WeekNum express Num)
)
;;(Weekday "February 12, 1969")=>值为 4，因为February 12, 1969是星期四
(defun Weekday (express / WorksheetFunction)
(or *excel*
(setq *excel* (vlax-get-or-create-object "excel.application"))
)
(setq WorksheetFunction (vlax-get *excel* 'WorksheetFunction))
(vlax-invoke WorksheetFunction 'Weekday express)
)
;;求和
;;(Sum '(6 7 8))=>21
(defun Sum (express / WorksheetFunction)
(or *excel*
(setq *excel* (vlax-get-or-create-object "excel.application"))
)
(setq WorksheetFunction (vlax-get *excel* 'WorksheetFunction))
(apply 'vlax-invoke (cons WorksheetFunction (cons 'Sum express)))
)
;;第几个最大数
;;(Large '(6 7 8) 1)=>8第一个最大数
(defun Large (express Num / WorksheetFunction)
(or *excel*
(setq *excel* (vlax-get-or-create-object "excel.application"))
)
(setq WorksheetFunction (vlax-get *excel* 'WorksheetFunction))
(apply 'vlax-invoke (cons WorksheetFunction (cons 'Large (list express Num))))
)
;查找替换
;;(Replace "ABcDCF" "C" "阿")=>"ABcD阿F"
(defun Replace (express find rep)
(or *scr*
(setq *scr* (vlax-create-object "MSScriptControl.ScriptControl.1"))
)
(vlax-put *scr* "language" "vbs")
(vlax-invoke
*scr*
'ExecuteStatement
(strcat "x = Replace(" (VL-PRIN1-TO-STRING express) "," (VL-PRIN1-TO-STRING find) "," (VL-PRIN1-TO-STRING rep) ")")
)
(vlax-invoke *scr* 'eval "x")
)
;;输入对话框 By 819534890
;;(inputbox "信息" "题头" "默认值")
(defun inputbox (info title default)
(or *scr* (setq *scr* (vlax-create-object "MSScriptControl.ScriptControl.1")))
(vlax-put *scr* "language" "vbs")
(vlax-invoke
*scr*
'ExecuteStatement
(strcat "str=InputBox("
(vl-prin1-to-string info)
","
(vl-prin1-to-string title)
","
(vl-prin1-to-string default)
")"
)
)
(vlax-invoke *scr* 'eval "str")
)
;;提示信息框 By 819534890
;(msgbox "信息" 2 "题头")，不同的button值自己试试
(defun msgbox (info button title / SCR)
(or *scr* (setq *scr* (vlax-create-object "MSScriptControl.ScriptControl.1")))
(vlax-put *scr* "language" "vbs")
(vlax-invoke
*scr*
'ExecuteStatement
(strcat "str=MsgBox("
(vl-prin1-to-string info)
","
(vl-prin1-to-string button)
","
(vl-prin1-to-string title)
")"
)
)
(vlax-invoke *scr* 'eval "str")
)
;;平均值
;;(Average '(6 7 8))=>7.0
(defun Average (express / WorksheetFunction)
(or *excel*
(setq *excel* (vlax-get-or-create-object "excel.application"))
)
(setq WorksheetFunction (vlax-get *excel* 'WorksheetFunction))
(apply 'vlax-invoke (cons WorksheetFunction (cons 'Average express)))
)
;;list中有Round,而 RoundDown RoundUp Ceiling Ceiling_Precise是没有的
;;(RoundDown 3.1256 2)=>3.12
(defun RoundDown (express num / WorksheetFunction)
(or *excel*
(setq *excel* (vlax-get-or-create-object "excel.application"))
)
(setq WorksheetFunction (vlax-get *excel* 'WorksheetFunction))
(apply 'vlax-invoke (cons WorksheetFunction (list 'RoundDown express num)))
)
;;;(Text1 123 "正;负;零")=>"正"
;;;(Text1 123 "0000")=>"0123"格式化字串,不足前面补0
;;;其它功能请核查VBA format Text
(defun Text1 (express form / WorksheetFunction)
(setq *excel* (vlax-get-or-create-object "excel.application"))
(setq WorksheetFunction (vlax-get *excel* 'WorksheetFunction))
(apply 'vlax-invoke (cons WorksheetFunction (list 'Text express form)))
)
;;阶乘
;;(Fact 4)===>24.0
(defun Fact (num / WorksheetFunction)
(setq *excel* (vlax-get-or-create-object "excel.application"))
(setq WorksheetFunction (vlax-get *excel* 'WorksheetFunction))
(vlax-invoke WorksheetFunction 'Fact num)
)
;;转换
;;这个功能太强
;;(CONVERT1 1.0 "lbm" "kg")=> 将 1 磅转换为千克 (0.453592)
(defun CONVERT1 (number from_unit to_unit / WorksheetFunction)
(setq *excel* (vlax-get-or-create-object "excel.application"))
(setq WorksheetFunction (vlax-get *excel* 'WorksheetFunction))
(apply 'vlax-invoke (cons WorksheetFunction (list 'CONVERT number from_unit to_unit)))
)
;;;; WorksheetFunction: nil
;;;; Property values:
;;;; Application (RO) = #<VLA-OBJECT _Application 0eb937b4>
;;;; Creator (RO) = 1480803660
;;;; Parent (RO) = #<VLA-OBJECT _Application 0eb937b4>
;;;; Methods supported:
;;;; AccrInt (7)
;;;; AccrIntM (5)
;;;; Acos (1)
;;;; Acosh (1)
;;;; Aggregate (30)
;;;; AmorDegrc (7)
;;;; AmorLinc (7)
;;;; And (30)
;;;; Asc (1)
;;;; Asin (1)
;;;; Asinh (1)
;;;; Atan2 (2)
;;;; Atanh (1)
;;;; AveDev (30)
;;;; Average (30)
;;;; AverageIf (3)
;;;; AverageIfs (29)
;;;; BahtText (1)
;;;; BesselI (2)
;;;; BesselJ (2)
;;;; BesselK (2)
;;;; BesselY (2)
;;;; BetaDist (5)
;;;; BetaInv (5)
;;;; Beta_Dist (6)
;;;; Beta_Inv (5)
;;;; Bin2Dec (1)
;;;; Bin2Hex (2)
;;;; Bin2Oct (2)
;;;; BinomDist (4)
;;;; Binom_Dist (4)
;;;; Binom_Inv (3)
;;;; Ceiling (2)
;;;; Ceiling_Precise (2)
;;;; ChiDist (2)
;;;; ChiInv (2)
;;;; ChiSq_Dist (3)
;;;; ChiSq_Dist_RT (2)
;;;; ChiSq_Inv (2)
;;;; ChiSq_Inv_RT (2)
;;;; ChiSq_Test (2)
;;;; ChiTest (2)
;;;; Choose (30)
;;;; Clean (1)
;;;; Combin (2)
;;;; Complex (3)
;;;; Confidence (3)
;;;; Confidence_Norm (3)
;;;; Confidence_T (3)
;;;; Convert (3)
;;;; Correl (2)
;;;; Cosh (1)
;;;; Count (30)
;;;; CountA (30)
;;;; CountBlank (1)
;;;; CountIf (2)
;;;; CountIfs (30)
;;;; CoupDayBs (4)
;;;; CoupDays (4)
;;;; CoupDaysNc (4)
;;;; CoupNcd (4)
;;;; CoupNum (4)
;;;; CoupPcd (4)
;;;; Covar (2)
;;;; Covariance_P (2)
;;;; Covariance_S (2)
;;;; CritBinom (3)
;;;; CumIPmt (6)
;;;; CumPrinc (6)
;;;; DAverage (3)
;;;; Days360 (3)
;;;; Db (5)
;;;; Dbcs (1)
;;;; DCount (3)
;;;; DCountA (3)
;;;; Ddb (5)
;;;; Dec2Bin (2)
;;;; Dec2Hex (2)
;;;; Dec2Oct (2)
;;;; Degrees (1)
;;;; Delta (2)
;;;; DevSq (30)
;;;; DGet (3)
;;;; Disc (5)
;;;; DMax (3)
;;;; DMin (3)
;;;; Dollar (2)
;;;; DollarDe (2)
;;;; DollarFr (2)
;;;; DProduct (3)
;;;; DStDev (3)
;;;; DStDevP (3)
;;;; DSum (3)
;;;; Duration (6)
;;;; DVar (3)
;;;; DVarP (3)
;;;; EDate (2)
;;;; Effect (2)
;;;; EoMonth (2)
;;;; Erf (2)
;;;; ErfC (1)
;;;; ErfC_Precise (1)
;;;; Erf_Precise (1)
;;;; Even (1)
;;;; ExponDist (3)
;;;; Expon_Dist (3)
;;;; Fact (1)
;;;; FactDouble (1)
;;;; FDist (3)
;;;; Find (3)
;;;; FindB (3)
;;;; FInv (3)
;;;; Fisher (1)
;;;; FisherInv (1)
;;;; Fixed (3)
;;;; Floor (2)
;;;; Floor_Precise (2)
;;;; Forecast (3)
;;;; Frequency (2)
;;;; FTest (2)
;;;; Fv (5)
;;;; FVSchedule (2)
;;;; F_Dist (4)
;;;; F_Dist_RT (3)
;;;; F_Inv (3)
;;;; F_Inv_RT (3)
;;;; F_Test (2)
;;;; GammaDist (4)
;;;; GammaInv (3)
;;;; GammaLn (1)
;;;; GammaLn_Precise (1)
;;;; Gamma_Dist (4)
;;;; Gamma_Inv (3)
;;;; Gcd (30)
;;;; GeoMean (30)
;;;; GeStep (2)
;;;; Growth (4)
;;;; HarMean (30)
;;;; Hex2Bin (2)
;;;; Hex2Dec (1)
;;;; Hex2Oct (2)
;;;; HLookup (4)
;;;; HypGeomDist (4)
;;;; HypGeom_Dist (5)
;;;; IfError (2)
;;;; ImAbs (1)
;;;; Imaginary (1)
;;;; ImArgument (1)
;;;; ImConjugate (1)
;;;; ImCos (1)
;;;; ImDiv (2)
;;;; ImExp (1)
;;;; ImLn (1)
;;;; ImLog10 (1)
;;;; ImLog2 (1)
;;;; ImPower (2)
;;;; ImProduct (30)
;;;; ImReal (1)
;;;; ImSin (1)
;;;; ImSqrt (1)
;;;; ImSub (2)
;;;; ImSum (30)
;;;; Index (4)
;;;; Intercept (2)
;;;; IntRate (5)
;;;; Ipmt (6)
;;;; Irr (2)
;;;; IsErr (1)
;;;; IsError (1)
;;;; IsEven (1)
;;;; IsLogical (1)
;;;; IsNA (1)
;;;; IsNonText (1)
;;;; IsNumber (1)
;;;; IsOdd (1)
;;;; ISO_Ceiling (2)
;;;; Ispmt (4)
;;;; IsText (1)
;;;; Kurt (30)
;;;; Large (2)
;;;; Lcm (30)
;;;; LinEst (4)
;;;; Ln (1)
;;;; Log (2)
;;;; Log10 (1)
;;;; LogEst (4)
;;;; LogInv (3)
;;;; LogNormDist (3)
;;;; LogNorm_Dist (4)
;;;; LogNorm_Inv (3)
;;;; Lookup (3)
;;;; Match (3)
;;;; Max (30)
;;;; MDeterm (1)
;;;; MDuration (6)
;;;; Median (30)
;;;; Min (30)
;;;; MInverse (1)
;;;; MIrr (3)
;;;; MMult (2)
;;;; Mode (30)
;;;; Mode_Mult (30)
;;;; Mode_Sngl (30)
;;;; MRound (2)
;;;; MultiNomial (30)
;;;; NegBinomDist (3)
;;;; NegBinom_Dist (4)
;;;; NetworkDays (3)
;;;; NetworkDays_Intl (4)
;;;; Nominal (2)
;;;; NormDist (4)
;;;; NormInv (3)
;;;; NormSDist (1)
;;;; NormSInv (1)
;;;; Norm_Dist (4)
;;;; Norm_Inv (3)
;;;; Norm_S_Dist (2)
;;;; Norm_S_Inv (1)
;;;; NPer (5)
;;;; Npv (30)
;;;; Oct2Bin (2)
;;;; Oct2Dec (1)
;;;; Oct2Hex (2)
;;;; Odd (1)
;;;; OddFPrice (9)
;;;; OddFYield (9)
;;;; OddLPrice (8)
;;;; OddLYield (8)
;;;; Or (30)
;;;; Pearson (2)
;;;; Percentile (2)
;;;; Percentile_Exc (2)
;;;; Percentile_Inc (2)
;;;; PercentRank (3)
;;;; PercentRank_Exc (3)
;;;; PercentRank_Inc (3)
;;;; Permut (2)
;;;; Phonetic (1)
;;;; Pi ()
;;;; Pmt (5)
;;;; Poisson (3)
;;;; Poisson_Dist (3)
;;;; Power (2)
;;;; Ppmt (6)
;;;; Price (7)
;;;; PriceDisc (5)
;;;; PriceMat (6)
;;;; Prob (4)
;;;; Product (30)
;;;; Proper (1)
;;;; Pv (5)
;;;; Quartile (2)
;;;; Quartile_Exc (2)
;;;; Quartile_Inc (2)
;;;; Quotient (2)
;;;; Radians (1)
;;;; RandBetween (2)
;;;; Rank (3)
;;;; Rank_Avg (3)
;;;; Rank_Eq (3)
;;;; Rate (6)
;;;; Received (5)
;;;; Replace (4)
;;;; ReplaceB (4)
;;;; Rept (2)
;;;; Roman (2)
;;;; Round (2)
;;;; RoundDown (2)
;;;; RoundUp (2)
;;;; RSq (2)
;;;; RTD (30)
;;;; Search (3)
;;;; SearchB (3)
;;;; SeriesSum (4)
;;;; Sinh (1)
;;;; Skew (30)
;;;; Sln (3)
;;;; Slope (2)
;;;; Small (2)
;;;; SqrtPi (1)
;;;; Standardize (3)
;;;; StDev (30)
;;;; StDevP (30)
;;;; StDev_P (30)
;;;; StDev_S (30)
;;;; StEyx (2)
;;;; Substitute (4)
;;;; Subtotal (30)
;;;; Sum (30)
;;;; SumIf (3)
;;;; SumIfs (29)
;;;; SumProduct (30)
;;;; SumSq (30)
;;;; SumX2MY2 (2)
;;;; SumX2PY2 (2)
;;;; SumXMY2 (2)
;;;; Syd (4)
;;;; Tanh (1)
;;;; TBillEq (3)
;;;; TBillPrice (3)
;;;; TBillYield (3)
;;;; TDist (3)
;;;; Text (2)
;;;; TInv (2)
;;;; Transpose (1)
;;;; Trend (4)
;;;; Trim (1)
;;;; TrimMean (2)
;;;; TTest (4)
;;;; T_Dist (3)
;;;; T_Dist_2T (2)
;;;; T_Dist_RT (2)
;;;; T_Inv (2)
;;;; T_Inv_2T (2)
;;;; T_Test (4)
;;;; USDollar (2)
;;;; Var (30)
;;;; VarP (30)
;;;; Var_P (30)
;;;; Var_S (30)
;;;; Vdb (7)
;;;; VLookup (4)
;;;; Weekday (2)
;;;; WeekNum (2)
;;;; Weibull (4)
;;;; Weibull_Dist (4)
;;;; WorkDay (3)
;;;; WorkDay_Intl (4)
;;;; Xirr (3)
;;;; Xnpv (2)
;;;; YearFrac (3)
;;;; YieldDisc (5)
;;;; YieldMat (6)
;;;; ZTest (3)
;;;; Z_Test (3)
---------------------------
又花了一个下午，测试了更多可用的函数，搞了个压缩包收点辛苦费，换酒喝
| 
如果你在论坛求助问题，并且已经从坛友或者管理的回复中解决了问题，请把帖子标题加上【已解决】；
如何回报帮助你解决问题的坛友，一个好办法就是给对方加【D豆】，加分不会扣除自己的积分，做一个热心并受欢迎的人！
|申请友链|Archiver|手机版|小黑屋|辽公网安备|晓东CAD家园
( 辽ICP备15016793号 )
窥视卡
雷达卡
已领礼包: 
财富等级: 
发表于 2016-12-23 13:04:07
提升卡
置顶卡
沉默卡
变色卡
千斤顶
