Excel UDF - Revision history

Water: /* Features not covered by the UDF */

2025-03-08T07:19:48Z

Features not covered by the UDF

← Older revision		Revision as of 07:19, 8 March 2025
Line 67:		Line 67:

	== Features not covered by the UDF ==		== Features not covered by the UDF ==
	The UDF only covers basic user needs. Single line functions (like switching to another sheet) or functions with too many parameters (like formatting a cell or range) are not covered by this UDF~~. You need to use the Excel COM yourself~~.<br />		The UDF only covers basic user needs. Single line functions (like switching to another sheet) or functions with too many parameters (like formatting a cell or range) are not covered by this UDF.<br />
	I will give a few examples here. The rest can be found on [http://msdn.microsoft.com/en-us/library/ff846392.aspx MSDN].		I will give a few examples here. The rest can be found on [http://msdn.microsoft.com/en-us/library/ff846392.aspx MSDN].

Water: Changed: Created a function of the linear code.

2025-02-07T13:36:01Z

Changed: Created a function of the linear code.

← Older revision		Revision as of 13:36, 7 February 2025
Line 379:		Line 379:
	; 4 - Strip all cells with more than 255 but less than 32767 characters to 255 characters using StringLeft		; 4 - Strip all cells with more than 255 but less than 32767 characters to 255 characters using StringLeft
	; 8 - Replace keyword Null with space		; 8 - Replace keyword Null with space
	~~Global~~ $aCount[] = [0, 0, 0, 0]		_Excel_Verify($aData, $bDetail, $bFix)
	For $i = 0 To UBound($aData, 1) - 1		Exit
	For $j = 0 To UBound($aData, 2) - 1
	Select		Func _Excel_Verify($aData)
	Case IsObj($aData[$i][$j])		Local $aCount[] = [0, 0, 0, 0]
	If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": IsObj" & @CRLF)		For $i = 0 To UBound($aData, 1) - 1
	If BitAND($bFix, 1) = 1 Then $aData[$i][$j] = ""		For $j = 0 To UBound($aData, 2) - 1
	$aCount[0] += 1		Select
	Case StringLen($aData[$i][$j]) > 32767		Case IsObj($aData[$i][$j])
	If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": Length > 32767" & @CRLF)		If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": IsObj" & @CRLF)
	If BitAND($bFix, 2) = 2 Then $aData[$i][$j] = StringLeft($aData[$i][$j], 32767)		If BitAND($bFix, 1) = 1 Then $aData[$i][$j] = ""
	$aCount[1] += 1		$aCount[0] += 1
	Case StringLen($aData[$i][$j]) > 255		Case StringLen($aData[$i][$j]) > 32767
	If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": Length > 255" & @CRLF)		If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": Length > 32767" & @CRLF)
	If BitAND($bFix, 4) = 4 Then $aData[$i][$j] = StringLeft($aData[$i][$j], 255)		If BitAND($bFix, 2) = 2 Then $aData[$i][$j] = StringLeft($aData[$i][$j], 32767)
	$aCount[2] += 1		$aCount[1] += 1
	Case IsKeyword($aData[$i][$j]) = $KEYWORD_NULL		Case StringLen($aData[$i][$j]) > 255
	If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": IsNull" & @CRLF)		If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": Length > 255" & @CRLF)
	If BitAND($bFix, 8) = 8 Then $aData[$i][$j] = ""		If BitAND($bFix, 4) = 4 Then $aData[$i][$j] = StringLeft($aData[$i][$j], 255)
	$aCount[3] += 1		$aCount[2] += 1
	EndSelect		Case IsKeyword($aData[$i][$j]) = $KEYWORD_NULL
	Next		If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": IsNull" & @CRLF)
	Next		If BitAND($bFix, 8) = 8 Then $aData[$i][$j] = ""
	ConsoleWrite(@CRLF & "SUMMARY" & @CRLF)		$aCount[3] += 1
	ConsoleWrite("Cells containing data type object : " & $aCount[0] & ". Fixed: " & (BitAND($bFix, 1) = 1) & @CRLF)		EndSelect
	ConsoleWrite("Cells containing data > 32.767 characters: " & $aCount[1] & ". Fixed: " & (BitAND($bFix, 2) = 2) & @CRLF)		Next
	ConsoleWrite("Cells containing data > 255 characters : " & $aCount[2] & ". Fixed: " & (BitAND($bFix, 4) = 4))		Next
	If (BitAND($bFix, 4) <> 4) Then ConsoleWrite(". Use $bForceFunc = True in _Excel_RangeWrite.")		ConsoleWrite(@CRLF & "SUMMARY" & @CRLF)
	ConsoleWrite(@CRLF)		ConsoleWrite("Cells containing data type object : " & $aCount[0] & ". Fixed: " & (BitAND($bFix, 1) = 1) & @CRLF)
	ConsoleWrite("Cells containing data type Null : " & $aCount[3] & ". Fixed: " & (BitAND($bFix, 8) = 8) & @CRLF)		ConsoleWrite("Cells containing data > 32.767 characters: " & $aCount[1] & ". Fixed: " & (BitAND($bFix, 2) = 2) & @CRLF)
			ConsoleWrite("Cells containing data > 255 characters : " & $aCount[2] & ". Fixed: " & (BitAND($bFix, 4) = 4))
			If (BitAND($bFix, 4) <> 4) Then ConsoleWrite(". Use $bForceFunc = True in _Excel_RangeWrite.")
			ConsoleWrite(@CRLF)
			ConsoleWrite("Cells containing data type Null : " & $aCount[3] & ". Fixed: " & (BitAND($bFix, 8) = 8) & @CRLF)
			EndFunc ;==>_Excel_Verify
	</syntaxhighlight>		</syntaxhighlight>

