MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

newbie questions

  Asked By: Diem    Date: Jan 02    Category: MS Office    Views: 936

I've programmed extensively in c, c++ and java but never in vba. Thus
I would like to know how to define a global variable, ie, so that I
can access it from any method.

My other main question is if I am using the vba code later in excel,
and I have a range of cells, how would I input a formula into another
cell using that range. For example, say I have the range, A1 to A10.
How would I put into the cell A11 the formula = sum(A1:A10) using VBA

Finally, how would I declare a two-dimensional array?



6 Answers Found

Answer #1    Answered By: Dion Jones     Answered On: Jan 02

There really are not any dumb questions  - just those which go unasked.

The best way to get your feet wet with VBA is to use the macro Recorder
and then decipher the results. So, if you have not found the Macro
Recorder in Excel, Tools -> Macro -> Record New Macro. Perform your
steps and then click the stop button on the Recording Toolbar. ALT+F11
will bounce you into the VBE window so you can review your results.

One other thing you should play with is turning Relative References on
and off when you are recording different macros so you can see the
results where you have specifically placed your cursor, versus placing
your cursor down two cells  and over two.

Give that a whirl and let us know if you need more specifics.

Also, in the links area of this group, there are links to some excellent
references for VBA Coding in Excel. However, you need to have a Yahoo
ID to access  them. http://groups.yahoo.com/group/ExcelVBA/links

Here is a link to further information regarding using arrays in Excel:

Answer #2    Answered By: Lurleen Fischer     Answered On: Jan 02

To make a variable  available to all procedures in all the vba  modules
in a project, declare  the variable at module level by using the Public
keyword rather than Dim AND putting it before the first procedure in
a standard vba module. Eg:

Public myVariable as long

Re:"How would I put  into the cell  A11 the formula  = sum(A1:A10) using
VBA code?"

If you record a macro you'll get R1C1 type references which you can
adjust thus:

Worksheets("Sheet1").Range("A11").FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"

Easier to write and understand though, you could use A1 type refs:

Worksheets("Sheet1").Range("A11").Formula = "=SUM(A1:A10)"

Answer #3    Answered By: Helina Bonkob     Answered On: Jan 02

I use many globals. Took a bit if experimentation to do it
correctly. I find the MSDN and microsoft.support sites good for my
level of experience (Advanced beginner - not a professional

Here's a couple of links for MSDN:


Microsoft support, searching on VBA gets you to the knowledge base:


Snipped from my serial I/O spreadsheet:

In the declarations section of a module
Option Explicit
Public com_port As Long
Public BailOut As Long
Public PortName As String
Public BaudStr As String
Public xerr As Boolean
Public data_msg As String
Public data_in As String
Public totalread As Long
Public r As Integer
Public pref As String
Public suff As String
Public AnswerLength As Integer
Public radio_address As Integer
Public j As Integer
Public Cmd As String

Answer #4    Answered By: Cara Lewis     Answered On: Jan 02

Do you have a code  for your serial I/O speadsheet ??
Not sure if we are at the same frequency, what I am trying to do is
to retrieve data from a serial port and display on a spreadsheet.
Here's my code snippet

Private Sub MSComm1_OnComm()
Dim sData As String ' Holds our incoming data
Dim DataIn As Variant
Dim lHighByte As Long ' Holds HighByte value
Dim lLowByte As Long ' Holds LowByte value
Dim lWord As Long ' Holds the Word result

' If comEvReceive Event then get data and display
If MSComm1.CommEvent = comEvReceive Then

sData = MSComm1.Input ' Get data (2 bytes)
lHighByte = Asc(Mid$(sData, 1, 1)) ' get 1st byte
lLowByte = Asc(Mid$(sData, 2, 1)) ' Get 2nd byte

' Combine bytes into a word
lWord = (lHighByte * &H100) Or lLowByte

Cells(1, 1).Value = (lWord)
End If
End Sub

It works for a short while, and after a few seconds it display an
error message as follows

" application-defined or object-defined error"

Any clue where goes wrong ??

Answer #5    Answered By: Luis Fischer     Answered On: Jan 02

I have no working knowledge of the MSComm methods of serial I/O. I
have seen references to it and may try it using your example  as a
start, but I digress.
I don't read here too often now that I have things under control, so
Send my Yahoo account (noskosteve) an email and I'll be glad to send
my spreadsheet, but be forewarned it is complex and the serial I/O
Comm Module is complex as well. I do, however, comment the begeebers
out of my code. anyway...

On to your problem - You must look in the code  to see what is
highlighted. To see it, click on the DEBUG choice when the error
dialog comes up. Whatever gave VBA heartburn will be highlited.
Sometimes the location of the root cause is far ahead of this
location, but it may be the only clue you get in this game.

I don't understand the need to combine bytes. I would only be able
to speculate that 'MSComm1.CommEvent' goes True when a serial byte
is received (by the OS) and stuffed into the VBA serial buffer. If
your code loops fast enough, you catch each individual byte
in 'sData'. But you say it works for a while I have also noticed
that VBS tends to "soft-fail" at times, thus masking your coding
problem. Again, only guessing, but it can be something like this:
Let's say sData has only one byte, VBS may not alert you to the fact
that 'Mid$(sData, 2, 1))' does not exist and proceed to load a blank
into 'lLowByte" ... which may be ok until some condition gets a choke-
hold on the VBA compiler and triggers angry mister error generator.

This may be part of understanding MSComm, but each serial input  is
only one byte. My code is fast enough, so I get individual bytes.

3- Pepper yout code with Debug.Print statements and spend lots of
time staring at the results in the immediate window to track variable
values and code progression. Mine has so many (and I don't want to
delete them for fear of future need), it frequently takes me
considerable time to figure out what the heck I'm looking at and
there I am in the execution, so I also prefix variables with the name
of the variable  and location in the code. Like this:

Placed appropriately:
Debug.Print "*Get-Data Sub Entry"

Debug.Print "* Get-Data Sub, sData=" ; sData

Debug.Print "* Get-Data Sub EXIT"

[[ notice the indented structure that mimics nesting.]]


Debug.Print "* Get-Data Sub, sData=" ; Mid(sData,1,1);"-";Mid(sData
(2,1)' etc

I think all my such statements have "ActiveSheet" as prefixes for
cell references, where yours below doesn't:
ActiveSheet.Cells(r, 1) = Readable / 1000

Answer #6    Answered By: Xavier Thompson     Answered On: Jan 02

Dim YourArray(100, 10)
will declare  a two dimensionl array  with members from 0 to 100, and 0
to 10; that is with 101 x 11 elements, since vba  assumes the first
element is 0 rather than 1. You can tell vba to assume that 1 is the
first element of an array with the statement:
Option Base 1
before any procedures in a module.
Alternatively you can be explicit and declare it thus:
Dim YourArray(1 To 100, 1 To 10)
or unusually:
Dim YourArray(13 To 100, 7 To 10)
which is an array with 88 x 4 elements.

Didn't find what you were looking for? Find more on newbie questions Or get search suggestion and latest updates.