I cannot make the sqr work in VBA:

Function test1() As Variant

test1 =

Application.WorksheetFunction.MMult(Application.WorksheetFunction.MMult(RegionR, CovarMatrix), Application.WorksheetFunction.Transpose(RegionR))

End Function

RegionR is Variant with 6 objects

CovarMatrix is Variant with 6 x 6 objects

Anyone know a shortcut?

Function test1() As Variant

test1 =

Application.WorksheetFunction.MMult(Application.WorksheetFunction.MMult(RegionR, CovarMatrix), Application.WorksheetFunction.Transpose(RegionR))

End Function

RegionR is Variant with 6 objects

CovarMatrix is Variant with 6 x 6 objects

Anyone know a shortcut?

- How do you calculate the 5th square root in MS Excel?
- Rsquared function in VBA
- Use of square brackets in vba excel 2003
- Square Root Spiral Function Graph...
- Perform the square root function in excel.
- Cube Root of a Negative Number fails in VBA?
- Multidimensional arrays in VBA
- VBA : square root of -25
- Square Root With Userform Textbox's
- Multiple linear regression in VBA
- Round Square Root
- Square root macro
- Choosing to develop in VBA - researching options
- LINEST in VBA - R2 Doesn't Match Chart Value!
- Do Until Loop in finding the Square Root
- Plotting a square root line of best fit
- #name error in VBA function-
- GOTO in vba
- Macro in VBA to Concatenate a row of X amount of cells into a single cell
- Sql query against a range in vba
- Formulas in VBA versus directly in a cell
- VBA code to copy data out of a cell for use in VBA
- Using Round function in VBA
- Is it possible to download file unknown from website(IE) in vba?

square root to the fourth power, etc. in MS Excel?

Thank you.

I am looking for some help with the r squared function in VBA

I have a userform with textboxes where X and Y values are entered.

What I am stuck with is how to get the values of the textboxes into the r squared argument.

Here is what I have tried so far.

Code:

Sub test() theys = UserForm1.TextBox1.Value ,UserForm1.TextBox2.Value,UserForm1.TextBox3.Value thexs = UserForm1.TextBox4.Value,UserForm1.TextBox5.Value,UserForm1.TextBox6.Value ans = Application.WorksheetFunction.RSq(theys, thexs) MsgBox (ans) End SubI just don't know how to get the Y's and X's values into the rsquared function correctly.

Thanks

compile error:

can't find project or library

What I'm doin here is going to my template sheet then getting the info from different cells and then later using it somewhere else. in place of using

range and selecting cell I am using square brackets to select a cell. it worked fine on my computer and was a lot less writing

here is a small part of my code

