Free Microsoft Excel 2013 Quick Reference

how to declare Pub Const String Array ?

can someone please show me how to declare a Public Constant String
Array? With shortened spelling below:

Pub Const C as String = "jones" works OK
Pub Const C(2) as String = "jones","smith" does not work

What to do? I would like to be able to index in an array of strings to
get replacement values for stuff in columns H,I,J etc. by using an
index of 18,19,20 etc.


Post your answer or comment

comments powered by Disqus

We can declare a dynamic array by:

Dim myArray() As String

But I would like to declare a 2-dimensional dynamic array.
How to do it? Anyone knows?
Thanks a lot!


Ok, I have the following global variable:

Public Const g_strPWD = "passwordgoeshere"

I also want to declare a global static arrray with the following feilds:

"Oct 2007", "Nov 2007", etc.....

The syntax has eluded me

I want to declare an array, the size of which depends on the length of the
contents of a cell - so, if the cell contains 123456, I want to declare a 6
element array etc.
However, when I try to run the following code, I'm told that I have to use a
constant, as against an expression, when defining the array size.
Can anyone suggest a way around this, please?

Sub StringProcess()
Dim ArrayLength As Integer
ArrayLength = Len(ActiveCell.Value)
Dim StoreArray(ArrayLength) As Integer
End Sub

Thanks in advance for your help


I've check if the string is numeric in using IsNumberic(string). But when I input the value like "1d01" or "1234567e9" it still pass the checking. How to check if the string does not contain any alphabet character?

Hi i want to how to declare local variables for Excel.Workbook object
at runtime using C#.

Suppose i have a dropdown which contains the list of excel files in a
Say there are 3 files in a particular folder. july.xls,
august.xls,september.xls. Each time the user selects a file i declare a
variable xlbook of type Excel.Workbook and open tat wbook.

Excel.Application xlApp=new Excel.Application();
Excel.Workbook"c:july.xls",missin g.value,missing.value....);
Excel.Workbook"c:august.xls",miss ing.value,missing.value..);
Excel.Workbook"c:september.xls",m issing.value,missing.value);

I want to know if i can declare xlbook1,xlbook2,xlbook3 at runtime...


Hi all

Im creating a new project in Access 2003. In my project i have 5 forms and one login form. In login form i have userid,password and department.

I need those variables to be carried out in all forms.

So is it possible to declare those variables globally? it is possible then please assist me how to declare and where to declare those variable globally


Hi, does anybody know how to rotate/shift a vector/array in excel without VBA ? Say that I have in the range A1:C1 the value 8, 9 and 10. Then I would like to rotate the value in the range one position to the right so that the result of =SUMPRODUCT(TRICK(A1:C1);A2:C2) would be 9 when A2:C2 values are 0, 0 and 1. Of course TRICK() is the question of this post ...

I hope I have made myself understood

I have this before my loops starts:

Dim log() As Variant

Max = ThisWorkbook.Sheets.Count ' finds the maximum array size = 3
ReDim log(1 To Max) ' declares the array variable with the necessary size

So in this test sheet, I have 3 of them, so Max would be 3 and my array
is log(1 to 3). I placed a watch on my array log and I see that it indeed
setup my array size of 3.

When info inputs into the array, I see a + sign under each element I guess
you call that, and when I press the (+) on each number I see:

log(1)(0) - Value 1
log(2)(0) - Value 2
log(3)(0) - Value 3

Now I would like to write these values back form the array and spit them out
starting on the second row going down on column A.

So A2 for log(1), and A3 for log(2), and A4 for log(3).

I feel like I am close to adding logging to my script, just need to learn how
to read these back in a loop.

Any help would be appreciated.

I've been programming in VB6 for a while but it seems that
how you declare the properties, methods, and events of a
class module in Excel VBA is different.

I've declared several public properties in a a class
module. Now I want to set these properties from a form
module. What am I doing wrong?

Code excerpt from Class Module (PGDSettings):
'LEIS User Name Start - Public
Public Property Get LEISUserName() As String
LEISUserName = GetSetting
(App.ProductName, "Settings", "LEISUUID")
End Property
Public Property Let LEISUserName(strLEISUserName As String)
SaveSetting App.ProductName, "Settings", "LEISUUID",
End Property
'LEIS User Name End

