By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,964 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,964 IT Pros & Developers. It's quick & easy.

Declare Variable

100+
P: 142
Sam,
After I finish those input text to visio VBA in new excel sheet1, I try to move everything including Sheet1, Module, and ThisWorkbook to a excel application already exist with many other modules. But after moved, those declared visio variable such as "Public vsApp as visio.application" or "Dim vsDocs as visio.documents" got highlight and come out a error message "Complie error: User-defined type not defined". But some declaration such as "Dim vFile as Variant" won't be selected. Do you know what is the reason for that?
Thanks
Apr 18 '07 #1
Share this Question
Share on Google+
13 Replies


SammyB
Expert 100+
P: 807
Sam,
After I finish those input text to visio VBA in new excel sheet1, I try to move everything including Sheet1, Module, and ThisWorkbook to a excel application already exist with many other modules. But after moved, those declared visio variable such as "Public vsApp as visio.application" or "Dim vsDocs as visio.documents" got highlight and come out a error message "Complie error: User-defined type not defined". But some declaration such as "Dim vFile as Variant" won't be selected. Do you know what is the reason for that?
Thanks
You need a reference to the Visio object library. Use the Tools, References menu in the VBA Editor to add it.
Apr 18 '07 #2

100+
P: 142
You need a reference to the Visio object library. Use the Tools, References menu in the VBA Editor to add it.
oh, yeah, how stupid I am!
Apr 18 '07 #3

SammyB
Expert 100+
P: 807
oh, yeah, how stupid I am!
But, you are getting smarter! Right? ;)
Apr 18 '07 #4

Expert 5K+
P: 8,434
oh, yeah, how stupid I am!
Just remember these very useful definitions...

Stupid: Doesn't know something I know.

Egghead: Knows something I don't.

:D
Apr 19 '07 #5

100+
P: 142
Just remember these very useful definitions...

Stupid: Doesn't know something I know.

Egghead: Knows something I don't.

:D
Result: Stupid and Egghead is antonym.

anyway. Help me out again.
When one excel application is runing, all other excel application will freeze and cannot be edit untill that application stop running. I try to figure out the excel VBA function for when one excel application is runing, other excel application can be edit. What is that function call?

Thanks
Apr 19 '07 #6

SammyB
Expert 100+
P: 807
>When one excel application is runing, all other excel application will freeze and cannot be edit untill that application stop running.

I think that you actually only have one excel application running and this excel application has multiple workbooks, so only one of these workbooks can be worked on.

To do what you want, you'll need to have a new Excel application for each workbook:
dim xlApp1 as new Excel.Application
set xlBook1 = xlApp1.Workbooks.Open("One.xls")
dim xlApp2 as new Excel.Application
set xlBook2 = xlApp2.Workbooks.Open("Two.xls")
Now, you can run a long process on One.xls and the user can manually work on Two.xls.
Apr 19 '07 #7

100+
P: 142
>When one excel application is runing, all other excel application will freeze and cannot be edit untill that application stop running.

I think that you actually only have one excel application running and this excel application has multiple workbooks, so only one of these workbooks can be worked on.

To do what you want, you'll need to have a new Excel application for each workbook:
dim xlApp1 as new Excel.Application
set xlBook1 = xlApp1.Workbooks.Open("One.xls")
dim xlApp2 as new Excel.Application
set xlBook2 = xlApp2.Workbooks.Open("Two.xls")
Now, you can run a long process on One.xls and the user can manually work on Two.xls.
Not sure how is that work. Let's saying in one workbook have the "Input Text to Visio" VBA function, and it take a long time to run, so I open and work on anohter workbook, which is not relate with previous workbook. How can that code above apply to the "Input Text To Visio" workbook.
Apr 19 '07 #8

100+
P: 142
How to set xlBook1 = xlApp1.Workbooks.Open("One.xls") if the workbook already open?
Apr 19 '07 #9

SammyB
Expert 100+
P: 807
How to set xlBook1 = xlApp1.Workbooks.Open("One.xls") if the workbook already open?
GetObject. Remember http://www.thescripts.com/forum/thread629094.html
Apr 19 '07 #10

Expert 5K+
P: 8,434
Joe, in VB6 you would use the DoEvents statement during your long-running process to allow Windows to handle other things (in the thread). Perhaps this will also work in Excel? Seems worth a look, anyway.
Apr 19 '07 #11

SammyB
Expert 100+
P: 807
Joe, in VB6 you would use the DoEvents statement during your long-running process to allow Windows to handle other things (in the thread). Perhaps this will also work in Excel? Seems worth a look, anyway.
Joe's using VBA, so he has DoEvents. It wouldn't hurt to put it in. DoEvents even survived in .NET, but it is Application.DoEvents()
Apr 19 '07 #12

100+
P: 142
wow, it work when I just add DoEvents before Next
Apr 20 '07 #13

Expert 5K+
P: 8,434
wow, it work when I just add DoEvents before Next
Glad to hear it! :)
Apr 21 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.