Sub SheetSetup() Sheets("Template").Select' go to template sheet NumberofRowsToSetup = [b15] + 8 '''' NumberofRowsToSetup = what ever number is in B15 on the sheet+8 Numberofdropdown = [b16] NumberofDateSetup = [a16] NumberofFieldSetup = [o16] Call ColunmWidth ......I'm new at excel an using square brackets in this way might be incorrect, but the thing I don't understand is it work awesome on the computer I created the program on

thanks for any help you give me

mattsgame

i was looking for vba code to graph the Square Root Spiral from Pythagorus in Excel...?

???

thank you.

Gr = ((1 - 2 / a) / (1 + x * sqr(2 / (a - 4)))) ^(1 / 3)

I did a watch on a and x, they are 191 and -21.

The error is " Run-time error '5' Invalid procedure call or argument "

Oddly, if I perform the SAME calc in the Worksheet it works FINE. It just fails in VBA?

Any other ... um ... hints?

k.

1. I already had a thought to send it back to the worksheet and then kuludging it back to the macro.

2. Also have implemented a -ve if statement check and multiplied by -1 if <0, then cube root that and return it to a -ve. Still a kludge though.

First post and I'm hoping I don't break a rule. I'm trying to wrap my head around the concept of multidimensional arrays. I figure their native structure could be one of two possibilities.

Example:

VB:Is this two one-dimensional arrays, having a total of 8 elements?4)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Or, is it one two-dimensional array, having a total of 16 elements?

*If you know the answer right off, you can save yourself from reading the rest*

I tried some of my own code to figure it out:

VB:The resulting count for n is 16 (square of 4), but I think it could be the result of the way I programmed the test. The reason I have a hard time accepting this result is because I liken it to physical dimensions. Take length times width and you've squared something, length times width times height and you've cubed it. I learned arrays in VBA can have up to SIXTY dimensions!! What does Excel do after the third dimension!? (head explode)ArrayCountTest() Dim Arr() As Integer Redim Arr(0 To 3, 0 To 3) Dim n As Integer n = 0 For x = 0 To 3 For xx = 0 To 3 Arr(x, xx) = n n = n + 1 Debug.Print "Dim1:" & x Debug.Print "Dim2:" & xx Debug.Print "Count:" & n Debug.Print "-" Next xx Next x End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

i am trying to use the square root formula to work out the top length of the picture on the userform from the numbers i have in the textbox's.

It is basically a 10x5 rectangle and i need to find the diagonal length

which should be 11.18 but it is returning 10

can anyone see what iam doin wrong

cheers

toe

I would like to run a multiple linear regression in vba. I have one dependent and three explanatory variables. I will have to use a macro of some kind, since I need to run too many regressions to do it manually. To simplify things a little bit:

- There will always be exactly three independent variables

- There are no missing values

- The data is allways numerical

I've already got four ranges defined: Yrange, X1range, X2range, X3range. I would like to take these ranges as input parameters for the regression model. The only two parameters I need are Sum Square for Regression (SSR) and the degrees of freedom. I understand that you can use excel's matrix formulas to calculate some of the input parameters, but one doesn't really get around vba. Does anybody know where I can find a (simple) source code allowing me to conduct a regression with three input parameters? Many thanks in advance

I am trying to find the square root of a number and then return it as a rounded number. The code below displays the square root of the InputBox number, example:

If I enter 24 in the InputBox, 4.898979 is displayed in the MsgBox. I would like to round it so that 5 is displayed. Any help with the code would be great.

Code:

Sub SQRoot() Do num = InputBox("Enter a number", "Square Root Calculator", "0") If num < 0 Then MsgBox "You can't take the square root of a negative number like " & _ num, vbOKOnly + vbExclamation ElseIf Len(num) = 0 Then Exit Do Else MsgBox "The square root of " & num & " is " & Sqr(num), vbOKOnly End If Loop End Sub

Below is the macro I have been working on and can't seem to get it, I think I am missing some components, not sure, not an expert, any help appreciated:

Sub Squareroot()

'

' Squareroot Macro

' Macro recorded 8/20/2010 by W, Ron

'

'

Sub sq()

Dim n As Integer

Dim j As Integer

Dim Z As Variant

'Count size of array

n = Range("x").Count

'Do loop

For j = 1 To n

Z = Range("x").Cells(j).Value

Range("y").Cells(j).Value = Z * Z

nextj

End Sub

I have been looking on the net for guidance as to how to progress an Excel spreadsheet program that I have developed. It's a program to track foods and symptoms which is intended to help people with diet related illnesses. There aren't any complex calculations but plenty of printouts and lots of areas to input daily data and produce graphs etc.. I orginally developed it all to help myself overcome my CFS and IBS and I'm now thinking about putting it together in such a way that others can easily benefit from it and hopefully make use of its facilities to help them find a way out of their health problems.

I'm trying to decide whether to develop it into a distributable application using VBA or to convert it into a stand alone .exe program. My concern now is that if I use VBA then people who may want to use it will have to have Excel ( an assumption on my part at the moment ) so that may restrict people who don't have/can't afford Office who may want to use it. Many CFS sufferers are out of work for a very long time.

An exe application gives it more flexibility ( plus cross platform capability ) but will certainly take me a lot more time to put together and no doubt will cost me a lot more too as I'm not at that level of programming and will need help. Also I can see that things may evolve and it will be easier to make mods in Excel/VBA so I am considering getting things together via this route at present in view of the flexibility and cost considerations.

I have looked on the net and seen third party applications which state that they will convert Excel workbooks in .asp / html/ java etc type web based applications but from what I've seen of the final product none of them looks too convincing in what they can do. I came across this site and thought that it was very helpful and I do like the idea of using VBA and maybe getting someone to help me put it together but then I felt that before making any decisions I would ask for opinions here about possible routes for getting something together which could easily be used by anyone with just basic computer skills.

I wondered if, having developed something in VBA, there was then a route in the future to translating the final product into a stand alone application without having to start from square one. I have done this in the distant past with a very expensive product which I used to convert a complex cashflow spreadsheet which I designed but I remember that the final product wasn't too good with graphs. This current spreadsheet of mine has a lot of graphs and also makes lots of use of things like comment boxes which Excel is good at and these I will need in the final form of the program.

I have seen distributed applications produced in VBA and some of them have looked very professional, well designed and easy to use and I very much like the idea of going this route but I need to do more research before making up my mind on this subject. From what I have seen I am inclined to go the VBA route, especially now that I have found this site and seen that I can get help and advice, but I appreciate that I need the input of experienced wisdom before jumping into a plan which means a lot of work and/or expense on my part.

My apologies if this topic has been covered elsewhere on the forum. Pointers to it would be appreciated if it has.

Any advice or pointers on this subject would be very much welcomed.

Kindest regards and very best wishes to you

Phillip

varRegResults = Application.WorksheetFunction.LinEst(Range(Cells(2, 4), Cells(2, 4).End(xlDown)), Range(Cells(2, 8), Cells(2, 8).End(xlDown)), False, True) varSlope = varRegResults(1, 1) varRSquared = varRegResults(3, 1) varError = varRegResults(2, 1) varF = varRegResults(4, 1)The slope matches perfectly, but the R value does not. Note that I did force the line through zero in the VBA and manually. I am absolutely certain that the data is identical in both cases. Using Watch and Locals, I can see that the R2 is just plain a different value! I'm using Excel 2003. It seems though, that there are some major problems with Excel's regression procedures. (For example, see http://www.daheiser.info/excel/main/section9.pdf).

Thoughts? Thanks in advance!

This one's a short explanation from wikipedia:

ScreenShot228.jpg

And here's my scratch code: I can't seem to make it work.

Sub Sqrt() Dim S As Integer Dim N As Integer Dim ctr As Integer S = InputBox("Enter Number") Do Until ctr = S ^ (1 / 2) N = S / 2 If ctr = (1 / 2) * (N + S / N) Then MsgBox (ctr) Else N = ctr End If Loop End SubWhat do I need to change? I also want to show the answer with a Message Box. Thank you!

I'm using excel 2007

Thanks in advance for any help

I have several functions I think i may have a problem with most of them. but they are written in such a way that

they depend on each other so that if the first returns an error the rest wont show anything.

The first is for calculating correction factor fc taken from Serth see code. I am stuck, see code.

VB:Any body with any idea.'Correction Factor calculations for the LMTD 'source Robert R Serth, heat Transfer principles and applications. 'For any number of shell side passes and any even number of tube side passes as follows 'LMTD is calculated as ' N is the nymber of shell side passes Function Calcfc(Tis As Double, Tos As Double, Tit As Double, Tot As Double, N1 As Double, N2 As Double) As Double 'Correction Factor calculations for the LMTD 'source Robert R Serth, heat Transfer principles and applications. 'For any number of shell side passes and any even number of tube side passes as follows ' N is the nymber of shell side passes 'R P and alpha are ratios used to calculate the correction factor Dim R As Double, P As Double, S As Double, alpha As Double, n As Double Dim num As Double, den As Double, F As Double 'the numerators and denominators of the functions to ease writing the functions 'Where Ta (Tis)= inlet temperature of the shell-side fluid 'Tb (Tos) = outlet temperature of the shell-fluid 'ta (Tit= inlet temperature of the tube side fluid 'tb(Tot) = oulet temperature of the tube side fluid R = (Tis - Tos) / (Tot - Tit) P = (Tot - Tit) / (Tis - Tit) n = N1 * N2 alpha = ((1 - R * P) / (1 - P)) ^ (1 / n) If R = 1 Then S = P / (n - (n - 1) * P) num = (S * 2 ^ 0.5) den = (1 - S) * WorksheetFunction.Ln((2 - S * (2 - 2 ^ 0.5)) / (2 - S * (2 + 2 ^ 0.5))) F = num / den Else alpha = ((1 - R * P) / (1 - P)) ^ (1 / n) S = (alpha - 1) / (alpha - R) num = ((R ^ 2 + 1) ^ 0.5) * WorksheetFunction.Ln((1 - S) / (1 - R * S)) den = (R - 1) * WorksheetFunction.Ln((2 - S * (R + 1 - (R ^ 2 + 1) ^ (1 / 2))) / (2 - S * (R + 1 + (R ^ 2 + 1) ^ (1 / 2)))) F = num / den End If Calcfc = F End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks

Hi all,

could you please explain, why GOTO should NOT be used in vba?

(almost newbie question)

could you please explain, why GOTO should NOT be used in vba?

(almost newbie question)

I am having an issue with trying to figure out how to program a specific macro in VBA using Excel 2007. This macro that I want to develope will concatenate a range (single rows only (example: select C20:X20)) of X (variable) amount of Cells into 1 single cell, preferably the cell preceeding the selected range (ie B20). The issue is that not all the cells in that range would be filled and each row has a different column filled in with text. Thusly, how do I program a macro that will allow me to select a range of cells, pick out only the cells filled with text, and concatenate those cells into a single cell just preceeding the range in the same row? Then from there i would be able to copy that macro and and paste it in an infinite number of rows below each row selecting it's own row range next to it and applying the concatenation (example concatenate C20:X20, copy and paste the macro to the next cell and it will automatically select and concatenate C21:X21... etc...). Thanks for taking the time in considering my post

thanks!

thank you.

This is something that confuses me quite a bit and I'm guessing there is a very simple explanation and thus cure to my headbanging. I've put formulas from cells into a VBA file before, so I'm at a loss on this on. Are there some formulas that need some extra syntax when put in VBA?

I have an array formula typed into a cell and it works, but when I take that formula and put it in my VBA file it doesn't work. I'm using Named ranges, but I changed it back to reference the cells directly and it didn't help.

The formula in cell AQ3 is:

=COUNT(IF(Angles>=112,IF(Angles

Had a quick look and couldn't find anything like this that has been answered already.

I would like to do the following.

Background.

We have approx 30 technical claims handlers who each use a spreadsheet to record their individual caseloads with various dates etc. All these spreadsheets live in a communual folder on a network drive.

To assist the team manager I want to build something that will analyse each persons data for purposes of monthly 1 to 1's etc. That in itself is a doddle.

Due to Head Office and their silly rules I cannot do the obvious and add a worksheet in (don't even go into the why and why nots) so I plan on a standalone spreadsheet to copy across an individuals data and work things out there. (Again a doddle)

The team managers are not exactly excel savvy, so the idea is to automate as much as possibly using VBA and macro. What I plan is a dropdown box - they select the persons name, click a button and hey presto it all happens. (99% of that I can do)

The problem

I envisage a vlookup off the name in the dropdown box, the second column would contain the filepath and filename of that persons spreadsheet. So if I picked Josh I would get s:filepathjosh.xls

What I don't know how to do in VBA is to say "take the contents of cell C1, and use that as the name of the spreadsheet to open

e.g. Workbooks.Open Filename:="S:filepathjosh.xls."

what code do i put in so it reads C1 as the link and puts it in.

I hope that makes some kind of sense.

Thanks

Lynton

I have found a couple of posts relating to rounding functions but I could not find one that answered my question, so here I go.

I'm working on a project currently and I need to use the "Round" function effectively. At the moment I have gotten syntax errors when I've tried to use it in my code. What I'm trying to achieve is to make the cell value a whole number without decimal places and where there are decimal places I wish to round it to the nearest whole number. To make it easier for you to see and me to explain I will included the actual VBA code. (I'll try and use the instructions on how to post code - it is my first time) so if it is wrong, just let me know gently, thanks. :

VB:The variables in RED are the values I would like to be able to have some control over. I have tried to use Round(.Cells(3, 15),0) but I haven't got the "right" answer or I've received an error. It is late in the evening and maybe too late, I'm too tired. I hoped someone might be able to explain how this works in VBA Code, I know how to use it in regular Excel usage, i.e. "=ROUND(Cell#,2 dec places)" it is easy no worries. But in code it seems to behave differently. Help would be appreciated.For j = 12 To 18 Step 3 For i = 2 To intRow '1) Calc number of wins [COLOR=#ff0000].Cells(i, j + 1)[/COLOR] = .Cells(i, j) * .Cells(i, j + 1) '2) Calc strike rate If .Cells(i, j) = 0 Then .Cells(i, j + 2) = 0 Else [COLOR=#ff0000] .Cells(i, j + 2)[/COLOR] = .Cells(i, j + 1) / .Cells(i, j) End If Next i Next j End WithIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

No luck finding an answer? You could always try Google.