Escaping ‘ in Access SQL
The “Replace” function should do the trick. Based on your code above: DLookup(“island”, “villages”, “village=”” & Replace(txtVillage, “””, “””) & “‘”)
vba
Copy an entire worksheet to a new worksheet in Excel 2010
It is simpler just to run an exact copy like below to put the copy in as the last sheet Sub Test() Dim ws1 As Worksheet Set ws1 = ThisWorkbook.Worksheets(“Master”) ws1.Copy ThisWorkbook.Sheets(Sheets.Count) End Sub
Excel telling me my blank cells aren’t blank
a simple way to select and clear these blank cells to make them blank: Press ctrl + a or pre-select your range Press ctrl + f Leave find what empty and select match entire cell contents. Hit find all Press ctrl + a to select all the empty cells found Close the find dialog Press … Read more
Get Name of Current VBA Function
There’s nothing to get the current function name, but you can build a fairly lightweight tracing system using the fact that VBA object lifetimes are deterministic. For example, you can have a class called ‘Tracer’ with this code: Private proc_ As String Public Sub init(proc As String) proc_ = proc End Sub Private Sub Class_Terminate() … Read more
If Not function proceeds when value *is* the one specified
Use On Error Resume Next judiciously. Don’t use On Error Resume Next for the entire code – it will hide all your errors. Use it only when it’s really needed. Using On Error Resume Next means telling the code to Shut UP and do what you want. In most cases it will do what you … Read more
How to fix a VBA “type mismatch” error after switching to 64-bit Excel
I updated the dll calls to use LongPtr everywhere instead of Long. You should have not done that. By adding PtrSafe to a function declaration, you promise to the compiler that you have put LongPtr in all places where it needs to be, and nowhere else. LongPtr is a pointer-sized integer. It must be used … Read more
VBA Run-time error 438 appears when “paste” runs
Try Selection.PasteSpecial xlPasteAll Paste by itself works on several objects, most notably Worksheet but not on a Range object which is what your Selection is. To paste to a Range you really have to use the PasteSpecial method with its’ available arguements such as xlPasteAll; xlPasteValues; xlPasteFormulas; xlPasteFormats and others which you can see by … Read more
Using VBA and VBA-JSON to access JSON data from WordPress API
The JsonConverter is returning a collection of VBA.Collections Scripting.Dictionaries, and Values. In order to understand the output you will have to test the TypeName of all the returned values. The real question is “How to navigate through a json object (or any unknown object for that matter) and access the values within. Immediate Window Using … Read more
ODBC Call Failed with stored procedure – Pass through query
To get more information about the cause of an “ODBC–call failed.” error we can loop through the DBEngine.Errors collection and see if there are other messages that might be a bit more descriptive. For example, with the code qdf.Connect = strConnectionString qdf.SQL = ” EXEC [dbo].[SAMPLE_TEST]” qdf.ReturnsRecords = True On Error GoTo oops Set rst … Read more
Start VBA macro when editing a cell
You can’t. The code engine won’t run while Excel is in Edit mode. You have to have the user enter the text in something other than a cell – like a control on the worksheet or a control on a userform.