当前位置：首页 → 问答吧 → 程式转换一问

程式转换一问

时间：2014-05-29

来源：互联网

想请问如果将含VBA Excel File转换为其他程式执行(例如VB)，会唔会好复杂?

[ 本帖最后由 alee001 於 2014-5-10 10:55 PM 编辑 ]

作者: alee001 发布时间: 2014-05-29

引用:原帖由 alee001 於 2014-5-10 10:24 PM 发表
想请问如果将含VBA Excel File转换为其他程式执行(例如VB)，会唔会好复杂?

基本上无可能转换, 除左 dim xx as yyy , if then else 呢 d 句式相似外, 基乎要由头写过, 最麻烦系 vba 内的专用 object, 要找返相同功能的 dll 给 vb.net 用基本上无可能, 例如 sheet1.range(xx), vb.net 已经无呢样野。如果 vb.net 内有类似功能的 object /dll, 写法都同 vba 唔同, 不过点都好过由 0 开始。

作者: 烟民比食屎9更贱发布时间: 2014-05-29

引用:原帖由 alee001 於 2014-5-10 10:24 PM 发表
想请问如果将含VBA Excel File转换为其他程式执行(例如VB)，会唔会好复杂?

转 VB/ VB.NET 应好多野都用得返，你可以 google 一下 vb office automation excel
我 N 年前整过

d code 好似咁样的，你见同 excel vba 差唔多咁。

复制内容到剪贴板代码:Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim objApp As Excel.Application
Dim objBook As Excel._Workbook
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet As Excel._Worksheet
Dim objRange As Excel.Range

' Instantiate Excel and start a new workbook.
objApp = New Excel.Application()
objBooks = objApp.Workbooks
objBook = objBooks.Add
objSheets = objBook.Worksheets
objSheet = objSheets.Item(1)

objRange = objSheet.Range("A1")

'Set the range value.
objRange.Value(Excel.XlRangeValueDataType.xlRangeValueDefault) = "Hello, World!"

'Return control of Excel to the user.
objApp.Visible = True
objApp.UserControl = True
End Sub

作者: yuki~~ 发布时间: 2014-05-29

咁旧版VB系有个内置转换VBA功能?

作者: alee001 发布时间: 2014-05-29

引用:原帖由 alee001 於 2014-5-11 09:46 PM 发表
咁旧版VB系有个内置转换VBA功能?

除左 vb 2 个字相同外, 其他野 (包括写法) 都 8 成唔同!

作者: 烟民比食屎9更贱发布时间: 2014-05-29

引用:原帖由 yuki~~ 於 2014-5-11 06:44 AM 发表

转 VB/ VB.NET 应好多野都用得返，你可以 google 一下 vb office automation excel
我 N 年前整过

d code 好似咁样的，你见同 excel vba 差唔多咁。

