# Thread: Whats wrong with this function? (VBA Excel)

1. ## Whats wrong with this function? (VBA Excel)

Hi, I have made this function but error kept coming up on me. Please advise

Private Function calcKnockDown()
'set the errorIn variable:
inputForm.errorIn = "calcKnockDown"
'copied from ebola sheet 1
'each variable is set in the sheet 1

f6 = Abs((matThckness - tipThckness(leaf, endd)) / (auxLength(endd) - flatLength))
f9 = Abs((revThckness - tipThckness(leaf, endd)) / (revLength(endd) - paraLength))
d10 = tipLength + pi * (eyeDiam(endd) + tipThckness(leaf, endd)) + 0.25
g7 = revLength(endd) + paraLength
g8 = revLength(endd) + paraLength + paraLength

'calculating the deflection for each section
defl1 = flatLength ^ 2 / (2 * flatLength * flatLength)
defl2 = Abs((WorksheetFunction.Ln(flatthckness / auxThckness) - f6 * auxLength(endd) / flatthckness)) _
/ (f6 * f6)
defl4 = (g7 * WorksheetFunction.Ln(g7 / g8) + paraLength) / (ParaConst(endd) * ParaConst(endd))
defl5 = Abs((WorksheetFunction.Ln(tipThckness(leaf, endd) / revThckness) - f9 * revLength(endd) / _
tipThckness(leaf, endd)) / (f9 * f9))
defl6 = tipLength ^ 2 / (2 * tipThckness ^ 2)

'calculating the slope for each of the section
slope1 = flatLength / (flatthckness * flatthckness)
slope2 = Abs((1 / flatthckness - 1 / auxThckness) / f6)
slope4 = WorksheetFunction.Ln(g8 / g7) / (ParaConst(endd) ^ 2)
slope5 = Abs((1 / revThckness - 1 / tipThckness(leaf, endd)) / f9)
slope6 = tipLength / (tipThckness ^ 2)

'calculating def, thats what its called
def1 = defl1 + slope1 * (tipLength - flatLength)
def2 = defl2 + slope2 * (tipLength - auxLength(endd))
def3 = delf3 + slope3 * (tipLength - d7)
def4 = h8 + i8 * (tipLength - revLength(endd))
def5 = h9 + i9 * (tipLength - tipLength)
def6 = defl6

'calculate the knockdown
kdtemp = defl1 + def2 + def3 + def4 + def5 + def6

kd(leaf, endd) = kdtemp * 12 * kdtemp / Data.kdConst

End If

End Function

2. ## Re: Whats wrong with this function? (VBA Excel)

> I have made this function but error kept coming up on me

What error message?
Which line of code?

StuartR

3. ## Re: Whats wrong with this function? (VBA Excel)

it actually say error in the module,
error in the array (0,0)
I have no idea why it does that ?
I am pretty new in VBA

4. ## Re: Whats wrong with this function? (VBA Excel)

A belated welcome to Woody's Lounge!

It's impossible to say much about the code you posted because it refers to lots of items that are not defined in the code itself. I can't tell whether tipThckness is a function or an array, for example.
Could you post a copy of your workbook with proprietary details removed or altered? (Maximum file size 100 KB, zip if necessary)

5. ## Re: Whats wrong with this function? (VBA Excel)

the code is itself is pretty large. I was thinking about adding this function in to the original code. but as soon as i call it , it just went error.
tipthckness is an 2 dimensional array that calculate the tip thickness of the top (front and rear) and then the bottom(front and rear)
when the module starts, it goes thru the process of top (front) then top (rear ) then bottom(front) and lastly bottom(rear).
I do not know if i made my function right to calculate the flow.
thank you

6. ## Re: Whats wrong with this function? (VBA Excel)

I'm sorry, without seeing the workbook I cannot offer further assistance. I cannot analyze the code as posted since it refers to variables not defined in the code, so I have no idea what it means.

7. ## Re: Whats wrong with this function? (VBA Excel)

Hi Hans,
I will post the workbook for you. With all the infomation changed.
I will do it as soon as possible.
Sorry for the trouble

8. ## Re: Whats wrong with this function? (VBA Excel)

Hi Hans,
Here is the file that i attach for you. If you need additional infomation please ask me.
Thanks alot for helping

9. ## Re: Whats wrong with this function? (VBA Excel)

Look, this isn't going to work. You ask a question about Excel code, and you post a Word document with only a part of the code. But even if you posted a workbook with the full code, there's no way I'll be able to analyze it; that is far beyond the scope of Woody's Lounge.
If you have a specific question, you're welcome to ask it, but to say 'here are 550 lines of code, there's an error somewhere in it, please find it for me' is not realistic.

10. ## Re: Whats wrong with this function? (VBA Excel)

<hr>it actually say error in the module,
error in the array (0,0)<hr>
When this message appears, do you have a Debug button? If so, click the button and look at the highlighted line of code. Then open the Locals window and inspect the variables on which the code is operating to see whether your code makes an assumption about what is supposed to be there that is not actually true. If so, either fix your code or look at why the variables are not what you expect. Repeat until it works. Welcome to debugging. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

11. ## Re: Whats wrong with this function? (VBA Excel)

Sorry. I ll try to look at it and debug. I fresh in the debugging world
Thanks

12. ## Re: Whats wrong with this function? (VBA Excel)

thanks sir,
I know what's wrong with it now.
I have an array problem. I called a wrong variables on the array.
thanks sir for your tips.
Do you have any books to introduce that will be good for starters like me?

13. ## Re: Whats wrong with this function? (VBA Excel)

This recent post <post:=691,503>post 691,503</post:> contains some links to free resources on VBA programming.

If you want to learn Excel VBA in particular, take a look at Excel Books by John Walkenbach - he is a well-respected author and he has both introductory and advanced books about macros in Excel.

14. ## Re: Whats wrong with this function? (VBA Excel)

Hi Hans,
I have a minor problem.
Let say i have this eqn
a = b(column, row) + c ' b is an array
then
d = a^2
I break my code and toggle over it.
b and c has a value. but a and d is giving me a 0

Do you know whats wrong with the code?

thank you

15. ## Re: Whats wrong with this function? (VBA Excel)

The variables a and d will have the default value 0 (assuming they have been declared as numeric) until after the lines have been executed. You will have to single-step through the code (using F8) until you are on the line below the one that assigns a value to them.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•