vba

“Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]

Try below code I will recommend to use error handler while using vlookup because error might occur when the lookup_value is not found. Private Sub ComboBox1_Change() On Error Resume Next Ret = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Worksheets(“Sheet3”).Range(“Names”), 2, False) On Error GoTo 0 If Ret <> “” Then MsgBox Ret End Sub OR On Error Resume Next Result … Read more

How to compare two entire rows in a sheet

Sub checkit() Dim a As Application Set a = Application MsgBox Join(a.Transpose(a.Transpose(ActiveSheet.Rows(1).Value)), Chr(0)) = _ Join(a.Transpose(a.Transpose(ActiveSheet.Rows(2).Value)), Chr(0)) End Sub What’s going on: a is just shorthand for Application to keep the code below easier to read ActiveSheet.Rows(1).Value returns a 2-D array with dimensions (1 to 1, 1 to {number of columns in a worksheet}) We’d … Read more

How do I base64 encode a string efficiently using Excel VBA?

You can use the MSXML Base64 encoding functionality as described at www.nonhostile.com/howto-encode-decode-base64-vb6.asp: Function EncodeBase64(text As String) As String Dim arrData() As Byte arrData = StrConv(text, vbFromUnicode) Dim objXML As MSXML2.DOMDocument Dim objNode As MSXML2.IXMLDOMElement Set objXML = New MSXML2.DOMDocument Set objNode = objXML.createElement(“b64”) objNode.dataType = “bin.base64” objNode.nodeTypedValue = arrData EncodeBase64 = objNode.Text Set objNode = … Read more