Private Sub Button1_Click(ByVal sender ...

你呢个系 suppose 用户有装 Office, 但即使有装, d winform 都唔同用法。简单既可能得, 但一 d 复杂既, 个设计上已经唔同, vba 基本上唔系 oo, 但 vb.net 写法系 oo, 已经唔同左。加上 vba 写法较松, 而 vb.net 较严諽, 所以必定会出好多问题, 自动转换? 算把啦

作者: 烟民比食屎9更贱发布时间: 2014-05-29

多谢各位意见，本来谂著将VBA转做VB再修改成.NET版，但睇黎无望...

作者: alee001 发布时间: 2014-05-29

引用:原帖由 alee001 於 2014-5-12 03:07 PM 发表
多谢各位意见，本来谂著将VBA转做VB再修改成.NET版，但睇黎无望...

唔系转唔到，　系要花心机及时间。你比得起钱，　一定有人搞得掂！

作者: 烟民比食屎9更贱发布时间: 2014-05-29

Post 出嚟睇睇先

引用:原帖由 alee001 於 2014-5-10 22:24 发表
想请问如果将含VBA Excel File转换为其他程式执行(例如VB)，会唔会好复杂?

作者: a8d7e8 发布时间: 2014-05-29

成几百行又唔系几十行CODE，当中有又过百条Excel自定公式，成几十MB File，运行觉得好heavy所以先考虑转换...

作者: alee001 发布时间: 2014-05-29

你 post 啲特徵出嚟就够

每一种特别用法 post 一款就够, 重覆的不用

引用:原帖由 alee001 於 2014-5-13 13:39 发表
成几百行又唔系几十行CODE，当中有又过百条Excel自定公式，成几十MB File，运行觉得好heavy所以先考虑转换...

作者: a8d7e8 发布时间: 2014-05-29

其实File都系用VBA几种基本型态写Code(i.e. IF...else; For loop; UserForm)再加上Excel巢状公式组成，全部由以下5个step按钮同几个worksheet控制:
Date input
Date setting
Date test
Date chart
Date analysis
完成整个流程最少须时2hr视乎Data多少。一直想将file写成Add-Ins或执行程式，只系唔知点入手?

作者: alee001 发布时间: 2014-05-29

引用:原帖由 alee001 於 2014-5-13 11:23 PM 发表
其实File都系用VBA几种基本型态写Code(i.e. IF...else; For loop; UserForm)再加上Excel巢状公式组成，全部由以下5个step按钮同几个worksheet控制:
Date input
Date setting
Date test
Date chart
Date analysi ...

2hrs 去 run? 1 万条都唔洗咁耐，vba 无 multi-threading, 所以做野要一步接一步。vb.net 就可以，multi-threading 在多数情况下，比 single thread 快 n 倍。

作者: 烟民比食屎9更贱发布时间: 2014-05-29

excel vba慢有时系因为佢即时响screen跳嚟跳去做update，你disable佢update，等行完晒先一嘢refresh会快好多

作者: skww 发布时间: 2014-05-29

正确讲>30K data执行(least 3000row x avg 10 tick/row)for 2hr，唯有开多几个Excel file处理当系multi-threading。

作者: alee001 发布时间: 2014-05-29

as #14, skww said,

Have you put:

application.screenupdating = false

before you run your data?

turn it on when done. You will see the difference.

作者: stevie1 发布时间: 2014-05-29

呢招已经用咗...

作者: alee001 发布时间: 2014-05-29

咁师兄不如重新写过，最好唔好用excel喇，用csv/tab delimited嘅txt file，纯文字咁做3000+ rows应该唔会用好多时间啫

除非你每条row都有好多嘢要搞啦

作者: skww 发布时间: 2014-05-29

do the worksheets have a lot of formula?

作者: stevie1 发布时间: 2014-05-29

There are a lot of formule be structured for statement and result per row on sheet besides VBA step button.

作者: alee001 发布时间: 2014-05-29

Have you do profiling to find out the bottleneck?

Are you sure the bottleneck is the per statement execution overhead of VBA that cannot be further fine tuned? (That you hope converting it to another Language would speed it up)

引用:原帖由 alee001 於 2014-5-15 13:05 发表
There are a lot of formule be structured for statement and result per row on sheet besides VBA step button.

作者: a8d7e8 发布时间: 2014-05-29

Yes, input more data row will creat more formula per row which to cause spend more running time for VBA, I think it may be diffcult to change other lauguage with part of statement formula.

作者: alee001 发布时间: 2014-05-29

Which version of Excel are you using?

There seems to be a timer function in recent Excel (10 years ago, let say).

Something like setTimeout "funcToInvoke()", for sure the function name is fake here...

引用:原帖由 alee001 於 2014-5-15 13:33 发表
Yes, input more data row will creat more formula per row which to cause spend more running time for VBA, I think it may be diffcult to change other lauguage with part of statement formula.

作者: a8d7e8 发布时间: 2014-05-29

I used to Excel 2010, I had made optimization of code (eg. copy statement formule after looping to avoid calcuration on VBA running) but it seems a little improve to running time...

作者: alee001 发布时间: 2014-05-29

Oh you were the guy that asked............... constrained optimization problem .........

Without having access to a stereotype of your code or sample data, I can hardly see beyond the black box without inventing the black box myself first.

Probably if you can transform the operation into other equivalent forms, all of us here can contribute more to your problem.

Maybe you can leave out the critical parts, substitute with meaningless but time complexity equivalent operations.........

引用:原帖由 alee001 於 2014-5-15 14:33 发表
I used to Excel 2010, I had made optimization of code (eg. copy statement formule after looping to avoid calcuration on VBA running) but it seems a little improve to running time...

作者: a8d7e8 发布时间: 2014-05-29

If I want to find out tick from [t] to [t+n] that state will be changed on a tick[t+x], which method can more efficiency in case of using by looping or bisection?

作者: alee001 发布时间: 2014-05-29

Sorry my brain is malfunctioning in processing abstract states atm.

Do you have an example?

t1: state A
t2: state A
:
tx-1: state A
tx: state B
:
tn: state B
Is this correct? But how to evaluate the state? Is it a black box? Is it a random function or non-deterministic function.....

That means.....the following:
t1: state At2: state A:tx-1: state Atx: state B:tx+m: state Btx+m+1: state ANY other than B:tn: state ANY

引用:原帖由於 2014-5-15 16:10 发表 If I want to find out tick from [t] to [t+n] that state will be changed on a tick[t+x], which method can more efficiency in case of using by looping or bisection?

作者: a8d7e8 发布时间: 2014-05-29

Yes, state (state A, state B or state C)change on Excel statement formula cell per row depend on input tick [t] to [t+n], I want to find out state change tick [t+x] (i.e. state [t] <> state [t-1])

[ 本帖最后由 alee001 於 2014-5-15 04:46 PM 编辑 ]

作者: alee001 发布时间: 2014-05-29

Sorry no idea. Haven't been into securities. If u can point out some equivalent operations. (such as slow stochastic technical analysis)

引用:原帖由 alee001 於 2014-5-15 16:43 发表
Yes, state (state A, state B or state C)change on Excel statement formula cell per row depend on input tick [t] to [t+n], I want to find out state change tick [t+x] (i.e. state [t] state [t-1])

作者: a8d7e8 发布时间: 2014-05-29

i believe,

before your process starts,
Application.Calculation = xlCalculationManual
after done,
Application.Calculation = xlCalculationAutomatic
will improve the speed.

unless, your task need immediately calculation...

作者: stevie1 发布时间: 2014-05-29

or restore to the workbook's original calculation mode.

作者: stevie1 发布时间: 2014-05-29

Must be xlCalculationAutomatic because of formula cell and VBA are interactive.

作者: alee001 发布时间: 2014-05-29

I had wrote program find out changing tick with scan from low to high value by looping, I want to try re-write process by Bisection if which spend less time for running by looping on VBA, but I meet problem how to set mid-point value within between ticks. Anyone can give me advice? Thks.

作者: alee001 发布时间: 2014-05-29

引用:原帖由 alee001 於 2014-5-15 10:07 PM 发表
Must be xlCalculationAutomatic because of formula cell and VBA are interactive.

does your tick calculation relate to your vba task?

I doubt, you need to turn the auto-calculation always on, while putting values into the worksheets.
It could either, be better designed, or even, move most (if not all) of the formula inside worksheet to the vba code instead.

There is another trick.

Leave the Workbook in Manual Calculation mode, and just have those sheets calculate whenever they change.e.g. Sheets("Summary1").Calculate

作者: stevie1 发布时间: 2014-05-29

有啲excel function会慢啲，好似vlookup咁会不断update，可以research吓就搵到边啲function 慢啲

有啲dynamic way可以简约就简约，放入function去计，去简化一啲validation, 再output array 都可以speed up好多，

有啲情况系可以你spreadsheet度计完一部分，将佢copy paste变做value，再放formula计其他部分，咁样去track 嗰个calculation process都可以好快，但系就要花好多啲时间写

作者: honyin 发布时间: 2014-05-29

34楼#:将Excel所有公式系VBA内计算后才paste到worksheet系可以快少少，但就难写同出错会好难check，有啲公式用Excel系方更过用VBA，系公式互动影响下须要使用自动计算。

35楼#:你既提议我已经用紧，将全部计完公式转做value会系可以减少运算时间，但步骤又会多啲，所以最后冇乜点快，我只系部分咁做。

作者: alee001 发布时间: 2014-05-29

Just try if the state looks good. ie. There is only one changing tick within the range......but I wonder if it is the case.

If your data range contains high low high low high low, the states maybe changed from A to B to A to B.....etc

Sorry can't help.

引用:原帖由 alee001 於 2014-5-15 22:28 发表
I had wrote program find out changing tick with scan from low to high value by looping, I want to try re-write process by Bisection if which spend less time for running by looping on VBA, but I meet p ...

作者: a8d7e8 发布时间: 2014-05-29

I had excluded in case of multi-change on a data betw low and high according to trending, so generally all data only one way changing with two state same as your example 27#. Have you any idea about Bisection?

[ 本帖最后由 alee001 於 2014-5-16 02:34 PM 编辑 ]

作者: alee001 发布时间: 2014-05-29

Then it is easy.

t[i+0]....................t[i+0+n] t[i+a]..............t[i+a+n] ...... t[i+z].............t[i+z+n]
Assume you divide the data into m groups each of which is n length and each group can only have a single changing point, then it sufficiently satisfy the requirement of bi-section method that a single "root" or no root is within two points inclusively.

There are too many tutorial about bi-section method ........ so I dont' repeat it here.

引用:原帖由 alee001 於 2014-5-16 14:32 发表
I had excluded in case of multi-change on a data betw low and high according to trending, so generally all data only one way changing with two state same as your example 27#. Have you any idea about B ...

作者: a8d7e8 发布时间: 2014-05-29

Thks for your point. I had tried to find process of bisection basiclly from website, I can divide n group and check low to high each group to find out which one state will be changed then scan tick detailly, I have no idea only how to divide the group range by mid-value that should be ceiling on tick value? (i.e. tick[t+1]=tick[t]+delta)

作者: alee001 发布时间: 2014-05-29

Not quite understand.....but I try to demonstrate...

Assume your function f(x) is either -1 (low) or 1 (high) and f(a) < f(b).

Target: Find r such that f(a) <= f(r) < f(b) or f(a) < f(r) <= f(b).

while b-a > 1, loop
m = (a+b)/2
If f(a) < f(m), b = m
Else If f(m) < f(b), a = m
Else no root
end loop

Take a if you want the -1 side or b if you want the 1 side.

引用:原帖由 alee001 於 2014-5-16 15:41 发表
Thks for your point. I had tried to find process of bisection basiclly from website, I can divide n group and check low to high each group to find out which one state will be changed then scan tick de ...

作者: a8d7e8 发布时间: 2014-05-29

其实excel本身sorting都好快，可以加个index column， copy paste 去第二度sort，再copy paste 番个index去都好快

或者research吓advance sorting algorithm

作者: honyin 发布时间: 2014-05-29

My problem is m value is not term of tick value, as follow:
Low tick = 10.5
High tick = 11
delta = 0.02
mid-value = (10.5+11)/2 = 10.75
change tick = 10.74 next tick 10.76
Therefore
group 1 = 10.5~10.74
group 2 = 10.76~11
How to make m value ceiling to tick value?

作者: alee001 发布时间: 2014-05-29

um......maybe I don't really know what you're doing. Sorry I surrender.

引用:原帖由 alee001 於 2014-5-16 22:50 发表
My problem is m value is not term of tick value, as follow:
Low tick = 10.5
High tick = 11
delta = 0.02
mid-value = (10.5+11)/2 = 10.75
change tick = 10.74 next tick 10.76
Therefore
group 1 = ...

[ 本帖最后由 a8d7e8 於 2014-5-16 11:04 PM 编辑 ]

作者: a8d7e8 发布时间: 2014-05-29

多谢C兄意见，不过我啲data唔驶用sorting因为test period会随住时间连公式增加行数以变得VBA运算缓慢，所以test progress头个50%会快过之后成倍。

作者: alee001 发布时间: 2014-05-29