Free Microsoft Excel 2013 Quick Reference

Excel, Hash (#) in VBA Calculations

Hi Folks,

I have recently inherited care of an Excel XP model that contains the
following calculations, containing the # symbol.

Amount = Amount / 100#
NewValue = OldValue * -1#

I can see where the variables Amount, NewValue and Old Value are declared
but nothing on the #.

The end results sor t of make sense if the # symbols were in fact the %
sign. Other than that I am totally confused.

Does this use of the # symbol make sense to anyone.

TIA

John Howard
Sydney, Australia


Post your answer or comment

comments powered by Disqus
Hi,

How I define the excel function in VBA if I want to use SUM -function which should sum up values in cells A1 and C2 for example. I have readed VBA help but procedure is still unclear.

OS: MS XP, Excel 2003

In VBA I need to be able to Open and when finished Close an Excel File.
There are several files in my folder. Folder is named Thom Drive.
All the file names start with 01, 02, and 03 Etc... They reference the
weeks in a year.
The 1st two (2) positions in the file name are unique.
How would I go about this using VBA?
..
Thanks in advanced.

--
Richard

Hi,

Can we use the excel formula in VBA directly without putting them on to worksheet.

FOr example a formula to sum a range like =SUM(A1:A10). As of now I am putting this to a dummy cell through VBA code, then getting value back again from that.

Now the question is : Can we use this SUM(A1:A10) formula directly in the code and get the value without pasting this formula to excel worksheet and get the summed value from that cell. Is there any method to achieve this.

Please help. Thanks a lot in advance for the expert advice.

Regards
Anand

Hi all,
I am new in VBA.
I want to use excel functions in VBA Loop.
fyr,
Formulas can be use by following way without Loop.
Dim fs As Worksheet
Set fs = Worksheets("sheet1");
Dim os As Worksheet
Set os = Worksheets("sheet2");

With fs
   .Cells(4, 2).Formula = "=int(sheet2!e4*sheet2!b13)"
End With
But now I want to use this Blue color lines in some range.
i.e
For i=4 To 10
  With fs
     .Cells(i, 2).Formula = "=int(os.Cells(i,2)*os.Cells(i+1,2))"
  End With
Next i
But Its seem to be wrong and its not working.
Can anyone help me to find the correct way to do it?

is there anyway to create a clone of an excel sheet in vba?

Im trying to set up an option pricer where i have unlimted number of calculation which spits out a value. I want to do all the calculations in VBA and then put the single value into excel. From what i hear this is possible but im not sure how to do it. Can anyone help me?

Much appreciated.

Hi guys,

I have two doubts related to some VBA code and would be very grateful if you could help me.

1. I have a VBA macro with some variables defined and in that code I'm trying to use standard Excel formulas. However, I'm not sure how to use Excel formulas + the code variable(s) together. More specifically, here is a snippet of my code -

            ' some code here along with start of the IF statement
            ElseIf k = 5 Then
                    Worksheets(i).Cells(10, 10).Formula = "=IF(ISERROR(Worksheets(i).Cells(4, 10).Value *
Worksheets(i).Cells(5, 10).Value), 0, Worksheets(i).Cells(4, 10).Value * Worksheets(i).Cells(5, 10).Value)"
                    Worksheets(i).Cells(11, 10).Formula = "=IF(ISERROR(Worksheets(i).Cells(6, 10).Value *
Worksheets(i).Cells(7, 10).Value), 0, Worksheets(i).Cells(6, 10).Value * Worksheets(i).Cells(7, 10).Value)"
                    Worksheets(i).Cells(12, 10).Formula = "=IF(ISERROR(Worksheets(i).Cells(8, 10).Value *
Worksheets(i).Cells(9, 10).Value), 0, Worksheets(i).Cells(8, 10).Value * Worksheets(i).Cells(9, 10).Value)"
                    Worksheets(i).Cells(j, k).Value = Worksheets(i).Cells(12, 10).Value + Worksheets(i).Cells(11, 10) +
Worksheets(i).Cells(10, 10)
            ' some code here along with end of the IF statement
