Developer.Blog();

When working in Visual FoxPro, functions are procedures are almost the same thing. Look at the following code:

FUNCTION DoSomething
LPARAMETERS Param1, Param2
? “Param 1= ” + Param1
? “Param 2= ” + Param2

PROCEDURE DoSomethingElse
LPARAMETERS Param1, Param2
? “Param 1= ” + Param1
? “Param 2= ” + Param2

The function and procedure are actually identical. The difference to determine whether you have a function or procedure is not how they are defined, but how they are called. You can even call them the same way.

DO DoSomething WITH “Hello”, “Procedure”

DoSomething(“Hello”, “Procedure”)

You’ll notice that DoSomething was defined as a procedure, but we can call it as either a procedure or a function. We can do the same thing with DoSomethingElse.

The difference is how the parameters are passed. When called as a procedure, the parameters are passed by reference. When called as a function, the parameters are passed by value. You can change how they�re passed in a function with SET UDFPARMS TO REFERENCE but you can�t change how parameters are passed when calling the routine as a procedure.

So, which should you use? I think you should always call the routine as a function because the routine cannot accidently change the value of the parameter. This is the kind of nasty side effect that can be difficult to track down.

Do you need to create an ActiveX control for you VFP app? Are you adding functionality to your VFP app though .Net? Are you migrating your VFP app to .Net, but can’t move the entire app at one time? The Forms Interop Toolkit was developed to assist you in all these scenarios. I’ve just published a new paper, Integrating .Net Winforms with Visual FoxPro that show how to use the Toolkit to do all this. As an added bonus, the paper also shows how to COM-enable a WPF control and use it on a VFP form.

I recently had a need to document the schema of a SQL Server database. The requirement was to have one worksheet per table and match the formatting done on similar project. The database I actually had to document contains over 300 tables. I worked up some Visual FoxPro code to do this automatically.

* Document SQL tables into Excel

LOCAL oXl AS Excel.Application
LOCAL oSheet AS EXCEL.Sheets
LOCAL oActiveSheet AS EXCEL.Worksheet


oXl = CREATEOBJECT("Excel.Application")
oXl.Visible = .T.
oWkb = oXl.Workbooks.Add()
oActiveSheet = NULL

lnHandle = SQLSTRINGCONNECT("Driver=SQL Native Client;Server=.;database=Northwind;Trusted_Connection=yes")
SQLEXEC(lnHandle, "Exec sp_tables", "cTables")
SELECT cTables
INDEX ON Table_Type TAG TableType DESCENDING
SCAN FOR Table_Type = "TABLE"
  oSheet = oXl.Sheets.Add()
  loActiveSheet = oXl.ActiveSheet
 
  SQLEXEC(lnHandle, "Exec sp_columns '" + ALLTRIM(cTables.Table_Name)
    +  "'", "cColumns")

  * Add the top two rows with table information after adding the column stuff
  * so that the columns will autosize properly
  XLAddColumn(loActiveSheet, "cColumns")
  loActiveSheet = XLAddTable(loActiveSheet, cTables.Table_Name)
ENDSCAN

SQLDISCONNECT(lnHandle)
oSheet = NULL
oWkb = NULL
oXl = NULL

*********************************

