type mismatch on saving excel calculation mode via VB app
 

Well, I'm just plain stumped. I get type mismatch errors early on trying to
save the calculation mode of excel object and a different error when trying
to set it to manual:

Dim CalculationMode As Variant
Private StatusBarSetting As Boolean
Dim xl As Excel.Application
Dim Argv As Variant

Sub Main()
Dim n As Integer
Dim sArgv As String

Argv = Parse_Args(5)
sArgv = ""
For n = 1 To UBound(Argv)
sArgv = sArgv & Argv(n) & vbCrLf
Next n
MsgBox "Arguments found: " & vbCrLf & sArgv
Start_Excel
Quit_Excel
End Sub

Private Sub Start_Excel()
Set xl = CreateObject("Excel.Application")
If Err.Number < 0 Then End
Call SaveSettings
End Sub

Private Sub Quit_Excel()
RestoreSettings
xl.Quit
Set xl = Nothing
End Sub

Private Sub SaveSettings()
'On Error Resume Next
If xl Is Nothing Then
MsgBox "Internal error!!! xl is Nothing!"
End
End If
xl.Interactive = False
xl.AskToUpdateLinks = False
CalculationMode = xl.Calculation <<-- type mismatch error on this
statement but don't know why.
xl.Calculation = xlCalculationManual <<- error 1004 on this
statement, "Method 'Calculation' of object '_Application' failed and don't
know why
StatusBarSetting = xl.DisplayStatusBar
xl.DisplayAlerts = False
xl.Cursor = xlWait
xl.ScreenUpdating = False
xl.DisplayStatusBar = True
End Sub


Ken Shaffer

type mismatch on saving excel calculation mode via VB app
 

Try this manual test.

Open excel. Close any workbook that you see (even book1).

Then try tools|options. You'll see that's greyed out.

Calculationmode is one of those settings that needs an open workbook.

Private Sub Start_Excel()
Dim xlWkbk As Workbook
Set xl = CreateObject("Excel.Application")
If Err.Number < 0 Then Exit Sub
Set xlWkbk = xl.Workbooks.Add
Call SaveSettings
End Sub

Ken Shaffer wrote:

Well, I'm just plain stumped. I get type mismatch errors early on trying to
save the calculation mode of excel object and a different error when trying
to set it to manual:

Dim CalculationMode As Variant
Private StatusBarSetting As Boolean
Dim xl As Excel.Application
Dim Argv As Variant

Sub Main()
Dim n As Integer
Dim sArgv As String

Argv = Parse_Args(5)
sArgv = ""
For n = 1 To UBound(Argv)
sArgv = sArgv & Argv(n) & vbCrLf
Next n
MsgBox "Arguments found: " & vbCrLf & sArgv
Start_Excel
Quit_Excel
End Sub

Private Sub Start_Excel()
Set xl = CreateObject("Excel.Application")
If Err.Number < 0 Then End
Call SaveSettings
End Sub

Private Sub Quit_Excel()
RestoreSettings
xl.Quit
Set xl = Nothing
End Sub

Private Sub SaveSettings()
'On Error Resume Next
If xl Is Nothing Then
MsgBox "Internal error!!! xl is Nothing!"
End
End If
xl.Interactive = False
xl.AskToUpdateLinks = False
CalculationMode = xl.Calculation <<-- type mismatch error on this
statement but don't know why.
xl.Calculation = xlCalculationManual <<- error 1004 on this
statement, "Method 'Calculation' of object '_Application' failed and don't
know why
StatusBarSetting = xl.DisplayStatusBar
xl.DisplayAlerts = False
xl.Cursor = xlWait
xl.ScreenUpdating = False
xl.DisplayStatusBar = True
End Sub

Ken Shaffer

--

Dave Peterson

type mismatch on saving excel calculation mode via VB app
 

Well that explains a lot (always wondered why when I set automatic
calculation that other spreadsheets were manual when opened).

Now, why is Calculation property under Application instead of Workbook?

Ken

PS: cross-posted in programming after realizing that my question was more
programming-related.

"Dave Peterson" wrote in message
...
Try this manual test.

Open excel. Close any workbook that you see (even book1).

Then try tools|options. You'll see that's greyed out.

Calculationmode is one of those settings that needs an open workbook.

Private Sub Start_Excel()
Dim xlWkbk As Workbook
Set xl = CreateObject("Excel.Application")
If Err.Number < 0 Then Exit Sub
Set xlWkbk = xl.Workbooks.Add
Call SaveSettings
End Sub

type mismatch on saving excel calculation mode via VB app
 

You'll have to ask MS why they designed excel that way <bg.

And it looks like you multi-posted into the other groups. Crossposting means
you sent one message with lots of groups in the header. Multiposting means you
sent multiple messages.

The nice thing about crossposting is that anyone's reply to any of the
crossposts will be seen in all the newsgroups.

The bad thing about multiposting is that each is separate and distinct. You
have to check multiple spots and others may spend time answering a question that
was answered elsewhere.



Ken Shaffer wrote:

Well that explains a lot (always wondered why when I set automatic
calculation that other spreadsheets were manual when opened).

Now, why is Calculation property under Application instead of Workbook?

Ken

PS: cross-posted in programming after realizing that my question was more
programming-related.

"Dave Peterson" wrote in message
...
Try this manual test.

Open excel. Close any workbook that you see (even book1).

Then try tools|options. You'll see that's greyed out.

Calculationmode is one of those settings that needs an open workbook.

Private Sub Start_Excel()
Dim xlWkbk As Workbook
Set xl = CreateObject("Excel.Application")
If Err.Number < 0 Then Exit Sub
Set xlWkbk = xl.Workbooks.Add
Call SaveSettings
End Sub

--

Dave Peterson