Code excerpt from Form Module:
Option Explicit
Public ps As New PGDSettings
Private Sub UserForm_Activate()
txtUuid.Text = ps.LEISUserName
End Sub

Hello, If I have a cell that has the following information:
DEF(352) HHY(24533) KLDD(3334)
And I extract that to a String with the following code:
How do I now store each entry in a list or somthing?

What I want to do is take a string like the above (It can have any number of Entrys) and store each entry into a list so I can work with them seperate.

I cannot figure out how to create a List or Array or somthing that can hold some number of Strings. I'v never used VB before but work is making me use it for this project.

My Ultimate goal is to take the string "DEF(352) HHY(24533) KLDD(3334)" which is in one cell and break it up into (in this case) 3 strings that will be placed into their own cells.

Any help is greatly appriciated.

I am trying to automate one of the routine tasks i need to handle. I have a 5 columns with userid, date,time and two flags to indicate two actions performed by user. The sample data looks as below

CJ3 07/01/2012 15:24:19 Y y
CJ3 07/01/2012 14:32:44 Y y
CJ3 09/01/2012 13:47:48 y
CJ3 09/01/2012 07:51:24 Y y
ADR 10/01/2012 15:16:46 Y y
CJ3 11/01/2012 22:25:24
CJ3 11/01/2012 19:15:33 Y y
CJ3 11/01/2012 14:26:10 Y
ADR 11/01/2012 11:24:15
CJ3 11/01/2012 09:41:44 Y
CJ3 11/01/2012 08:55:18 Y
ADR 12/01/2012 17:46:10 Y y
ADR 12/01/2012 11:34:04 Y y
CJ3 12/01/2012 09:17:01 Y
PC6 13/01/2012 15:55:46 y
ADR 13/01/2012 14:35:50 Y
MO5 13/01/2012 09:37:22 y
CJ3 13/01/2012 04:47:31 Y

For each user, I need to identify, if either or both of the flags are missing, if so I need to send one consolidated email with the date, time and a message indicating what flags are missing and send out a email.

Currently, I have written code to create a string array to hold these three columns, but i am not aware how to make this string array as body of the email.

Thanks in advance for your help.


Im not sure how to decalre a global public variable on a macro button I have! I need to declare it and set it to true! Anyone tell me how. This is the code im using.
 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If not fOK Then
	  MsgBox "Use the button you fool!"
	    Cancel = True
	    fOK = false
    End If
End Sub
Option Explicit
Sub SvMe() 'Save filename as value of A1 plus the current date

    Dim newFile As String, fName As String
     ' Don't use "/" in date, invalid syntax
    fName = Range("O2").Value
    newFile = fName & ".xls"
     ' Change directory to suit
    ChDir _
    "C:Documents and SettingsfaboualfaDesktop" 'YOU MUST Change USER NAME to suit
    ActiveWorkbook.SaveAs Filename:=newFile

End Sub



For Each myCell in myRange
'Would like to be able to move to the next myCell in a (non-contiguous)
'myRange if the current myCell.Value = 0. I do not know how to return
'the current myCell array element number AND to increment to the next
'non-zero "0" myCell.value
Next myCell

I have a text file which i will import into xls.
Once imported i am only interested in rows that have specific keywords in any
of its columns.

How to search for a string let us say 'HONDA' in the entire xls(across all columns and rows) and save only those rows containing the searched string, to a separate xls file?

I have 5 to 10 keywords to search.

I want the +ve result of all the above searches to be saved to a different xls.

How can i do this?

Thanks a lot. I am new to excel VBA.


Hey, so in one userform, I have an undefined array. The user will input the size of the array and then put values into each of these array variables

But then I need to use these values in another userform. How can I carry those undefined array size variables over? I know how to carry over an array with a defined size and I know how to carry over variables (not arrays), I'm just stumped on how to do this.

Thanks for the help

Hi, I'd like to write a formula that check if a string (contained in another cell - say A1) includes at least one occurrence of one of a set of 5 sub strings. If the substring is included then it should return which one.

For istance, say that the cell A1 = " The colour is BLACK" then I'd like a formula in cell B1 that check if any of the following strings is included in A1 (RED, GREEN, BLUE, BLACK, ORANGE) and that tells me which one.

Does anyone know how to do this with a one cell formula ?

Tryed with FOUND with little luck though



Sorry for my english