FUNCTION XLAddTable(toActiveSheet, tcTableName)
  LOCAL lcTable
  LOCAL toActiveSheet AS EXCEL.Worksheet
 
  lcTable = ALLTRIM(tcTableName)
  * Handle the sheet title (table name)
  toActiveSheet.Name = lcTable
  toActiveSheet.Range("A1") = ALLTRIM(tcTableName) + " Table"
  toActiveSheet.Range("A1").Font.Name = "Arial"
  toActiveSheet.Range("A1").Font.Size = 14
  toActiveSheet.Range("A1", "F1").Interior.Color = 12632256
  toActiveSheet.Rows(1).RowHeight = 24.75
  toActiveSheet.Range("A1:F1").Borders(7).Color = RGB(0, 0, 0)
  toActiveSheet.Range("A1:F1").Borders(8).Color = RGB(0, 0, 0)
  toActiveSheet.Range("A1:F1").Borders(9).Color = RGB(0, 0, 0)
  toActiveSheet.Range("A1:F1").Borders(10).Color = RGB(0, 0, 0)

  * Column Titles
  toActiveSheet.Range("A2") = "Column Name"
  toActiveSheet.Range("A2").Borders(7).Color = RGB(0, 0, 0)
  toActiveSheet.Range("A2").Borders(8).Color = RGB(0, 0, 0)
  toActiveSheet.Range("A2").Borders(9).Color = RGB(0, 0, 0)
  toActiveSheet.Range("A2").Borders(10).Color = RGB(0, 0, 0)
 
  * Data type
  toActiveSheet.Range("B2") = "Data Type"
  toActiveSheet.Range("B2").Borders(7).Color = RGB(0, 0, 0)
  toActiveSheet.Range("B2").Borders(8).Color = RGB(0, 0, 0)
  toActiveSheet.Range("B2").Borders(9).Color = RGB(0, 0, 0)
  toActiveSheet.Range("B2").Borders(10).Color = RGB(0, 0, 0)

  * Precision
  toActiveSheet.Range("C2") = "Precision"
  toActiveSheet.Range("C2").Borders(7).Color = RGB(0, 0, 0)
  toActiveSheet.Range("C2").Borders(8).Color = RGB(0, 0, 0)
  toActiveSheet.Range("C2").Borders(9).Color = RGB(0, 0, 0)
  toActiveSheet.Range("C2").Borders(10).Color = RGB(0, 0, 0)

  * Length
  toActiveSheet.Range("D2") = "Length"
  toActiveSheet.Range("D2").Borders(7).Color = RGB(0, 0, 0)
  toActiveSheet.Range("D2").Borders(8).Color = RGB(0, 0, 0)
  toActiveSheet.Range("D2").Borders(9).Color = RGB(0, 0, 0)
  toActiveSheet.Range("D2").Borders(10).Color = RGB(0, 0, 0)

  * Scale
  toActiveSheet.Range("E2") = "Scale"
  toActiveSheet.Range("E2").Borders(7).Color = RGB(0, 0, 0)
  toActiveSheet.Range("E2").Borders(8).Color = RGB(0, 0, 0)
  toActiveSheet.Range("E2").Borders(9).Color = RGB(0, 0, 0)
  toActiveSheet.Range("E2").Borders(10).Color = RGB(0, 0, 0)

  * Comments
  toActiveSheet.Range("F2") = "Comments"
  toActiveSheet.Range("F2").Borders(7).Color = RGB(0, 0, 0)
  toActiveSheet.Range("F2").Borders(8).Color = RGB(0, 0, 0)
  toActiveSheet.Range("F2").Borders(9).Color = RGB(0, 0, 0)
  toActiveSheet.Range("F2").Borders(10).Color = RGB(0, 0, 0)

  * Format the cells
  toActiveSheet.Range("A2", "F2").Font.Bold = .T.
  toActiveSheet.Range("A2:F2").VerticalAlignment = 2
  toActiveSheet.Range("A2", "F2").Interior.Color = 8454143
  toActiveSheet.Rows(2).RowHeight = 26.25

  toActiveSheet.Columns("B").Autofit()
  toActiveSheet.Columns("C").Autofit()
  toActiveSheet.Columns("D").Autofit()
  toActiveSheet.Columns("E").Autofit()
  toActiveSheet.Columns("F").Autofit()
  
  RETURN loActiveSheet
ENDFUNC

*********************************

FUNCTION XLAddColumn(toSheet, tcColCursor)
  LOCAL lnWorkArea
  LOCAL toSheet AS EXCEL.Worksheet
 
  lnWorkArea = SELECT()
  tcCell = ""

  SELECT (tcColCursor)
  SCAN
    * Column name
    tcCell = "A" + ALLTRIM(STR(RECNO() + 2))
    toSheet.Range(tcCell) = ALLTRIM(Column_Name)
    toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)

    * Data Type
    tcCell = "B" + ALLTRIM(STR(RECNO() + 2))
    toSheet.Range(tcCell) = ALLTRIM(Type_Name)
    toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)
  
    * Precision
    tcCell = "C" + ALLTRIM(STR(RECNO() + 2))
    toSheet.Range(tcCell) = Precision
    toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)
  
    * Length
    tcCell = "D" + ALLTRIM(STR(RECNO() + 2))
    toSheet.Range(tcCell) = Length
    toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)
  
    * Scale
    tcCell = "E" + ALLTRIM(STR(RECNO() + 2))
    toSheet.Range(tcCell) = Scale
    toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)

    * Comments
    tcCell = "F" + ALLTRIM(STR(RECNO() + 2))
    toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)
  ENDSCAN

  toSheet.Columns("A").Autofit()
 
  SELECT (lnWorkArea)
ENDFUNC

A new VFP 9.0 SP2 help file is now available for download from the VFP home page. One issue that people are running into is that pages don’t display or links aren’t available in the file. If this happens to you, open Windows Explorer, right-click on the file and select Properties. Then click on Unblock.