Working on other programs while VBA runs = bad?

[kbrownk]

I have VBA automating Excel work that includes some copying and pasting of rows and columns.

I decided to do some work on MS Word on the same PC and noticed that when I copied something it was sometimes not available for pasting a second later. I assume this is b/c VBA/Excel used a copy command in between. No big deal. However, is it possible that I could also have messed up what was pasted in Excel by using copying w/ Word? In general, could the VBA procedure be changed by anything I do in other programs?

BTW, I'm not so worried about the copying and pasting point I use as an example above, since the time b/t copying in pasting in my VBA should be nearly instantaneous. But I'm curious in general. Saving is also a concern...

Thanks,
k

 

[GlennUK]

Show your code.

 

[kbrownk]

Here's a specific example, but I use numerous macros so my question is really a general one:


Sub Outer()

Application.DisplayAlerts = False 'DISABLES ALERT PROMPT

With Application
.Iteration = True
.MaxIterations = 10
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

'Import swc file
Dim NameF(70)

For i = 1 To 68

NameF(i) = "TEXT;C:\Users\Kerry\Desktop\Excel\QuantBased_NoHeader\cons\NonRnd\NonRndFile" + Str(i) + ".swc"
Sheets("Input").Select
With ActiveSheet.QueryTables.Add(Connection:= _
NameF(i), Destination:=Range("$A$2"))
.Name = NameF(i)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

'Copy of swc to IBD
Columns("A:G").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Linearize").Select
Columns("B:H").Select
ActiveSheet.Paste
Calculate
SaveTo = "C:\Users\Kerry\Desktop\Excel\QuantBased_NoHeader\cons\NonRnd\NonRndFile" + Str(i) + ".xlsm"
ChDir "C:\Users\Kerry\Desktop\Excel\QuantBased_NoHeader\cons\NonRnd"
ActiveWorkbook.SaveAs Filename:=SaveTo, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

'LinNonRndSummary
Workbooks.Open Filename:="C:\Users\Kerry\Desktop\Excel\QuantBased_NoHeader\cons\NonRnd\LinNonRndSummary.xlsm"
Windows("NonRndFile" + Str(i) + ".xlsm").Activate
Sheets("summary").Select
Columns("A:A").Select
Selection.Copy
Windows("LinNonRndSummary.xlsm").Activate
Sheets("Dist").Select
Columns(i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("NonRndFile" + Str(i) + ".xlsm").Activate
Sheets("summary").Select
Rows(2).Select
Selection.Copy
Windows("LinNonRndSummary.xlsm").Activate
Sheets("Scalar").Select
Rows(1 + i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
SaveTo = "C:\Users\Kerry\Desktop\Excel\QuantBased_NoHeader\cons\NonRnd\LinNonRndSummary.xlsm"
ChDir "C:\Users\Kerry\Desktop\Excel\QuantBased_NoHeader\cons\NonRnd"
ActiveWorkbook.SaveAs Filename:=SaveTo, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Windows("LinNonRndSummary.xlsm").Activate
ActiveWorkbook.Close False

Next

End Sub

 

[wigi]

kbrownk

If you'd remove all those unneeded .Select's and .Activate's, you might end up with something that is ready to be debugged.

The more if you write "numerous macros", I don't think you should focus on the issue you have. First writing good coding, and then, if the issues still exist, look into these issues. Not the other way round.

Also, indenting code helps a lot, just as using

 tags does.

 

[kbrownk]

I don't think I'm trying to debug, which is why I didn't post any code in the 1st place.

I'm just asking, can actions taken while using MS Word (copying and pasting, etc.), while VBA runs a macro on MS Excel at the same time, affect the Excel process?

For example: I have VBA running constantly copying and pasting values in Excel for some math homework project. I want to also use my PC to work on my English homework while the math project runs. Will copying and pasting text in MS Word while I work on my English homework potentially cause the Excel VBA to accidentally copy my English homework text and paste that in Excel, even though it is a separate process running it's own thing.

It's a general question, I don't know how I'd show code for it, but in case I was missing why (I'm not the expert, obviously), I just posted exactly the code that made me think of it, rather then simplify it since I didn't know what you would be looking for.

Thanks,
Kerrio

 

[wigi]

Sure, Word and Excel will influence each other during these copy/paste actions. They use the Clipboard, so if it happens that Word puts something out there, Excel will paste that one.

 

[kbrownk]

Thanks, that's what I figured. Seems pretty important to know!

K