I'm writing a program to do some basic design for wood stud walls. The actual process really isn't important, but there is a part I am having problems with.

The process involves many different equations and I need to end up solving for the total load, P. However, when all the equations needed are combined together (needs to be <= 1.0), 'P' occurs multiple times and is nearly immpossible to isolate and solve for.
My next thought then is to just run a loop and plug in an initial guess for P (Pi) and compare the value to 1.0. After comparing it, the loop would then adjust it and calculate the new value and compare that to 1.0 and then adjust that, and so on.

However, when I run the code, it comes up with an error of "Type mismatch." I have delcared all variables in question and have removed the type from each variable thus making them all of type "variant." Yet, still I get the "Type mismatch."

I'm not sure what else to do, so please review the code below and let me know what you think I should be doing.

*I am definitely an amateur at VBA, I just started teaching myself last week, so be prepared for code that may be able to be simplified.

Thank you to all those that can help!

Public Sub SolveForP()

'Do loop will repeat until the difference of the given value (1.0) and the calculated value (X)
'is less than 0.01
fc1 = Pi / (d * b)
X = (fc1 / Fprimec1) ^ 2 + (fb / (Fb2(1 - (fc1 / FcE))))

Pdesign = Pi
Pi = Pi / X

Loop Until Abs(X - 1) <= 0.01

Worksheets("Main").Range("B21").Value = fc1
Worksheets("Main").Range("B32:C32").Value = Pdesign

End Sub


Please let me know if any code may be missing. I did not include the entire code (i.e. variable declarations, sheet formatting code, etc.)

Re: Problem with code: "Type mismatch" error

Joseph Dawson

I ran into something similar to your problem not so long ago. I was using a loop to change a parameter until a function was solved such that it equaled 12 +/- 0.01. When I had this problem one of two things was happening:

1. The counter I was using in the loop to change a variable was getting larger than what a variable declared as an Integer type can be. It does not look like you are using a counter, but one of your variables could be growing larger than the variable type you declared it to be. For example, if you declare one of your variables to be an Integer and its value ends up falling outside of -32,768 to 32,767 then you will get a type mismatch error.

2. Or one of the variables may not be able to go far enough out in decimal places to satisfy the Loop Until condition. For example, if you declare a variable as a Currency data type then it can only go out 4 decimal places. If the solution to the function you are trying to solve requires the variable going out 5 decimal places then you will get the type mismatch error when the loop reaches that threshold.

You could try declaring all of your variables as double or not declaring their types at all. You could also use a numerical method to to make your search more efficient and therefore have to go through less iterations to solve your equation. I was able to use bisection to make a seek function I wrote way way way more efficient. There is an example of the bisection method on the following page:

Re: Problem with code: "Type mismatch" error



Which line is coming up type mismatch Also can you post the declarations. Some times VBA will try to perfom calculations as integer values unless you construct your code to avoid this, so the particular line and the declaration of the variable on that line are important.