I think you are looking for the Dictionary object, found in the Microsoft Scripting Runtime library. (Add a reference to your project from the Tools…References menu in the VBE.) It pretty much works with any simple value that can fit in a variant (Keys can’t be arrays, and trying to make them objects doesn’t make … Read more
The type-declaration character for Double is the number sign (#). Also called HASH Other type declaration characters are: Integer % Long & Currency @ Single ! Double # String $ Don’t understand the significance of # here. It implies that when the expression is evaluated, the number in front of the type declaration character is … Read more
That’s right, execution of user-defined functions has certain limitations. There is few tricks to do what you want within UDF and not to violate an order. 1. Get another instance of Excel.Application via late binding, open workbook with it, perform all the necessary calculations by referring to the instance. It’s critical to refer exactly to … Read more
Where do you want to see the output? Messages being output via Debug.Print will be displayed in the immediate window which you can open by pressing Ctrl+G. You can also Activate the so called Immediate Window by clicking View -> Immediate Window on the VBE toolbar
Excel VBA has the Application.OnUndo function to handle this: Public Sub DoSomething … do stuff here Application.OnUndo “Undo something”, “UnDoSomething” End Sub Public Sub UnDoSomething … reverse the action here End Sub
If you want to know the color of a cell that has been colored by a conditional formatting rule (CFR) then use Range.DisplayFormat.Interior.Color¹. If you want to definitively know what color a cell may or may not have been colored by a CFR you need to iterate through the CFRs that could be affecting that … Read more
It’s solved finally… Option Explicit Public Declare Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long) Private Declare Function FindWindowEx Lib “user32” Alias “FindWindowExA” _ (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _ ByVal lpsz2 As String) As Long Public Sub AddReference() ThisWorkbook.VBProject.References.AddFromFile “C:\Windows\SysWOW64\UIAutomationCore.dll” End Sub ‘after my original code as … Read more
Use this one instead: .AutoFilter Field:=1, Criteria1:=Array(“HP Compaq 6000”, “HP Compaq 8000”, _ “HP Compaq 8200”, “HP Compaq 8200 Elite”, _ “HP Compaq dc5800”, “HP Compaq dc7900”, _ “HP Compaq Elite 8300 SFF”, “HP Compaq Pro 8300 SFF”), _ Operator:=xlFilterValues
This will get you to an answer for your simple case, but can you expand on how you’ll know which columns will need to be compared (B and C in this case) and what the initial range (A1:D5 in this case) will be? Then I can try to provide a more complete answer. Sub setCondFormat() … Read more
In the best of all possible worlds the Related list would contain a reference to this question which I found as the first hit for “vbscript adodb.stream bom vbscript site:stackoverflow.com”. Based on the second strategy from boost’s answer: Option Explicit Const adSaveCreateNotExist = 1 Const adSaveCreateOverWrite = 2 Const adTypeBinary = 1 Const adTypeText = … Read more