Water: Enhanced formatting

2024-05-20T13:59:38Z

Enhanced formatting

← Older revision		Revision as of 13:59, 20 May 2024
Line 309:		Line 309:
	=== Transpose limits ===		=== Transpose limits ===
	Functions _Excel_RangeWrite, _Excel_RangeRead and _Excel_RangeCopyPaste use the Excel transpose method. This method has an undocumented limit based on the Excel version you run.		Functions _Excel_RangeWrite, _Excel_RangeRead and _Excel_RangeCopyPaste use the Excel transpose method. This method has an undocumented limit based on the Excel version you run.
	Details about Excel up to version 2000 can be found here: https://support.microsoft.com/en-us/kb/177991, https://www.askingbox.com/info/xls-and-xlsx-maximum-number-of-columns-and-rows, https://www.mrexcel.com/forum/excel-questions/49697-upper-limit-transpose-function.html, https://newtonexcelbach.com/2016/01/01/worksheetfunction-transpose-changed-behaviour-in-excel-2013-and-2016/.		Details about Excel up to version 2000 can be found here:
			* https://support.microsoft.com/en-us/kb/177991, <BR>
			* https://www.askingbox.com/info/xls-and-xlsx-maximum-number-of-columns-and-rows, <BR>
			* https://www.mrexcel.com/forum/excel-questions/49697-upper-limit-transpose-function.html, <BR>
			* https://newtonexcelbach.com/2016/01/01/worksheetfunction-transpose-changed-behaviour-in-excel-2013-and-2016/.
	{\| class="wikitable"		{\| class="wikitable"
	\|-		\|-

Water: /* Compare example scripts */

2021-04-08T08:32:43Z

Compare example scripts

← Older revision		Revision as of 08:32, 8 April 2021
Line 260:		Line 260:
	#include <Excel.au3>		#include <Excel.au3>
	Local $sWorkbook = @TempDir & "\Temp.xls"		Local $sWorkbook = @TempDir & "\Temp.xls"
	; The workbook you want to connect to must already have been opened by the user or another script		; The workbook you want to connect to must already
			; have been opened by the user or another script
	$oWorkbook = _Excel_BookAttach($sWorkbook)		$oWorkbook = _Excel_BookAttach($sWorkbook)
	</syntaxhighlight>		</syntaxhighlight>

Water: /* Compare example scripts */

2021-04-08T08:31:35Z

Compare example scripts

← Older revision		Revision as of 08:31, 8 April 2021
Line 260:		Line 260:
	#include <Excel.au3>		#include <Excel.au3>
	Local $sWorkbook = @TempDir & "\Temp.xls"		Local $sWorkbook = @TempDir & "\Temp.xls"
	~~Local $oExcel = _Excel_Open()~~		; The workbook you want to connect to must already have been opened by the user or another script
	~~Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)~~
	$oWorkbook = _Excel_BookAttach($sWorkbook)		$oWorkbook = _Excel_BookAttach($sWorkbook)
	</syntaxhighlight>		</syntaxhighlight>

Water: /* Function -/_Excel_Open */

2021-04-08T08:27:05Z

Function -/_Excel_Open

← Older revision		Revision as of 08:27, 8 April 2021
Line 209:		Line 209:

	=== Function -/_Excel_Open ===		=== Function -/_Excel_Open ===
	It's mandatory now to call function _Excel_Open before any other function. This function didn't exist in the old UDF.		It's mandatory now to call function _Excel_Open before any other function (except _Excel_BookAttach). This function didn't exist in the old UDF.
	@extended is set if Excel was already running.		@extended is set if Excel was already running.

Water: /* _Excel_RangeWrite writes no data */

2020-02-07T10:34:37Z

_Excel_RangeWrite writes no data

← Older revision		Revision as of 10:34, 7 February 2020
Line 369:		Line 369:
	Global $aData[3][2] = [[ObjCreate("Scripting.Dictionary"), _StringRepeat("1", 256)], [_StringRepeat("1", 32768), _StringRepeat("1", 256)], [Null]]		Global $aData[3][2] = [[ObjCreate("Scripting.Dictionary"), _StringRepeat("1", 256)], [_StringRepeat("1", 32768), _StringRepeat("1", 256)], [Null]]
	Global $bDetail = True ; If set to True each cell with invalid data is listed with zero based row, column and explanation		Global $bDetail = True ; If set to True each cell with invalid data is listed with zero based row, column and explanation
	Global $bFix = 15 ; If set to <> 0 cells with invalid data will be modified or shortened so, as a result, they hold valid data		Global $bFix = 15 ; If set to <> 0 cells with invalid data will be modified or shortened so, as a result, they hold valid data
	; Can be a combination of any of the following values:		; Can be a combination of any of the following values:
	; 1 - Set all objects to ""		; 1 - Set all objects to ""

Water: /* _Excel_RangeWrite writes no data */

2020-01-28T10:10:45Z

_Excel_RangeWrite writes no data

← Older revision		Revision as of 10:10, 28 January 2020
Line 359:		Line 359:
	* a cell in the data to be written exceeds the 255 characters limit		* a cell in the data to be written exceeds the 255 characters limit
	* a cell containing data of type object		* a cell containing data of type object
	You could use something like this to check the correctness of your data. Row, Column and explanation get written to the ~~screen~~.<br>		* a cell containing the keyword Null
			You could use something like this to check the correctness of your data. Row, Column and explanation get written to the console.<br>
	Example for a 2D array:		Example for a 2D array:
	<syntaxhighlight lang="autoit">		<syntaxhighlight lang="autoit">
	#include <String.au3>		#include <String.au3>
			#include <AutoItConstants.au3>

	; Create an array with invalid data		; Create an array with invalid data
	Global $aData[2][2] = [[ObjCreate("Scripting.Dictionary"), _StringRepeat("1", 256)], [_StringRepeat("1", 32768), _StringRepeat("1", 256)]]		Global $aData[3][2] = [[ObjCreate("Scripting.Dictionary"), _StringRepeat("1", 256)], [_StringRepeat("1", 32768), _StringRepeat("1", 256)], [Null]]
	Global $bDetail = True ; If set to True each cell with invalid data is listed with zero based row, column and explanation		Global $bDetail = True ; If set to True each cell with invalid data is listed with zero based row, column and explanation
	Global $bFix = 7 ; If set to <> 0 cells with invalid data will be modified or shortened so, as a result, they hold valid data		Global $bFix = 15 ; If set to <> 0 cells with invalid data will be modified or shortened so, as a result, they hold valid data
	; Can be a combination of any of the following values:		; Can be a combination of any of the following values:
	; 1 - Set all objects to ""		; 1 - Set all objects to ""
	; 2 - Strip all cells with more than 32767 characters to 32767 characters using StringLeft		; 2 - Strip all cells with more than 32767 characters to 32767 characters using StringLeft
	; 4 - Strip all cells with more than 255 but less than 32767 characters to 255 characters using StringLeft		; 4 - Strip all cells with more than 255 but less than 32767 characters to 255 characters using StringLeft
	Global $aCount[] = [0, 0, 0]		; 8 - Replace keyword Null with space
			Global $aCount[] = [0, 0, 0, 0]
	For $i = 0 To UBound($aData, 1) - 1		For $i = 0 To UBound($aData, 1) - 1
	For $j = 0 To UBound($aData, 2) - 1		For $j = 0 To UBound($aData, 2) - 1
Line 387:		Line 391:
	If BitAND($bFix, 4) = 4 Then $aData[$i][$j] = StringLeft($aData[$i][$j], 255)		If BitAND($bFix, 4) = 4 Then $aData[$i][$j] = StringLeft($aData[$i][$j], 255)
	$aCount[2] += 1		$aCount[2] += 1
			Case IsKeyword($aData[$i][$j]) = $KEYWORD_NULL
			If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": IsNull" & @CRLF)
			If BitAND($bFix, 8) = 8 Then $aData[$i][$j] = ""
			$aCount[3] += 1
	EndSelect		EndSelect
	Next		Next
	Next		Next
	ConsoleWrite("SUMMARY" & @CRLF)		ConsoleWrite(@CRLF & "SUMMARY" & @CRLF)
	ConsoleWrite("Cells containing data type object : " & $aCount[0] & ". Fixed: " & (BitAND($bFix, 1) = 1) & @CRLF)		ConsoleWrite("Cells containing data type object : " & $aCount[0] & ". Fixed: " & (BitAND($bFix, 1) = 1) & @CRLF)
	ConsoleWrite("Cells containing data > 32.767 characters: " & $aCount[1] & ". Fixed: " & (BitAND($bFix, 2) = 2) & @CRLF)		ConsoleWrite("Cells containing data > 32.767 characters: " & $aCount[1] & ". Fixed: " & (BitAND($bFix, 2) = 2) & @CRLF)
