Tag Archives: variables

Global variables reset automatically

Questions: I want to define a global variable as a worksheet on opening a file, so I have used the following code: In Module1: Public mySheet As Worksheet In ThisWorkbook: Sub Workbook_Open() Set mySheet = Sheet1 End Sub I want to then use mySheet to refer to this particular worksheet throughout various procedures, some of… Read More »

How to Split a String and Store in variables?

Questions: I want to split a certain string and store them in a variable. GetID = “13H4″ sSplit = Split(GetID) I want to split them and store “13” in a variable and “4” in another variable. I don’t want to use MID,RIGHT,LEFT methods because I don’t want to put a location of string to get… Read More »

In VBA do all variables start out as variant type

Questions: To quote my colleague: all variables in VBA are variants but with different vartype: Dim x ‘—>variant with vartype = vbEmpty x= “hello” ‘—>vartype changed from vbEmpty to vbString and value assigned x= 1 ‘—>vartype changed to vbInteger Dim x as String ‘—>variant with vartype = vbEmpty is created and then vartype changed to… Read More »

Named variables in SQL using ADODB in vba

Questions: I have lots of SQL scripts, many of which use various different variables throughout, and I’d like to be able to drop the results directly into Excel. The hope is to do this as ‘smoothly’ as possible, so that when someone gives me a new SQL script (which may be relatively complicated), it is… Read More »

Can cell number for vba be given using two variables

Questions: Dim VAR1, VAR2, VAR3, VAR4, AS variant VAR1 = Worksheets(“Sheet3”).Cells(3, “J”).Value VAR2 = Worksheets(“Sheet3”).Cells(3, “K”).Value VAR3 = Worksheets(“Sheet3”).Cells(3, “L”).Value VAR4 = Worksheets(“Sheet3”).Cells(3, “M”).Value =sheet1.range(VAR1,VAR2).COPY =Sheet3.range(VAR2,VAR4).Pastespecial for the above i have given cell numbers in Var1 Var2…Var4 Is it possible to define a cell location using two variables, Var1=B Var2=5 and somehow combine them to… Read More »

Copy and Paste to Destination with Variables?

Questions: I’m at a loss with the syntax on this. I’ve searched and tried probably 20 different ways to write this. Can anyone help me fix my syntax or whatever the problem is on the destination of my copy? Here is the code – Dim wksMain As Worksheet, wksStats As Worksheet, i As Long, x… Read More »