Ignore messages by a program due to cnx to excel via macro

[Solution Searcher]

Hi guys

I am writing excel macro to create a table for young's modulus of materials. Just briefly describing what is meant to be done, connecting a table with 175000 row (rows contains materials e.x. metals ... etc) with different ID's some times similar, temperatures and norms. What I am up to do is writing macro in excel to connect this table to a material server program and search for young's modulus depending on temp. and ID of material.

I wrote a code which is working as I want but for some materials there is no available young's Modulus for some temperatures and then I am receiving message from the material server program which is saying not available and I should then press OK but this will be hard since I am dealing with 175000 different material so can any one help me with a code which can prevent that.

So I am receiving message not from excel but from the material server program and I want a code which will choose automatically OK and then I don't need to press OK my self every time.

Again thanks a lot.

My code is as following:
some words may not be clear because it is German

Dim Material As New LMaterial

Public Sub main()


Dim Ra As Range
Dim id As Integer
Dim N As Integer
Dim Emod As Single
Dim Alpha As Single

Set Ra = ActiveSheet.Range("A1")
With Material
N = 1
Do
id = CInt(Ra.Offset(N, 0).Value)
.Temperatur = CInt(Ra.Offset(N, 14).Value)

If .Temperatur < 20 Then

.Temperatur = 20

End If



.Suchtext = Str$(id)
Call .SearchAndCheck
Emod = .EModul(False)

'the problem shows up some times at this point when I debug
Alpha = .Alpha(False)
If Emod > 0! Then
EmodH = .EModul(True)
Ra.Offset(N, 15).Value = EmodH
'expT = .a

End If



N = N + 1
Loop While id > 0



End With


End Sub

 

[whitoulias]

Add this in the begging of your code

Application.DisplayAlerts  = False

I hope it works

 

[Solution Searcher]

Thanks whitoulias for the help but it didn't work still messages pops up from the material server program.

 

[obiron]

That looks like it is probably a problem with the Materials class.

.Alpha(param) is a method of the class and the sub for the method is throwing an error. If you have access to the class, you can try to debug it, otherwise you may need to put in an On Error Goto Handler: just before the code and On Error Goto 0 afterwards.

What should happen if the Materials class can't return a valid value for .Alpha? One thing that VBA CAN'T do is handle and error and then return directly to the error point.

 

[Solution Searcher]

Hi obirion
I tried it it is saying Compile error label not defined

Can you please edit my written code and show it to me.

Thanks a lot.

That looks like it is probably a problem with the Materials class.

.Alpha(param) is a method of the class and the sub for the method is throwing an error. If you have access to the class, you can try to debug it, otherwise you may need to put in an On Error Goto Handler: just before the code and On Error Goto 0 afterwards.

What should happen if the Materials class can't return a valid value for .Alpha? One thing that VBA CAN'T do is handle and error and then return directly to the error point.

 

[obiron]

google (or search this board) for On Error processing.

If you put in the code

sub mysub()

.. do whatever you need to do

On Error Goto MyErrorHandler:
..  do some stuff if there is no error
On Error Goto 0 '  cancels the error handler and all future errors will just proceed to the next line of code


myErrorHandler:
... Do the things which need to be done in the error handler (e.g.) turn screen updating, display alerts and autocalcuation back on; throw an error message etc..

end sub

Error handling in VBA is not great. You can't test for different errors and send the code to a different error handler; you have to go to the error handler and then test for the type of error. you can't resume where you left off and you can't choose to ignore certain errors. Use GoTo 0 with caution as it will ignore (almost) all errors.

 

[Solution Searcher]

Sorry to say it didn't work.


Thanks