As you can see, I have a variable "i" which loops through multiple sheets of the workbook doing some calculations in cells (10,10), (11,10), (12,10) and (j,k) respectively ("j", "k" too are variables). When I run my macro, it runs totally fine if these 4 lines are commented. But otherwise I get an error in these lines. So could someone tell me *specifically* what is the correct syntax for this statement, and also what one must do *in general* if one wants to use Excel formulas in VBA?

2. I also have one other question. How do I check if a particular value exists in a range (in my case the value is "#N/A Requesting Data...")? In my code currently I'm looping through every cell in that range and checking if
 and then doing some particular task. Although this is working, it is obviously very expensive in terms of time. Is there a
better way to check if a value exists in a range which you define?

Hope someone can help me at the earliest.

Thanks a lot,
exceluser_1

Hi All,

I want to use following formulae in vba and I want excel to update this every time worksheet is opened as it involves calculation of days using today function. Here are my formulae to be used as vba code.

In Column M2:M300=IF(ISTEXT(N2),"First Reminder Sent",IF(ISBLANK(L2),"",IF(L2<=TODAY()+30,"First Reminder Due","")))
In Column O2:O300=IF(ISTEXT(P2),"Second Reminder Sent",IF(ISBLANK(L2),"",IF(L2<=TODAY()+14,"Second Reminder Due","")))
In Column Q3:Q300=IF(ISTEXT(S2),"Final Reminder Sent",IF(ISBLANK(L2),"",IF(L2<=TODAY()+7,"Final Reminder Due","")))

Hope I would get some help

how can i clear the contents of an excel text box not a vba text box, in vba??
any ideas?

cheers guys
G

ActiveCell = Application.IsText(ActiveCell)

This syntax doesn't seem to be working.

Anyone know the correct way to use Excel functions (eg. IsText) in vba?

Thanks ,

Paul

Hi everyone,
I have the following VBA question. I want to calculate the variance of a range. Lets assume the code looks like the following

Code:
 
Dim wb as Workbook
Dim wb as Worksheet
Dim rng1, rng2 as Range
Dim Variance As Long
 
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")
Set rng1 = ws.Range("A1:A1000")
Set rng2 = ws.Range("B1:B1000")
 
Variance = Worksheetfunction.Var(rng1 *rng2) '

i'm editing someone else's work and they had created chart templates which still work fine... except for the colors.

we've changed the color scheme so that when we apply the old styles, text comes up as lime green. i was wondering if there is a way in vba to edit the color application. tell it that the text color is custom color no. 4 instead of 5?

they are MS Excel objects in VBA in a sheet. when i look in the "Chart Type" under Custom, the charts are "Built In" [normally one might expect to find them under "user-defined"].

anyway,this guy was super smart and if i have to start from scratch, it will be a much bigger project.

Ideas?

excelvbascreenshot1.jpg

I need to calculate the absolute week and have found a formula to do so. Is there a way to embed/execute this in VBA?

TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7) where StartDate is the date which you are trying to find the corresponding week number (for example Now().

Hi guys, I'm trying to call the function "mduration" in VBA. It outputs the correct numbers but it always says: "type mismatch" after performing the calculations.

Here's the code:


Cell.Offset(0, 15).Formula = "=mduration(" & Chr(34) & Date & Chr(34) & ", " &
Chr(34) & Cell.Offset(0, 6).Value & Chr(34) & ", " & Chr(34) & Cell.Offset(0, 4).Value / 100
& Chr(34) & ", " & Chr(34) & Cell.Offset(0, 14).Value / 100 & Chr(34) & ", "
& Chr(34) & Cell.Offset(0, 5).Value & Chr(34) & ")"
Cell.offset(0,14).value refers to yield. It has the following code which works fine:

Cell.Offset(0,
14).Formula = "=yield(" & Chr(34) & Date & Chr(34) & ", " & Chr(34) &
Cell.Offset(0, 6).Value & Chr(34) & ", " & Chr(34) & Cell.Offset(0, 4).Value / 100 & Chr(34)
& ", " & Chr(34) & Cell.Offset(0, 12).Value & Chr(34) & "," & Chr(34) & 100
& Chr(34) & "," & Chr(34) & Cell.Offset(0, 5).Value & Chr(34) & ")*100"
Cell.Offset(0, 14).Value = Cell.Offset(0, 14).Value
Any suggestions as to how to combat the type mismatch error?

Hi there

While using Excel Search Function in VBA programming, I got
"Sub or Function not defined!!" Error...

But need to use the Search funtion..
So please tell me how to use it?

Thanks in advance
john best

Hi, all,
Anyone know if we can use the excel built in function, e.g. sum(), countif(), offset() , in vba? if yes, what is the syntax like?

thanks a lot

Hi EveryOne,

I am trying to write some code that will help automate Excel reports. I am running into trouble, when I try to incorporate Excel formula's in VBA.

Here's some code that I am working with - but I am getting run time errors.

Regards - Exxel..

Sub InsertFormula()
'Variable Declaration..

Dim Arg1, Arg2 As String
Dim ProdRowNum As Integer
Dim ProdStartPeriod As Integer
Dim ProdEndPeriod As Integer
Dim TotalUnits As Integer
    
'RUN TIME ERROR OCCURS HERE. THESE MATCH STATEMENTS WERE WORKING OK Y'DAY - BUT TODAY
'I AM IN TROUBLE - ANY ADVICE?
ProdRowNum = "=MATCH(C6,INDIRECT(ADDRESS(1,3,,,$C$8)):INDIRECT(ADDRESS(100,3,,,$C$8)),0)"
Worksheets("Summary").Range("$B24").Value = ProdRowNum

ProdStartPeriod = "=MATCH($C$9,INDIRECT(ADDRESS(1,1,,,$C$8)):INDIRECT(ADDRESS(1,100,,,$C$8)),0)"
Worksheets("Summary").Range("$B25").Value = ProdStartPeriod

ProdEndPeriod = "=MATCH($C$10,INDIRECT(ADDRESS(1,1,,,$C$8)):INDIRECT(ADDRESS(1,100,,,$C$8)),0)"
Worksheets("Summary").Range("$B26").Value = ProdEndPeriod

'Arg1 = ProdRowNum.Value & "," & ProdStartPeriod.Value & ",,,$C$8"
'Worksheets("Summary").Range("$B27").Value = Arg1

'Arg2 = ProdRowNum.Value & "," & ProdEndPeriod.Value & ",,,$C$8"
TotalUnits = "=SUM(" & ProdRowNum & "," & ProdStartPeriod & ")"

' ALSO NEED HELP IN INCORPORATING VALUES OF LOCAL VARIABLES INTO EXCEL FORMULA - AM GETTING RUN TIME ERRORS HERE ALSO. 

TotalUnits = _
"=SUM(INDIRECT(ADDRESS(" & ProdRowNum & "," & ProdStartPeriod &
",,,$C$8)):INDIRECT(ADDRESS(" & ProdRowNum & "," & ProdEndPeriod &
",,,$C$8)))"
Worksheets("Summary").Range("$B27").Value = TotalUnits

End Sub


Hi EveryOne,

I am a beginner in VBA programming. I have programmed before in PERL, C etc, but suffice it to say that it's been a while. I am trying to write some code that will help automate Excel reports that I get on a daily basis for which I need your help/advice. I am running into trouble, when I try to incorporate Excel formula's in VBA. I am also facing issues in incorporating the values of local variables into Excel formula.

Here's some code that I am experimenting with - but I am getting run time errors.

Sub InsertFormula()
'Variable Declaration..
Dim n As Long
Dim Addr As String
Dim test, Arg1, Arg2 As String
Dim ProdRowNum As Integer
Dim ProdStartPeriod As Integer
Dim ProdEndPeriod As Integer
Dim TotalUnits As Integer

For n = 23 To 28
    Addr = "$" & "A" & n
    Worksheets("Summary").Range(Addr).Formula = "=RAND()"
Next n

test = """cfg""" & ",C6"
    Worksheets("Summary").Range("$B23").Formula = _
    "=CONCATENATE(" & test & ")"
    
'RUN TIME ERROR OCCURS HERE. THESE MATCH STATEMENTS WERE WORKING OK Y'DAY - BUT TODAY
'I AM IN TROUBLE - ANY ADVICE?
ProdRowNum = "=MATCH(C6,INDIRECT(ADDRESS(1,3,,,$C$8)):INDIRECT(ADDRESS(100,3,,,$C$8)),0)"
Worksheets("Summary").Range("$B24").Value = ProdRowNum

ProdStartPeriod = "=MATCH($C$9,INDIRECT(ADDRESS(1,1,,,$C$8)):INDIRECT(ADDRESS(1,100,,,$C$8)),0)"
Worksheets("Summary").Range("$B25").Value = ProdStartPeriod

ProdEndPeriod = "=MATCH($C$10,INDIRECT(ADDRESS(1,1,,,$C$8)):INDIRECT(ADDRESS(1,100,,,$C$8)),0)"
Worksheets("Summary").Range("$B26").Value = ProdEndPeriod

'Arg1 = ProdRowNum.Value & "," & ProdStartPeriod.Value & ",,,$C$8"
'Worksheets("Summary").Range("$B27").Value = Arg1

'Arg2 = ProdRowNum.Value & "," & ProdEndPeriod.Value & ",,,$C$8"
TotalUnits = "=SUM(" & ProdRowNum & "," & ProdStartPeriod & ")"

' ALSO NEED HELP IN INCORPORATING VALUES OF LOCAL VARIABLES INTO EXCEL FORMULA
' AM GETTING RUN TIME ERRORS IN THE STATEMENT BELOW TOO... - ANY ADVICE ?
'TotalUnits = _
"=SUM(INDIRECT(ADDRESS(" & ProdRowNum & "," & ProdStartPeriod &
",,,$C$8)):INDIRECT(ADDRESS(" & ProdRowNum & "," & ProdEndPeriod &
",,,$C$8)))"
Worksheets("Summary").Range("$B27").Value = TotalUnits

End Sub
Thanks in advance for all your help.

Regards,
-Exxel...

I often use Excel functions in VBA:

Application.WorksheetFunction.Sum
Application.WorksheetFunction.Average

etc

I cannot seem to get this to work when using the EMOMONTH function. Is this because it is an addin and must be referenced differently?

Thnx

Hi,

Is there some kind of translation dictionnary between excel functions and vba function?

For example :

find in excel = instr in vba
lower in excel = Lcase in vba
etc...

Thank you for your help

Hi, I'm very beginner in vba and tried it couple of times with no success.
Please, please help!

I need to add to existing vba macro next functionality:
add 3 collumns (N,O,P) to existing ****
Set formulas to new collums as following
(I'm copiing from one cell of each collum)

N104 should have following value
=IF( LEN($K104) > 0,DATEVALUE(MID($K104,1,FIND("GMT",$K104)-1)) - DATEVALUE(MID($L104,1,FIND("GMT",$L104)-1)),NOW()-DATEVALUE(MID($L104,1,FIND("GMT",$L104)-1)))
O233
=WEEKNUM(DATEVALUE(MID($L233,1,FIND("GMT",$L233)-1)))
P562
=WEEKNUM(IF( LEN($K562) > 0,DATEVALUE(MID($K562,1,FIND("GMT",$K562)-1)),0))
Thanks in advance!

I have have been trying to use either vlookup or Match Excel functions in VBA code where the lookup key field is a date. I don't have any problems doing either in the spreadsheet, but both fail in my VBA code. I get an error that the program can't find the "Vlookup [or Match] property of the the worksheetfunction object."

I have a thought that I could try to convert the date to an integer, and do lookup on the integer, but even if it works it seems like an escape from doing things the right way. I can get worksheetfunction.vlookup to work when I am not using a date as the lookup field.

Sorry I am not enclosing the code. I have changed it so much trying to find a workable solution that it is not much use now.

there is some thought that worksheetfunction.vlookup and worksheetfunction.match don't work in VBA when the lookup field is a date. Possibly this is just an Excel glitch, but if there is a way to fix, that would really make things easier. thanks in advance.

Is there a way to name a sheet in vba? I am creating new sheets in vba, and renaming these tabs, but is there a way to actually rename the sheet (so that in the properties window it says (Name) "Demand" AND Name "Demand" Instead of (Name) Sheet1, Name "Demand"?

I'm trying to use an Excel function in VBA but it give me an error stating "Object doesn't support this property of method". Am I missing something obvious? Here is the exact line: MySerial1 = WorksheetFunction.Mid(A2, 3, 1)


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