Solved

Running a sql function in an Excel macro

Posted on 2012-03-12
6
132 Views
Last Modified: 2012-03-13
I have a scalar function I have written in SQL. It takes in 2 date times and spits out our business cycle time.

I'd like to be able to have a macro that will look at two columns of cells and run the function for each row that has data.

IE, I want columns A and B to have a bunch of rows with Date1 and Date2.
I want to run a macro that will put
myDateFunction(A1, B1) into column C1
myDateFunction(A2, B2) into column C2

etc...
0
Comment
Question by:Josh_Blade
  • 3
  • 3
6 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 37711345
If all you want to do is put that function in column C for each row with data try this.
LastRow =Range("A"& Rows.Count).End(xlUp).Row

Range("C1:C" & C).Formula = "=myDateFunction(A1,B1)"

Open in new window


If it's more complicated than that please post further details.
0
 

Author Comment

by:Josh_Blade
ID: 37711509
imnorie,
 Thanks for the quick response.
I entered the below into the macro
LastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("C1:C" & C).Formula = "=dbo.CycleTimeASIhmmss(A1,B1)"

Open in new window


When I run it, I get a

"Run-time error '1004':
Method 'Range' of object '_Global' failed." error message.
(in debug, it highlights the second line as causing the problem)


Here's the Sample data I used:
     A                B                C
01/13/12      02/01/12
01/27/12      02/03/12
01/16/12      02/08/12
01/16/12      02/08/12


Looking around the web a little bit, I just wanted to be clear that I'm wanting to use a scalar function from SQL. I've noticed a lot of other posts about this topic creating connection strings (this spreadsheet does already have a connection string set up in the data tab if that makes a difference).
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 37711824
Sorry, my mistake - I was in a rush.

It should read like this.
LastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("C1:C" & LastRow).Formula = "=dbo.CycleTimeASIhmmss(A1,B1)"

Open in new window


Anyway, that doesn't really matter now you've explained what you actually have.

What I posted was for an Excel UDF written in VBA but I did think there was more to it.

If what you actually have is a scalar function in SQL then you need another approach.

One way would be to create a UDF that connects to the database, runs the function using the cell values as parameters and returns the value.

The possible problem with that is that you would have to make the connection every time the UDF was used, so if you had the formula in C1:C100 that would be 100 separate connections.

Also, UDFs are usually calculated whenever a worksheet is calculated, so that would be 100 connections every time.

How often would you need to calculate the function?

Is it a one-off or will the values in the columns be changed or new values added?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Josh_Blade
ID: 37714409
imnorie,
 Thanks again for your informed response. After some more digging around, i've stumbled across a few similar UDFs that other people have written. I've tweaked it to what I think should work for me, but I keep getting

Comile error:
User-defined type not defined

and the debugger highlights the signature of my function.

Public Function CycleTime(FromDate As Date, ToDate As Date) As String
     
   Dim cn As New ADODB.Connection, cmd As New ADODB.Command, rs As ADODB.Recordset
      cn.Open "Provider=SQLOLEDB;Data Source=MYSERVER;Initial Catalog=MYCATALOG;Integrated Security=SSPI;"
      cmd.ActiveConnection = cn
      cmd.CommandText = "Select dbo.CycleTimeASIhmmss('" & FromDate & "','" & ToDate & "')"
      
      Set rs = cmd.Execute()
      rs.MoveFirst
      CycleTime = rs(0)
End Function

Open in new window


From what I'm reading there's some kind of reference missing (probably something to do with the ADO/sql stuff that's going on). I was hoping you might have an idea of what that would be. If not, thanks for all of your help!

Edit: After some testing, I was able to verify that it was this line causing the problem, so I need to find the right reference for it. I'll update when I find it

 
Dim cn As New ADODB.Connection, cmd As New ADODB.Command, rs As ADODB.Recordset

Open in new window

0
 

Author Closing Comment

by:Josh_Blade
ID: 37715051
I needed the Microsoft ActiveX Data Objects 2.0 Library
You can find it by going to Tools>References from the vba window.

Here's a link in another EE page I found:
link
0
 
LVL 33

Expert Comment

by:Norie
ID: 37715215
Josh

Glad you got it running.

How is it performance wise?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Formula or Macro to determine variance 17 75
FormulaArray VBA Issue 6 18
simple vba query 9 22
excel help 4 20
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now