Line 396:		Line 404:
	If (BitAND($bFix, 4) <> 4) Then ConsoleWrite(". Use $bForceFunc = True in _Excel_RangeWrite.")		If (BitAND($bFix, 4) <> 4) Then ConsoleWrite(". Use $bForceFunc = True in _Excel_RangeWrite.")
	ConsoleWrite(@CRLF)		ConsoleWrite(@CRLF)
			ConsoleWrite("Cells containing data type Null : " & $aCount[3] & ". Fixed: " & (BitAND($bFix, 8) = 8) & @CRLF)
	</syntaxhighlight>		</syntaxhighlight>

Water: /* Function _ExcelSheetNameSet/- */

2019-10-15T14:35:12Z

Function _ExcelSheetNameSet/-

← Older revision		Revision as of 14:35, 15 October 2019
Line 237:		Line 237:
	=== Function _ExcelSheetNameSet/- ===		=== Function _ExcelSheetNameSet/- ===
	The function only contained a single line of code. So it was removed.		The function only contained a single line of code. So it was removed.
	Replace the function with the following code:		Replace the function with one of the following lines of code:
	<syntaxhighlight lang="autoit">		<syntaxhighlight lang="autoit">
	$oSheet.Name = "Name of the sheet"		$oSheet.Name = "New name of the sheet" ; $oSheet is the object of the worksheet
			$oWorkbook.Sheets(n).Name = "New name of the sheet" ; n is either the index or the current name of the worksheet
	</syntaxhighlight>		</syntaxhighlight>

Water: /* Function _ExcelSheetNameGet/- */

2019-10-15T14:33:39Z

Function _ExcelSheetNameGet/-

← Older revision		Revision as of 14:33, 15 October 2019
Line 229:		Line 229:
	=== Function _ExcelSheetNameGet/- ===		=== Function _ExcelSheetNameGet/- ===
	The function only contained a single line of code. So it was removed.		The function only contained a single line of code. So it was removed.
	Replace the function with the following code:		Replace the function with one of the following lines of code:
	<syntaxhighlight lang="autoit">		<syntaxhighlight lang="autoit">
	$sSheetName = $oSheet.Name ; $oSheet is the object of the worksheet		$sSheetName = $oSheet.Name ; $oSheet is the object of the worksheet