Hello all, I am currently trying to create a macro using a template to fill a spreadsheet.

The only problem I cannot fathom how to solve, is how to move the sizes at the end of the stock number into the size column.

Here is how I would like it to work:

It checks all the rows in column A after cell A4, for the size after the hash (Stock numbers look like this "CF18 BLACK#Size3" or "BDA BLUE#Size9") and concatenates it with UK at the end, so that it looks like "# UK", "3 UK" or "4 UK" to the size, in column M.

I have included an example spreadsheet, as well as a sample of the spreadsheet as we use it currently.

amazon listing.xls

USIN -:- YuxkUfWYzmcKUJhMpbwTMkWsyqaqNyHn

I need to get the real character value of a string which contains non-English character (Nordic)

In the Msgbox it is displayed as S?mna,Norway
In Debug.Print - the same

When I put its value in a userform textbox it is displayed correctly as Sømna,Norway

Can someone advice how to loop on the string with S?mna,Norway (in VBA) and get the value ø instead of the ? (the 2nd character)

Thanks !!!


I'm looking for a solution to this ! Will be very happy if you guys can sort this out !

As the title says, i want to replace a word/string from a cell that contain a string from another cell!


Column A contains many words including [img:1orcqvu5] and column B contains only : 1orcqvu5

Next Row - Column A contains many words including [img2d63ybp] and column B contains only : x2d63ybp

And so on ....

Is there any program by which i can replace

all the [img:1orcqvu5] /[img2d63ybp] / .... /..... / so on from column A to just [img] or to something else !

I have attached a sample file, will be grateful if somebody can solve this issue !


Hello there!

I've just stumbled accross this forum in search of an answer to a vba problem that I have so I thought I'd post and see if you could help me. excuse me if I'm not good with the terminology...

I'm trying to create a data entry form that uses comboboxes. I can populate the comboboxes with data from an excell sheet. There are 3 columns of data that are called to each combobox, and the list of data is the same in each combobox. When the chosen line is selected for each combobox I then want to pass all that data back to the excel sheet in the same column layout. I suspect this will need to be done with an array.

Basically, I want to have five tasks with timing details that are done from Monday to Friday - these are listed in excel. I want each of these tasks to appear in each combo box (two columns in the combobox) so that the user can decide which task will be done on which day. Then the tasks are passed back to excel as a timetable. If it were 1 column I could do it but the second column is causing me grief. I suspect this will need to be done with an array.

Clear as mud?!?

Hope someone can help me!



Does someone already have an example code to store excel in a multidimensional array? Here is what I am trying to accomplish. I have excel data have different # of columns. I would like to go from the first row, loop until the row contains no data, and for each row, loop and store each column's data until the column is empty.

I can handle the looping part, but need a little bit of help understanding how to declare/use a multidimensional array.

If anyone already has some code which can do what I described above, I would greatly appreciate it.

thanks in advance

Or you can just declare the whole array as variant and store what you want
in each element. Bit more overhead, however.

Or you could declare the whole array as long, and store only 0 or 1 in the
second dimension and have your code interpret it as boolean or treat it as
boolean and let vba coerce it.

Tom Ogilvy

"Charles Williams" > wrote in message
> Hi Tomer,
> There is a complex way of doing this using variants, but usually its
> to have two separate arrays on Dim as long and the other as boolean.
> (its usually faster to use long rather than integer).
> hth
> Charles
> ______________________
> Decision Models
> The Excel Calculation Site
> "Tomer" > wrote in message
> ...
> > Is it possible to declare a two dimenssion array, with
> > one dimmension integer on the second boolean? - or do
> > both dimmension should be with same type of value?
> > Many thans

I am trying to difrentiate between USA zip codes to Canada Post Codes.
As USA zip codes always start with a digit, and Canada Post code always
start with a letter, what would be the right formula to detect if a
string starts with a digit, or with a letter?


Dear All,

I'am having trouble with the GetPivotData-function. I've been googling, but couldn't find any solution.

In my source-data some records are identified by an single quote. When making a pivottable it shows that record under as [empty].

And now the problem comes. When I use the GetPivotData-function to extract data form the earlier mentioned pivottable I get an error-message.

I've attached an example in where I try to show the problem.

It's not possible to change the source. Does anyone have an idea how to solve this problem?

Best regards,

No luck finding an answer? You could always try Google.