Free Microsoft Excel 2013 Quick Reference

Square Root in VBA

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?


Post your answer or comment

comments powered by Disqus
How do you calculate the square root to the fifth or

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

Thank you.

Hi

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 Sub
I just don't know how to get the Y's and X's values into the rsquared function correctly.

Thanks

can someone help me. My code works great on my computer but when I bring it to another computer it gives me errors. I think it has to do with my use of square brackets to reference cells. the error that comes up is

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

Square Root Spiral Function Graph...

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

???

thank you.

How do I perform square root function in excel?

I'm stumped. I have a simple calculation in a Macro which results in a -ve number.

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.

Hi everyone,

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:
	
 4) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is this two one-dimensional arrays, having a total of 8 elements?
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:
	
 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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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)

how do i take the square root of -25...(with the engineering functions...?)

thank you.

hi

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

Hi everyone,
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

Hi All

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


I have a spreadsheet, column A1 is X, Column A2 is Y and under X in column A2,A3 are numbers 2,4,500. I want the numbers in column A to have the square root calculation in column B2,B3.......

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

Hi,

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

I have figured out how to get LINEST() to work in VBA, and to retrieve values from the array it creates. I am stumped though, for why the R Squared (a.k.a R^2, R2, coefficient of determination) in VBA doesn't match the R2 for when I do the exact same procedure manually in the Excel UI!

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!

Hello! I need a little help. I want to make a code that would use the Babylonian method of finding the square root

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 Sub
What do I need to change? I also want to show the answer with a Message Box. Thank you!

I have data recorded from an experiment which is of the form y proportional to the square root of x. I would like to plot a line of best fit to this information but am unsure of how to do this as the power option will not let me enter 0.5.

I'm using excel 2007

Thanks in advance for any help

I have been writing functions in vba for a template for designing heat exchangers. It returns #name errors when i try.
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:
	
 
 '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 Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any body with any idea.
Thanks

Hi all,

could you please explain, why GOTO should NOT be used in vba?
(almost newbie question)

Good Afternoon!
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!

i would like to have a sql query (in vba) go against a range in the activesheet and return values (at first just for viewing and later an update query to let the user update columns in a given row(s) based on criteria in other columns of that row(s), etc.

thank you.

Hi all,

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

Hi all,

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

Greetings,
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:
	
    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 With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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.

Is it possible to download file unknown from website(IE) in vba?


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