Erlandsen Data ConsultingIf you need to get the result from a formula in a cell, you would normally do something like this: dblValue = Range(“A1″).Value If you need to calculate parts of a formula or math expression then you can do something like this: ‘ assume that cell A1 contains this formula: ‘ =(A2+A3)/(A4+A5) strFormula = Range(“A1″).Formula [...]
With the macros below you are able to open and close the default CD/DVD tray. Declare Sub mciSendStringA Lib “winmm.dll” (ByVal lpstrCommand As String, _ ByVal lpstrReturnString As Any, ByVal uReturnLength As Long, _ ByVal hwndCallback As Long) Sub OpenDefaultDiscTray() mciSendStringA “Set CDAudio Door Open”, 0&, 0, 0 End Sub Sub CloseDefaultDiscTray() mciSendStringA “Set CDAudio [...]
The function below can be used to let the user pick or select a custom color. It uses the Excel applications own built in dialog for selecting colors. Unfortunately this built in dialog does not return a value for the color selected by the user. If the user selects a color in the dialog, it [...]
With the procedure below you can convert a color value to its separate RGB counterparts: Sub Color2RGB(lngColor As Long, intR As Integer, intG As Integer, intB As Integer) ‘ converts a color to its RGB counterparts intR = lngColor And 255 intG = lngColor \ 256 And 255 intB = lngColor \ 256 ^ 2 [...]
The worksheetfunction RAND() will return a decimal value greater than or equal to 0 and less than 1. Here are some examples on how to use this function : =RAND()*100 Returns a decimal value between 0 and 100 =RAND()*(100-50)+50 Returns a decimal value between 50 and 100 =ROUND(RAND()*100,0) Returns an integer value between 0 and [...]
Sometimes you need to display a Roman number. The simple numbers are easy to create, but when the numbers are large most peple have trouble getting them right. The function ROMAN(number,form) will display Roman numbers between 1 and 3999. The argument form is optional and can be omitted, but can also be used to tell [...]
Getting a root of a number is the same as raising that number to the power of 1/root. Example: The square root of x is x^(1/2). The square root of A1 is: =A1^(1/2). You can also use the built-in worksheet function SQRT: =SQRT(A1) The cube root of x is x^(1/3). The cube root of A1 [...]
Most users take advantage of the logical function IF(logical_test,if_true,if_false) to make Excel take a logical decision. If you want to perform more complex comparisions you can add the logical functions AND(), OR() and NOT() when you use the IF()-function. Here are some examples on how to use these functions: =IF(logical_test,if_true,if_false) This function performs a simple [...]
With the example function below you can get user defined functions to return error values just like the built in functions in Excel do. Function DummyFunctionWithErrorCheck(InputValue As Variant) As Variant If InputValue < 0 Then ‘ return an error value DummyFunctionWithErrorCheck = CVErr(xlErrNA) ‘ returns #N/A! ‘ xlErrDiv0, xlErrNA, xlErrName, xlErrNull, xlErrNum , xlErrRef, xlErrValue [...]
The function below converts a number between 1 and 256 to a column reference between A and IV: Function ColNo2ColRef(ColNo As Integer) As String If ColNo < 1 Or ColNo > 256 Then ColNo2ColRef = “#VALUE!” Exit Function End If ColNo2ColRef = Cells(1, ColNo).Address(True, False, xlA1) ColNo2ColRef = Left(ColNo2ColRef, InStr(1, ColNo2ColRef, “$”) – 1) End [...]