what does the symbol # means in VBA? (but I couldn't put the # in the subject of my message )
I'm trying to understand someone's code... at some point he wrote:
sum_LU_Area = 0#
I thought that the # was used to declare a constant but I'm not too sure because in his code earlier he declared
Public sum_LU_Area As Double
Beside, sum_LU_Area is calculated somewhere further in the program.
I have a userForm (Form1) that contains a persons name that I would like to reference in a separate UserForm (Form2). In the separate UserForm (Form2) I need to reference this persons name many times, so I was wondering if there was a was to declare this name in the separate UserForm (Form2) as a constant. Only thing is that a constant, to the best of my knowledge, must be an expression and not a variable. Mainly, I'm trying to avoid declaring the myName variable in each Sub within Form2, which it will be needed for a ton of Sub's.
Code for Form2: Const myName As String = Form1.txtName.Value
I am trying to declare lngLr as Long and Constant. But it's buggin out on me. Is this the correct way to do it?
Code:
Private Const lngLr As Long = ".Cells(Rows.Count, 1).End(xlUp).Row"
Sub calculate_active_employees_sheet_years_of_service_w_Oasis()
Application.ScreenUpdating = True
[Code].....
I know that we should declare all variables at the beginning of a subroutine, in fact I'm told it's good practice to use Option explicit to 'force' variables to be declared, my question is why?
If I don't declare a variable the routine still seems to work OK so what is the downside of not declaring them upfront? Is it just for neatness or common practice or is there another reason?
As a rule which variables should I declare?
Sub checkPO()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Dim Rng As Range, MainSheet As Worksheet, item As Variant ', CurrWidth As Integer, UnitWidth As Integer
Set MainSheet = Sheets("Orders To Chase")
Set Rng = MainSheet.Range("B5", MainSheet.Range("B60000").End(xlUp))
totalqueries = MainSheet.Range("B5", MainSheet.Range("B60000").End(xlUp)).Cells.Count
UnitWidth = 282 / totalqueries
With progbar '\displays the please wait box
.Show False
.prog.Width = 0 '\ updates progress bar
.Repaint
End With
If I declare the variable my progress bar goes off screen. remove it and its back to within its box.
I haven't declared any of these variables either. Does this matter?
POnum = .Range("G5").Value
Supplier = .Range("A5").Value
Req = .Range("B5").Value
Ordered = .Range("E5").Value
Why it seems not possible to declare variables in Class modules like so:
[Code] ....
I am running into the error, "Procedure too large". I know I need to break the range down into Arrays, so how can I hard code the values into an array? I cannot find an example to follow. Ranges: D:E,K:L,O:P,....etc. I know I can break the rows up into an array too, but one thing at a time. Here is an example of the range for D:E.
View 2 Replies View RelatedI have a column a1:A150 which includes some Data. I now want to declare these data as an array.
Afterwards I want from cell b50 to copy int the value from cell a1 and copying the next value from the array (cell a2) into cell b51 and so on until cell b200.
I want to do it by creating an array and not just by usual Excel formulas.
I want to declare and 'Set' a number of worksheets for later use. Like this ...
View 9 Replies View Relatedi need to set the range of variables that user can add to the range.
For Example:
AA_*
BB_*
CC_*
ABCD_*
so we accept variables STARTING with AA_ OR BB_ OR CC_ OR ABCD_. If the user enters sth else, then I want to disable the "Enter" key. (If the Cell is Empty than it is also OK!!)
If disabling the Enter key is not possible then maybe i can use Conditioning Formatting? But the question is then if i can use for single condition OR statement.
I need to create an array with a variable as it size For instance:
View 2 Replies View RelatedI have an object in a form and I need to get the name of the object from cell value or I need to declare it using Dim statement..
something like this..
NAcctF.Visible = False
where I need to declare like this..
itm= range("A1").value & "F"
itm.Visible = False
because, except "F" at last remaining part keep changing..
how to declare&initialize a variable as Global in vba?
I have a variable ,
listGroup=Array("aaa","bbb","ccc")
now i am using this variable for 3 different functions. so what i am doing is wrote the same code to 3 functions, so how i can declare&initialize this variable as global and access to all functions.
is it possible to declare an array or anyother datatype like the following in VBA?
Dim myArray() As Integer
myArray("A") = 0
myArray("Test") = 0
myArray("G") = 0
after checking some conditions i need to change the corresponding values too, like the following,
If mycondition = True Then
myArray("Test") = 1
End If
I need to declare a global variable in Application level not Module level.
I have declare a variable in Module1 and then Module2 with same name then complile it and get success. That means there has two variable with same name in different Module. I think this was not a proper global variable declaration by which I can allow to declare only one variable in all Module, Class every where. I have used code as below -
I need to declare a two dimensional array that will return the Row and the cell's string value. My only solution is to return the row as a string like this.
Public GlobalArray(2, 100) As String
How to get more control than this? Maybe I should just declare it as a variant but this might open it up for problems later.
I'm fairly new to VBA and I need to define a variable as a date range
i.e. Period1 = 1Apr2006 to 29Apr2006, Period2= 30Apr2006 to 27May2006 etc
How do I declare a const array of string type in VBA? for example I want to say
Const myStringArray(4) as String = string1, string2, string3, string4
I have a series of operations to carry out and, while I can do the code for each individual one, how to declare the variables correctly and have the operations done in a series of Do/ Loops or For/Nexts. Especially the declaring of named ranges as variables. Also a bit uncertain of the best way to find and coy the match. I have attached a simplified version of the workbook, with explanations on it.
Basically what I need to do is loop through a series of named ranges and then loop through the names in each, match each name with a name in a master list (with a flag as an image), add an e-mail hyperlink to that flagged name and copy both to a new cell.
Is it possible to use a contstant array in Excel VBA? I have tried several differnt ways to declare it, but the VBA editor keeps yelling at me. The following did not work:
View 2 Replies View Relatedi have problem with a long code.
So I divided that code in 4 parts.
So I have
module1 -> Sub Macro1()
module2 -> Sub Macro2()
module3 -> Sub Macro3()
module4 -> Sub Macro4()
I have declared
Option Explicit
Dim mdNextTime1 As Double
Dim myEnter As Integer
'Dim myK1 As Integer
'Dim myK2 As Integer
'Dim myK3 As Integer
Dim myK4 As Integer
Const myC As Integer = 82
Dim Lastrow As Integer
Dim myActualRow As Integer
all my sub have the following structure:
Here is what I try to do:
At
A1 = Power(C1,B1)
A2 = Power(C2, B1)
A3 = Power(C3, B1)
....
An = Power(Cn, B1)
How can I increment column C while keeping B constant at B1 by using right-bottom mouse pointer dragging on column A? Or is there another way?
If I have a formula that I want to copy throughout a column, but I want to keep one of the parts of the formula constant, is there a short way to do this?
For example:
First cell is A1*O24
Second Cell is A2*O24
Third Cell is A3*O24
Right now when I try to copy the formulas, the second cell always reverts to A2*O25, which is not correct.
I try to adjust chart range using variable value instead of constant value.
My current code :
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Values = "='Check_2G'!$Z$2:$Z$32"
I need to make range ( "='Check_2G'!$Z$2:$Z$32" ) adjestable with varible like m instead of 32 in previous code.
For a single column of values, the following macro counts the first value twice, how can i fix this? for example: A1 thru A5 are a,a,b,c,d the result is
a 2
a 2
b 1
c 1
d 1
and should be
a 2
b 1
c 1
d 1
Macro:
Sub CountOfEachItem()
Dim ListRange As Range
Dim NewList As Range
[Code].....
Code:
Const Books1 = Array("5638", "5639", "10984")
Const Books2 = Array("3140", "7509", "3050", "7549", "7508", "7032", "6770", "6773", "4388", "6460")
I want to set an array as above for multiple use in my code (Used for auto-filtering various data with the same criteria).
The above is not allowed it seems, is there an alternative method so that if my Constant changes I only need to amend this once.
I've never had to venture into Arrays as there was no need.
I'm now faced with Arrays.
Generally I'm able to figure things out, in this case I'm stumped.
What I have is a Constant Array, that output the amount of Rows the Array displayed in.
I know the information is there, what I don't know is how to extend the rows.
ie it Outputs 5 Rows right now, with a top row header.
What I want is 10 rows with a header.
I want to basically set a varible that can be called back into a multiple of documents I will try and explain this as best as I can but it is a fortnightly pay date... i.e. 22/04/2009 Wednesday... as the payroll is fortnightly I want to be able to use the current date or todays date in a future proof macro.
i.e. if todays date is the 14/04/09 then the next pay date has to be the 22/04/09, if this was run in two weeks it would be 06/05/2009.
I was thinking a long the lines of lock in one start date... 14/01/09, then adding increments of 14 days until todays date - variable + 14 * r = "-"
I am just wondering if this can be possible:
this is an table with a lot of cells
12 36 89 56 97 78 98 36
36 89 56 97 78 98 36 12
97 78 98 36 56 97 78 98
12 36 89 56 97 etc ..
etc...
to become like this ...
I created a name in Excel 2002, Name = "NameToUse", Refers to = "Bill".
How do I get the value in VBA code?
Range("NameToUse").value ' doesnt work
NameToUse ' doesn't work